Query Working But....

[jbento]

All,
I got the following code working from a command button on a search form. The search form has 5 fields to search on. My only problem now is that I can only enter one search criteria at a time to get the correct results. It doesn't work when I put more than one search criteria in. For instance if I only search one field (fieldA1) for a name and I put that name in, I will get the records with that name.
If I want to search on fieldA1 and field 2 the search will come back with no results. What is wrong with the code below?

Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()


'Constant Select statement for the Query definition
strSQL = "SELECT tbl_test.MainID, tbl_test.field1, tbl_test.field2, tbl_test.field3, tbl_sub1.MainID, tbl_sub1.fieldA1, tbl_sub1.fieldA2, tbl_sub2.MainID, tbl_sub2.fieldB1, tbl_sub2.fieldB2"
strSQL = strSQL & "FROM (tbl_test INNER JOIN tbl_sub1 ON tbl_test.MainID = tbl_sub1.MainID) INNER JOIN tbl_sub2 ON tbl_test.MainID = tbl_sub2.MainID "

strSQL = "SELECT tbl_test.*" ',tbl_sub1.*,tbl_sub2.*"
strSQL = strSQL & "FROM tbl_test "
'strSQL = strSQL & "INNER JOIN tbl_sub1 ON tbl_test.MainID = tbl_sub1.MainID)"
'strSQL = strSQL & "INNER JOIN tbl_sub2 ON tbl_test.MainID = tbl_sub2.MainID)"
strWhere = "WHERE"



strOrder = "ORDER BY tbl_test.MainID;"


'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.field1) Then '<--If the textbox field1 contains no data THEN do nothing
strWhere = strWhere & &quot; (tbl_test.field1) Like '*&quot; & Me.field1 & &quot;*' AND&quot; '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.field2) Then
strWhere = strWhere & &quot; (tbl_test.field2) Like '*&quot; & Me.field2 & &quot;*' AND&quot;
End If

If Not IsNull(Me.field3) Then
strWhere = strWhere & &quot; (tbl_test.field3) Like '*&quot; & Me.field3 & &quot;*' AND&quot;
End If

If Not IsNull(Me.fieldA1) Then
strWhere = strWhere & &quot; (tbl_sub1.fieldA1) Like '*&quot; & Me.fieldA1 & &quot;*' AND&quot;
strSQL = strSQL & &quot;INNER JOIN tbl_sub1 ON tbl_test.MainID = tbl_sub1.MainID &quot;
End If

If Not IsNull(Me.fieldA2) Then
strWhere = strWhere & &quot; (tbl_sub1.fieldA2) Like '*&quot; & Me.fieldA2 & &quot;*' AND&quot;
strSQL = strSQL & &quot;INNER JOIN tbl_sub1 ON tbl_test.MainID = tbl_sub1.MainID &quot;
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs(&quot;qry_test&quot
qryDef.SQL = strSQL & &quot; &quot; & strWhere & &quot;&quot; & strOrder

'Open the Query
DoCmd.OpenReport &quot;rpt_test&quot;, acViewPreview, &quot;&quot;, &quot;&quot;, acNormal

End Sub

Please help,

Thanks,

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[JonFer]

In your post you have extraneous lines (starting with &quot;strSQL&quot in the sections dealing with fieldA1 and fieldA2.

After you correct that, put in a breakpoint where the WHERE string is done, press the button, and then view the values of strWhere, strSQL, and strOrder.

 

[jbento]

I'm sorry I tried to fix it, but I don't know what I'm doing

Can you help me figure this out? I have uploaded the Access 2002 database to my site:

http://www.jerpat.org/testSub.mdb

There is a search form in the forms area of the database that has a command button with the code behind.

Would it be a problem if you downloaded and looked at it for me?

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[JonFer]

This gets the query to run. You had several errors which could be seen by viewing the SQL you were assigning to the QueryDef (missing spaces, extraneous join syntax, etc). I removed the MAIN_ID fields from tbl_sub1 and tbl_sub2 in the query.

Private Sub cmdSearch_Click()

'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()


'Constant Select statement for the Query definition
strSQL = "SELECT tbl_test.MainID, tbl_test.field1, tbl_test.field2, tbl_test.field3, tbl_sub1.fieldA1, tbl_sub1.fieldA2, tbl_sub2.fieldB1, tbl_sub2.fieldB2 "
strSQL = strSQL & "FROM (tbl_test INNER JOIN tbl_sub1 ON tbl_test.MainID = tbl_sub1.MainID) "
strSQL = strSQL & "INNER JOIN tbl_sub2 ON tbl_test.MainID = tbl_sub2.MainID "

strWhere = "WHERE"
strOrder = "ORDER BY tbl_test.MainID;"

'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.field1) Then '<--If the textbox field1 contains no data THEN do nothing
strWhere = strWhere & &quot; (tbl_test.field1) Like '*&quot; & Me.field1 & &quot;*'  AND&quot; '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.field2) Then
strWhere = strWhere & &quot; (tbl_test.field2) Like '*&quot; & Me.field2 & &quot;*'  AND&quot;
End If

If Not IsNull(Me.field3) Then
strWhere = strWhere & &quot; (tbl_test.field3) Like '*&quot; & Me.field3 & &quot;*'  AND&quot;
End If

If Not IsNull(Me.fieldA1) Then
strWhere = strWhere & &quot; (tbl_sub1.fieldA1) Like '*&quot; & Me.fieldA1 & &quot;*'  AND&quot;
End If

If Not IsNull(Me.fieldA2) Then
strWhere = strWhere & &quot; (tbl_sub1.fieldA2) Like '*&quot; & Me.fieldA2 & &quot;*'  AND&quot;
End If

'Remove the last AND from the SQL statment
'use 5 to remove WHERE if no parameters requested
'uses two spaces in front of each AND so 5 works
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs(&quot;qry_test&quot;)
   qryDef.SQL = strSQL & &quot; &quot; & strWhere & &quot; &quot; & strOrder
   
'Open the Query
DoCmd.OpenReport &quot;rpt_test&quot;, acViewPreview, &quot;&quot;, &quot;&quot;, acNormal

End Sub

 

[jbento]

Did you actually get the code to work by searching more than one field?

I pasted your code into my database and I still couldn't get it to work where I can search on more than one field at a time.

This is so frustrating.

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[JonFer]

Yes, it works fine. You have some of your fields mapped oddly based on their names. &quot;SUBFORM1 DATA&quot; checks FieldA1 in tbl_sub1 and &quot;SUBFORM2 DATA&quot; checks FieldA2 in the same table (not tbl_sub2 as expected). No fields from tbl_sub2 are included in the WHERE.

Run it without any criteria and then add one criteria at a time.