Using ACC2002 SP3, DAO 3.6 and ADO 2.6 references are checked...

I'm using VBA to construct an "on-the-fly" query to search a single database table fields. I got the code from Martin Green's (FontAccess) site, modified it slightly for my needs, and for the most part it works nicely. However, the query fails to find some of the records and I can't seem to figure out why.

The user opens a form with 10 different text boxes, fills in some search terms, then clicks the Search button which opens a results form based on the query manipulated by the QueryDef code. Some records are never found by the query no matter what I do. If I run a manually constructed query, it finds the record(s). I'm baffled, can anyone help, or suggest some other means of searching the table? I can post the database if needed.

VBA Code: (the Dim str* As String are the text box values from the search form)

Private Sub cmdOK_Click()
' Pointer to error handler
On Error GoTo cmdOK_Click_err
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strArrDate As String
Dim strReport As String
Dim strLastName As String
Dim strFirstName As String
Dim strDOB As String
Dim strCharge As String
Dim strOfficers As String
Dim strBeat As String
Dim strBINum As String
Dim strArrestLocation As String
Dim strSQL As String
Dim stDocName As String
Dim stLinkCriteria As String
' Identify the database and assign it to the variable
Set db = CurrentDb
' Check for the existence of the query, create it if not found,
' and assign it to the variable
If Not QueryExists("qrySearch") Then
Set qdf = db.CreateQueryDef("qrySearch")
Else
Set qdf = db.QueryDefs("qrySearch")
End If
' Get the values from the combo boxes
If IsNull(Me.cboArrDate) Then
strArrDate = " > #01/01/1901# "
Else
strArrDate = "= #" & Me.cboArrDate & "# "
End If
If IsNull(Me.cboReport.Value) Then
strReport = " Like '*' "
Else
strReport = " Like '*" & Me.cboReport.Value & "*' "
End If
If IsNull(Me.cboLastName.Value) Then
strLastName = " Like '*' "
Else
strLastName = " Like '*" & Me.cboLastName.Value & "*' "
End If
If IsNull(Me.cboFirstName.Value) Then
strFirstName = " Like '*' "
Else
strFirstName = " Like '*" & Me.cboFirstName.Value & "*' "
End If
If IsNull(Me.cboDOB) Then
strDOB = " > #01/01/1901# "
Else
strDOB = "= #" & Me.cboDOB & "# "
End If
If IsNull(Me.cboCharge.Value) Then
strCharge = " Like '*' "
Else
strCharge = " Like '*" & Me.cboCharge.Value & "*' "
End If
If IsNull(Me.cboOfficers.Value) Then
strOfficers = " Like '*' "
Else
strOfficers = " Like '*" & Me.cboOfficers.Value & "*' "
End If
If IsNull(Me.cboBeat.Value) Then
strBeat = " Like '*' "
Else
strBeat = " Like '*" & Me.cboBeat.Value & "*' "
End If
If IsNull(Me.txtBINum.Value) Then
strBINum = " Like '*' "
Else
strBINum = " Like '*" & Me.txtBINum.Value & "*' "
End If
If IsNull(Me.cboArrestLocation.Value) Then
strArrestLocation = " Like '*' "
Else
strArrestLocation = " Like '*" & Me.cboArrestLocation.Value & "*' "
End If
' Build the SQL string
strSQL = "SELECT tblArrestLog.* " & _
"FROM tblArrestLog " & _
"WHERE tblArrestLog.[Arr Date]" & strArrDate & _
"AND tblArrestLog.DR" & strReport & _
"AND tblArrestLog.[Last Name]" & strLastName & _
"AND tblArrestLog.[First Name]" & strFirstName & _
"AND tblArrestLog.DOB" & strDOB & _
"AND tblArrestLog.Charge" & strCharge & _
"AND tblArrestLog.Officers" & strOfficers & _
"AND tblArrestLog.Beat" & strBeat & _
"AND tblArrestLog.[BI NO]" & strBINum & _
"AND tblArrestLog.[ARREST LOCATION]" & strArrestLocation & _
"ORDER BY tblArrestLog.[Last Name],tblArrestLog.[First Name];"
' Pass the SQL string to the query
qdf.SQL = strSQL
' Turn off screen updating
DoCmd.Echo False
' Check the state of the query and close it if it is open
'If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qrySearch") = acObjStateOpen Then
' DoCmd.Close acQuery, "qrySearch"
'End If
' Open the query
'DoCmd.OpenQuery "qrySearch"
'Open the results form
stDocName = "frmSearchResults"
DoCmd.OpenForm stDocName, , , stLinkCriteria
cmdOK_Click_exit:
' Turn on screen updating
DoCmd.Echo True
' Clear the object variables
Set qdf = Nothing
Set db = Nothing
Exit Sub
cmdOK_Click_err:
' Handle errors
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_exit
End Sub

In the code:

"WHERE tblArrestLog.[Arr Date]" & strArrDate & _
"AND tblArrestLog.DR" & strReport & _
"AND tblArrestLog.[Last Name]" & strLastName & _
"AND tblArrestLog.[First Name]" & strFirstName & _
"AND tblArrestLog.DOB" & strDOB & _
"AND tblArrestLog.Charge" & strCharge & _
"AND tblArrestLog.Officers" & strOfficers & _
"AND tblArrestLog.Beat" & strBeat & _
"AND tblArrestLog.[BI NO]" & strBINum & _
"AND tblArrestLog.[ARREST LOCATION]" & strArrestLocation & _

you always have criteria for all fields. This will only work for records where none of the the 10 fields have a Null value.

Are all 10 fields set as "required" in the table?

For the records that do not get returned that you think should, do any have a fields that has a Null value?

Thanks for the quick reply Coach:

none are required

yes, typically at least one field is not entered by the data entry person

Should I be accounting for Nulls with the Nz() function? Could you show how modify the code by example if another function is needed?

I have found that it really is better to not use this:

... AND tblArrestLog.Beat Like "*" ...

If you are searching for "*" it is better to leave it out altogether. The query will run a lot faster.


Otherwise you can use this:

... AND ( tblArrestLog.Beat Like "*" Or tblArrestLog.Beat Is Null ) ...


Hope this helps.



Edited by: HiTechCoach on Fri Feb 3 15:23:05 EST 2006.

Here is a previous post that has a lot of good information on this subject:
Query Help Null entries in table

Thanks again Coach - looks like I'll have to rework the query to omit the fields not filled out in the Search form. If I include the Or Is Null qualifier, I get records other than what's also specified in the Last Name field for example. You pointed me in the right direction...

You're Welcome.

Good luck with your project...