Empty textboxes not allowing query to operate properly

[Need_Help]

Hi all,



I have a form where users can enter text into multiple text boxes. I want to be able to run a query that can select data from multiple text boxes and skip over text boxes that are left empty by the user. How can I get a query to properly display the results?

Thanks a lot in advance

[pbaldy]

One way, though I personally don't use it with more than a couple of fields:

http://access.mvps.org/access/queries/qry0001.htm

When I have numerous optional criteria, I tend to build dynamic SQL as demonstrated in the sample db here:

http://www.baldyweb.com/BuildSQL.htm

[Need_Help]

I like the second one, the first will just get messy, I think. However, I built as at least I think it's supposed to but now when I click the button on the form, there isn't even a response.

Also, here's how I was entering my code for the form Search_Parts. I just want to make sure all of this is going in correctly. These are the first two text boxes that the function is sifting through; all textboxes allow open entry by the user. Thanks for the help so far.

If Len(Search_Parts.Number & vbNullString) Then
If (Search_Parts.chkLike) Then
strSQLWhere = "WHERE Part_Number Like " & Chr$(39) & "*" & _
Search_Parts.Number & "*" & Chr$(39)
Else
strSQLWhere = "WHERE Part_Number = " & Chr$(39) & _
Me.txtEmployeeName & Chr$(39)
End If

strSQLWhere = strSQLWhere & strJoin

End If

If Len(Search_Parts.Name & vbNullString) Then
If Len(strSQLWhere) = 0 Then
strSQLWhere = "WHERE "
End If

If Len(Search_Parts.Name & vbNullString) Then
strSQLWhere = strSQLWhere & "Part_Name Like " & Chr$(39) & "*" & Search_Parts.Name & _
"# AND #" & Search_Parts.Name & "#"
Else
strSQLWhere = strSQLWhere & " Part_Name = #" & Search_Parts.Name & "#"
End If

strSQLWhere = strSQLWhere & strJoin

End If

[Need_Help]

I just figured out that I had referenced the button incorrectly, so the Private Sub now recognizes the click, but there is an error and the VBA window pops up trying to debug the code. I also fixed another reference in the code I just posted. I imagine there is still something wrong in the structure.

Revised code:

If Len(Search_Parts.Number & vbNullString) Then
If (Search_Parts.chkLike) Then
strSQLWhere = "WHERE Part_Number Like " & Chr$(39) & "*" & _
Search_Parts.Number & "*" & Chr$(39)
Else
strSQLWhere = "WHERE Part_Number = " & Chr$(39) & _
Search_Parts.Number & Chr$(39)
End If

strSQLWhere = strSQLWhere & strJoin

End If

If Len(Search_Parts.Name & vbNullString) Then
If Len(strSQLWhere) = 0 Then
strSQLWhere = "WHERE "
End If

If Len(Search_Parts.Name & vbNullString) Then
strSQLWhere = strSQLWhere & "Part_Name Like " & Chr$(39) & "*" & Search_Parts.Name & _
"# AND #" & Search_Parts.Name & "#"
Else
strSQLWhere = strSQLWhere & " Part_Name = #" & Search_Parts.Name & "#"
End If

strSQLWhere = strSQLWhere & strJoin

End If

[pbaldy]

Well, it appears that you are surrounding a name with # signs. That is only appropriate for a date value. If you read that tutorial, it goes over it in detail, but basically dates values are surrounded by #, text by quotes, number by nothing. A helpful tool to use is to put:

Debug.Print strSQLWhere

after the string is built. That will print out the finished SQL to the VBA Immediate window, which will let you examine/test it. What are you doing with it after this point? There's clearly more to the code.

[Need_Help]

The rest of the code is pretty much just more If/Else statements that look exactly like the second group above until the end where I have the two lines:

strSQL = strSQLHead & strSQLWhere & strSQLOrderBy

Search_Parts.fsubParts.Parts_Display.RecordSource = strSQL

The problem that is now coming to light is when I attempt to debug, I get "Run time error 424: Object Required". I don't think there is anything wrong with file locations, the rest of my DB works fine.

[pbaldy]

What line throws the error? If you have an error handler, you may need to temporarily comment it out.