Access Database using do.cmd openform where clasue - returning all values

Question

DoCmd.OpenForm "Database Search", acFormDS, , srcLastName & "AND " & srcFirstName

This is only a small sample of the where clause - there are many more terms.

First, there is a set of If, Then type tings up top that set the variable srcLastName and srcFirstName to some value. These are not the problem and work just fine.

The trouble is getting them to return all values (for instance if you only want to search by one, on neither(return full database list))

Thus far I have settled for (in the if then section):

srcLastName = "[Lastname] =" & Chr(34) & cboLastName & Chr(34) - to search for something and

srcLastName = "[Lastname] <>" & Chr(34) & "Nuthin" & Chr(34) - to return everything (not equal to an absurd and mispelled database term.)

The trouble is that data that is null is also not returned. If I have a null firstname, it will not show up in any search period.

is there a term I can set [lastname] and [firstname] equal to that will return EVERYTHING (null, open, data, numbers, wierd stuff and otherwise) in a search

an SQL form of "give me everything shes got scotty" if you will.

the real issue here comes from the data entry - if I could just know that the people would enter everything 100% of the time, this code would work. but forget to enter the persons age or whatever, and it wont return that entry.

So far, the only other solution I have come up with is to put a counter in each if then statement. The count will go up by one for each thing that is being searched by. Then if the count is = 1, then I can search by something like just

DoCmd.OpenForm "Database Search", acFormDS, , srcLastName

or

DoCmd.OpenForm "Database Search", acFormDS, , srcFirstName

then revert back to the

DoCmd.OpenForm "Database Search", acFormDS, , srcLastName & "AND " & srcFirstName

when the count is 2 or more

truoble here is that it only works for one (unless I so wanted to create a custon list of 2 combined, 3 combined, 4 combined, but that is not happening)

Answer 1

It looks like you want to:

1. create a complex query on the fly
2. use a form to let the user design the query
3. cope with non-normal data

For #1, your SQL skills aren't strong enough. To deal with null values, you can add

OR IsNull([Lastname])

But I don't think you're in position to apply that.

For #2, check: - http://www.mvps.org/access/forms/frm0045.htm

For #3, validation of data should happen at the time of entry (of course that isn't always possible). This can be done at the table-field level: - http://allenbrowne.com/ValidationRule.html - http://www.databasejournal.com/features/msaccess/article.php/3680831/Designing-Forms-for-Efficient-and-Accurate-Data-Entry.htm) ... or it can be done using form automation:
- http://www.blueclaw-db.com/access_event_programming/beforeupdate.htm

Answer 2

Ok, so the answer for me at least is to build ther where clause on the fly - using the if than else satements above to do something like...

If chkLastName.Value = False Then
            srcLastName = ""
        End If
        '------------------------------------------
        If chkLastName.Value = True Then
            If cboLastName = "" Then
                srcLastName = ""
            Else
                If IsNull(cboLastName) = True Then
                    srcLastName = ""
                Else
                    varCounterSelector = varCounterSelector + 1
                    srcLastName = "[Lastname] =" & Chr(34) & cboLastName & Chr(34)
                End If
            End If
        End If

        If chkFirstName.Value = False Then
            srcFirstName = ""
        End If
        '------------------------------------------
        If chkFirstName.Value = True Then
            If cboFirstname = "" Then
                srcFirstName = ""
            Else
                If IsNull(cboFirstname) = True Then
                    srcFirstName = ""
                Else
                    varCounterSelector = varCounterSelector + 1
                    If varCounterSelector > 1 Then
                        rdyFirstName = "[First name] = " & Chr(34) & cboFirstname & Chr(34)
                        rdyJoinSyntax = "and"
                        srcFirstName = rdyJoinSyntax & rdyFirstName
                    End If
                    If varCounterSelector <= 1 Then
                    srcFirstName = "[FirstName] =" & Chr(34) & cboFirstname & Chr(34)
                    End If
                End If
            End If
        End If

followed by a do.cmd openform with a where caluse like...

DoCmd.OpenForm "Database Search", acFormDS, , srcLastName & srcFirstName

Its not pretty, and I could probably optimize it, but Ima lazy coder, so dont fix it if it eint broke.

Answer 3

This is not a terribly complicated problem. The key, as you discovered, is to have no criterion on the particular field when you don't want to filter on it. But your code is unnecessarily convoluted. Here's simpler code:

  Dim strLastName As String
  Dim strFirstName As String
  Dim strWhere As String

  strLastName = Me!cboLastName & vbNullString
  strFirstName = Me!cboFirstName & vbNullString
  If Len(strLastName) > 0 Then
     strWhere = strWhere & " AND " & Application.BuildCriteria("LastName", dbText, strLastName)
  End If
  If Len(strFirstName) > 0 Then
     strWhere = strWhere & " AND " & Application.BuildCriteria("FirstName", dbText, strFirstName)
  End If

  DoCmd.OpenForm "Database Search", acFormDS, , Mid(strWhere, 6)

This is bog-standard code for this kind of thing. You can add any number of criteria to your WHERE clause doing it this way with a minimum of code.