Re: No results on a multiple parameter search

Gee, what fun, lots of code to look at with no clue as to what problem you
are having with it. Ok, time to put on our guessing game hats:

1)
strSql = strSql & "(Username = """ & Me.txtUsername & ") AND "
Use of quotes is inconsistent with that of other fields

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

If lngLen > 0 Then

(Note that lngLen has not been assigned a value and will always be 0, which
means your constructed WHERE clause is ignored and you get the message "No
Criteria".)

My guess is that you want this to be something like:
If Not IsNull(Me.txtOS) Then
strSql = strSql & "(OS = """ & Me.txtOS & """) AND "
End If

lngLen = Len(strSql) - 5
If lngLen > 0 Then
(etc.)

HTH,
--
George Nicholson

Remove 'Junk' from return address.



<charlienilmag@xxxxxxxxx> wrote in message
news:1139336550.967336.53760@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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

  • 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)