Private Sub cmdSearch_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#dd\/mm\/yyyy\#" 'The format expected for dates in a JET query string.
Dim intRCount As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSql As String
Dim varSQLWhere As Variant
Dim rs As DAO.Recordset
' Set db = CurrentDb()
' Set rst = qdf.OpenRecordset()
' Set qdf = db.QueryDefs("qrySendaways")
'***********************************************************************
'Look at each search box, and build up the criteria string from the non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtCFirst) Then
strWhere = strWhere & "([PatientFirstName] = """ & Me.txtCFirst & """) AND "
End If
'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtCLast) Then
strWhere = strWhere & "([PatientSecondName] Like ""*" & Me.txtCLast & "*"") AND "
End If
'Date field example. Use the format string to add the # delimiters and get the right international format.
If Not IsNull(Me.txtCDoB) Then
strWhere = strWhere & "([DoB] = " & Format(Me.txtCDoB, conJetDate) & ") AND "
End If
'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere
'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
' Get the loaded record count
intRCount = rst.RecordCount
intRCount = db.TableDefs("qrySendaways").RecordCount
' Set up the form message
If intRCount <> 1 Then
Me.RecordCount = intRCount & " Records found."
Else
Me.RecordCount = intRCount & " Record found."
End If
' Reveal the result
Me.RecordCount.Visible = True
' Clear the database object
Set db = Nothing
' If 10 or fewer
If intRCount <= 10 Then
' Expand the form to show them all
Me.InsideHeight = Me.FormHeader.Height + Me.FormFooter.Height + _
(intRCount * Me.Detail.Height)
Else
' Show the first 10
Me.InsideHeight = Me.FormHeader.Height + Me.FormFooter.Height + _
(10 * Me.Detail.Height)
End If
' Reload my recordset
Me.Requery
' Unhide the detail section
Me.Detail.Visible = True
'Debug.Print strSql
End Sub
