strSQL as String

[S Jackson]

I am trying to requery a combo box on my form based on the selection made in
another combo box. Here is the code I have so far:

'Code to filter lkpPetitioner
Dim strSQL As String
strSQL = "SELECT tblCases.PetLastName & ', ' & tblCases.PetFirstName AS
Petitioner, " _

'Build WHERE Clause
Select Case Me.FindProgram
Case "CNA"
strSQL = strSQL & "WHERE tblCases.Program = CNA " _

Case "EMR"
strSQL = strSQL & "WHERE tblCases.Program = EMR " _

Case "NFA"
strSQL = strSQL & "WHERE tblCases.Program = NFA " _

Case "CMA"
strSQL = strSQL & "WHERE tblCases.Program = CMA " _

End Select

'Order Statement
strSQL = strSQL & "ORDER by PetLastName;"
MsgBox strSQL

Me.lkpPetitioner.RowSource = strSQL
Me.lkpPetitioner.Requery

I keep getting a syntax error (missing operator) in query expression 'WHERE
tblCases.Program = NFA ORDER by PetLastName' for the lkpPetitioner combo box
data source, but everything seems to look fine when I view the MsgBox
strSQL:

SELECT tblCases.PetLastName & ', ' & tblCases.PetFirstName AS Petitioner,
WHERE tblCases.Program = NFA ORDER by PetLastName;

What is wrong here?
TIA
S. Jackson

 

[S Jackson]

Figured it out! I forgot the to include SELECT tblCases.Program and I
forgot to include the FROM clause. FYI, here is the correct code:

Dim strSQL As String
strSQL = "SELECT tblCases.PetLastName & ', ' & tblCases.PetFirstName AS
Petitioner, " _
& "tblCases.Program FROM tblCases " _

'Build WHERE Clause
Select Case Me.FindProgram
Case "CNA"
strSQL = strSQL & "WHERE tblCases.Program = " & "'" & "CNA" & "' " _

Case "EMR"
strSQL = strSQL & "WHERE tblCases.Program = " & "'" & "EMR" & "' " _

Case "NFA"
strSQL = strSQL & "WHERE tblCases.Program = " & "'" & "NFA" & "' " _

Case "CMA"
strSQL = strSQL & "WHERE tblCases.Program = " & "'" & "CMA" & "' " _

End Select

'Order Statement
strSQL = strSQL & "ORDER BY tblCases.PetLastName; "

 

[Klatuu]

A simpler solution:

Dim strSQL As String
strSQL = "SELECT tblCases.PetLastName, tblCases.PetFirstName AS
Petitioner, " _
& "tblCases.Program FROM tblCases WHERE tblCases.Program = '" _
& Me.FindProgram & "' ORDER BY tblCases.PetLastName; "

 

[S Jackson]

Doh! Yep - much simpler. Thanks for pointing it out.

S. Jackson