MS Access, Selecting records based on multiple criteria form inputs

Question

I've got an Access form connected to a query that's based on the input from 3 different boxes. If box1 is filled, it needs to hold true for the subsequent boxes but can be ignored if empty. The other two boxes (box2 and box3) can either have data or be empty and if empty should be ignored.

To use a broad example, if I'm searching for books contained within a library system that has 5 different libraries, I want to either select all the books within a specific library by John Doe and Jane Deer or the books by John Doe and Jane Deer held at all libraries or just books by Jane Deer, etc.

I'd been trying to follow what's described here, and it works unless I leave any of the input fields empty.

WHERE ((tblBooks.LibraryName LIKE [Forms]![frmSearchAuthors]!Box1 & "*") OR ([Forms]![frmSearchAuthors]!Box1 IS NULL)) 
AND (((tblBooks.Author Like "*" & [Forms]![frmSearchAuthors]!Box2 & "*") OR ([Forms]![frmSearchAuthors]!Box2 IS NULL)) 
OR ((tblBooks.Author Like "*" & [Forms]![frmSearchAurthors]!Box3 & "*") OR ([Forms]![frmSearchAuthors]!Box3 IS NULL)))

Alternatively, I have also tried using an IIF statements but couldn't figure out how to ignore any boxes that were left empty.

WHERE (tblBooks.LibraryName Like [Forms]![frmSearchAuthors]!Box1 & "*" OR [Forms]![frmSearchAuthors]!Box1 IS NULL) 
AND (IIF (ISNULL([Forms]![frmSearchAuthors]!Box2), (tblBooks.Author IS NULL), (tblBooks.Author Like "*" & [Forms]![frmSearchAuthors]!Box2 & "*")) 
OR IIF (ISNULL([Forms]![frmSearchAuthors]!Box3), (tblBooks.Author IS NULL), (tblBooks.Author Like "*" & [Forms]![frmSearchAuthors]!Box3 & "*")))

Is there something wrong with my syntax or is there another way I should be doing this?

For optional criteria in a `WHERE` clause you will need to build-up a SQL string ("Dynamic SQL") inside VBA and then run that. Be sure to use Parameters instead of concatenating strings naively to prevent SQL-injection vulnerabilities (and to prevent your application from breaking when a user types-in an apostrophe) — Dai, Aug 28 '20 at 23:28
Review http://allenbrowne.com/ser-62.html and https://stackoverflow.com/questions/49509615/how-do-i-use-parameters-in-vba-in-the-different-contexts-in-microsoft-access — June7, Aug 28 '20 at 23:50
Mixing AND and OR operators gets complicated. Notice the example you cite does not show OR operator for multiple parameters on same field which is what you are trying with Author. — June7, Aug 29 '20 at 00:27

score 1 · Answer 1 · answered Aug 29 '20 at 07:59

1

The linked technique only works if all search boxes have the same "rank" and are combined with AND.

If you look logically, the whole author block is combined with OR, so each empty search box in there will make the whole block TRUE.

So in your case you need to build the search string with VBA, pseudo code:

for i = 1 to n
  if not isnull(authorsearchbox(i)) then
    strSearch = StrAppend(strSearch, CSql(authorsearchbox(i)), " OR ")
  end if
next i

'-------- with --------

' Append sAppend to sBase, use sSeparator if sBase wasn't empty
Public Function StrAppend(sBase As String, sAppend As Variant, sSeparator As String) As String
    If Len(sAppend) > 0 Then
        If sBase = "" Then
            StrAppend = Nz(sAppend, "")
        Else
            StrAppend = sBase & sSeparator & Nz(sAppend, "")
        End If
    Else
        StrAppend = sBase
    End If
End Function

and
CSql

answered Aug 29 '20 at 07:59

Andre

26,751
7
36
80

Ok, thank you. I'll try this set up in my database. Is VBA the only option here? I get what you're saying about my author block and using OR but for a variety of reasons (only partially lack of familiarity), I'd been trying to stay away from VBA. – honu.atuin Aug 31 '20 at 17:59
Yes, I'm fairly certain that you cannot do this without VBA. You could, if you would limit it to **one** author search box. -- But IMHO using VBA is essential for all but the most rudimentary Access applications, avoiding it will limit you a lot. @honu.atuin – Andre Aug 31 '20 at 18:37
I'd been coming to that realization. Well, I'll give your code a try and probably end up coming back with questions on how to get it going since I don't have a lot of VBA experience. Thanks! – honu.atuin Aug 31 '20 at 20:40

MS Access, Selecting records based on multiple criteria form inputs

1 Answers1