Solved: SQL Strings

[Mavver]

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

[matthewspatrick]

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]

[Mavver]

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

[Mavver]

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

[matthewspatrick]

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]

[Mavver]

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

[geekgirlau]

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

[Mavver]

Geekgirl to the rescue

many thanks

Mav