No results on a multiple parameter search

C

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!
 
G

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 "
Click to expand...
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
Click to expand...

(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!
Click to expand...
 
C

charlienilmag

George said:
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,
Click to expand...


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.
 
G

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.
Click to expand...
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,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

search button to report based on multiple combo boxs ( combo criteria based upon queries ) 6
Creating an advanced search from a form 4
Continuous form search problem 5
Allen Browne's "Filtering on a field in the Subform" 3
Enumerating all controls in a form that are on a tab control 4
Cleanup Code on Form ?? 3
Linking Multiple list boxes to create a query. 4
Create Report On the Fly 1

Top