No results on a multiple parameter search

[charlienilmag]

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!

 

[George Nicholson]

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.

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!

 

[charlienilmag]

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)
Use of quotes is inconsistent with that of other fields

2)

(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,

Sorry about that George. But thanks for replying! I made all the
changes like you suggested and I am no longer receiving the dialog box
that tells me "No Criteria". So thanks!

But when I enter in a value into the search fields, I'm not receiving
any records returned. Initially I used a wizard to create my form
which is in the detail portion. My search fields are down in the form
footer. Sorry if I don't have any more information. I'm not sure what
else you need to know. Obviously, I'm a noob.

 

[George Nicholson]

1) if there is any question about what your final SQL string looks like,
insert a breakpoint or temporary msgbox at the end of your procedure so you
can eyeball the final strSQL for yourself. Errors are usually fairly obvious
at that point.

I'm not receiving any records returned.

2) No records being returned? You aren't asking for any, at least not in the
code provided. By the time cmdSearch_Click finishes, you should have an SQL
string built (strSQL). But there is no code that does anything with that
procedure-level declared variable. I think you need to use strSQL to open a
recordset or querydef. Which method to use depends on how you expect records
"returned".

HTH,