Solved: SQL Strings

Hello All

If I have two SQL statements like the following

[vba]
strSQL = "SELECT * FROM tblSchool_Clean " & _
"WHERE tblSchool_Clean.PoE_Desc IN(" & strCriteria1 & ");"
[/vba]

[vba]
strSQL = "SELECT * FROM tblSchool_Clean " & _
"WHERE tblSchool_Clean.ToE_Desc IN(" & strCriteria2 & ");"
[/vba]

is it possible to merge them into one by using an AND somewhere, I dont know how to do this so if it is possible can someone show me with the above example please.

Many Thanks

Mav

Depends on what you mean by "merging" them. If you mean, 'give me a
result set that matches both criteria':

[VBA]
strSQL = SELECT * FROM tblSchool_Clean " & _
"WHERE PoE_Desc IN (" & strCriteria1 & ") " & _
"AND WHERE ToE_Desc IN (" & strCriteria2 & ")"
[/VBA]

If you mean, 'give me a result set that matches at least one criterion':

[VBA]
strSQL = SELECT * FROM tblSchool_Clean " & _
"WHERE PoE_Desc IN (" & strCriteria1 & ") " & _
"OR WHERE ToE_Desc IN (" & strCriteria2 & ")"
[/VBA]

many many thanks, and if I had Pivot Table to share I would give it you.

Alas it did not work, I placed the following string in the code and an error message box pops up with "Characters found after end of SQL statement

[vba]
strSQL1 = "SELECT * FROM tblSchool_Clean " & _
"WHERE tblSchool_Clean.PoE_Desc IN(" & strCriteria1 & ");" & _
"AND WHERE tblSchool_Clean.ToE_Desc IN(" & strCriteria2 & );"
[/vba]

The code which follows this bit is

[vba]
' Apply the new SQL statement to the query
qdf.SQL = strSQL1

' Empty the memory
Set db = Nothing
Set qdf = Nothing
[/vba]

Can anyone shed some light on this please, thanks ever so much in advance

Mav

Mav,

You did not quite follow directions :)

[VBA]
strSQL1 = "SELECT * FROM tblSchool_Clean " & _
"WHERE tblSchool_Clean.PoE_Desc IN(" & strCriteria1 & ") " & _
"AND WHERE tblSchool_Clean.ToE_Desc IN(" & strCriteria2 & ");"
[/VBA]

Another error message pops up this time (I cut and pasted this time)

Syntax error (missing operator) in query expression 'tblSchool_Clean.PoE_Desc IN ('School Phase One','School Phase Two') AND WHERE tblSchool_Clean.ToE_Desc IN ('School Type One','School Type Two')'.

....School Phase One/Two and School Type One/Two are the selection types.....

ta

Mav

[vba]
strSQL1 = "SELECT * FROM tblSchool_Clean " & _
"WHERE tblSchool_Clean.PoE_Desc IN(" & strCriteria1 & ") " & _
"AND tblSchool_Clean.ToE_Desc IN(" & strCriteria2 & ");"
[/vba]

Geekgirl to the rescue

many thanks

Mav