No results on a multiple parameter search

On an earlier post, I thought I had found code that was perfectly
suited for what I needed. What I'm trying to create is a way to search
a database using multiple fields regardless of whether that field is
filled in or not. Anything that matches those fields entered in, would
return any record(s).

For example, one column is "City". If I were to search for Austin, I
would expect all records with the "City" Austin to be returned.

Here's the code I have:

Private Sub cmdSearch_Click()

Dim strSql As String
Dim lngLen As Long
Const strcStub = "SELECT [Inventory_Database].Asset Tag,
[Inventory_Database].Username, [Inventory_Database].Email,
[Inventory_Database].City, [Inventory_Database].Address,
[Inventory_Database].Region, [Inventory_Database].Make,
[Inventory_Database].Model, [Inventory_Database].CPU,
[Inventory_Database].RAM, [Inventory_Database].HDDTotal,
[Inventory_Database1].HDDFree, [Inventory_Database].WindowsVersion FROM
[Inventory_Database]"


'Build the WHERE clause from the non-Null boxes.
If Not IsNull(Me.txtAsset) Then
strSql = strSql & "(Asset = """ & Me.txtAsset & """) AND "
End If

If Not IsNull(Me.txtUsername) Then
strSql = strSql & "(Username = """ & Me.txtUsername & ") AND "
End If

If Not IsNull(Me.txtEmail) Then
strSql = strSql & "(Email = """ & Me.txtEmail & """) AND "
End If

If Not IsNull(Me.txtCity) Then
strSql = strSql & "(City = """ & Me.txtCity & """) AND "
End If

If Not IsNull(Me.txtAddress) Then
strSql = strSql & "(Address = """ & Me.txtAddress & """) AND "
End If

If Not IsNull(Me.txtRegion) Then
strSql = strSql & "(Region = """ & Me.txtRegion & """) AND "
End If

If Not IsNull(Me.txtMake) Then
strSql = strSql & "(Make = """ & Me.txtMake & """) AND "
End If

If Not IsNull(Me.txtModel) Then
strSql = strSql & "(Model = """ & Me.txtModel & """) AND "
End If

If Not IsNull(Me.txtCPU) Then
strSql = strSql & "(CPU = """ & Me.txtCPU & """) AND "
End If

If Not IsNull(Me.txtRAM) Then
strSql = strSql & "(RAM = """ & Me.txtRAM & """) AND "
End If

If Not IsNull(Me.txtHDDTotal) Then
strSql = strSql & "(HDDTotal = """ & Me.txtHDDTotal & """) AND
"
End If

If Not IsNull(Me.txtHDDFree) Then
strSql = strSql & "(HDDFree = """ & Me.txtHDDFree & """) AND "
End If

If Not IsNull(Me.txtOS) Then
strSql = strSql & "(OS = """ & Me.txtOS & """) lngLen =
Len(strSql) - 5 "
End If

If lngLen > 0 Then
strSql = strcStub & " WHERE " & Left$(strSql, lngLen) & ";"
Else
strSql = strcStub & ";"
MsgBox "No criteria"
End If

End Sub



Any help would be greatly appreciated!

.

Relevant Pages

  • Re: No results on a multiple parameter search
    ... For example, one column is "City". ... If I were to search for Austin, ... Private Sub cmdSearch_Click ... Dim strSql As String ...
    (microsoft.public.access.forms)
  • Re: data entry on form
    ... Private Sub Zipcode_AfterUpdate ... Dim strSql As String ... The example assumes that Zipcode is a Text type field (not a Number ... >like to be able to enter a zipcode and have the city and state be ...
    (microsoft.public.access.forms)
  • Re: zipcode update city and state
    ... >didn't realize a zipcode can have more than one city. ... It will only enter Des Moines in the City field ... > Private Sub ZIP_AfterUpdate ... > Dim strSql As String ...
    (microsoft.public.access.forms)
  • zipcode update city and state
    ... A few days ago someone gave me some code so I can enter the zipcode and it ... will automatically fill in the city and state. ... Private Sub ZIP_AfterUpdate ... Dim strSql As String ...
    (microsoft.public.access.forms)
  • Re: UPDATE Field in Table using SQL & VBA
    ... Dim strSQL As String ... Private Sub POrderItemsID_BeforeUpdate ... Dim intQuantity As Integer ...
    (microsoft.public.access.modulesdaovba)
Loading