Query Syntax Problem

[Andi Plotsky]

Can someone please tell me - what's the problem with the syntax of the
Select portion of the call to the ChangeQueryDef function. My " " marks are
off - but I can't figure out where -

Private Sub cboSelectPhys_Change()
Dim rsA As DAO.Recordset
Dim strSQL As String

'select the records which have the same Full dr's name as that
'selected in the combobox
Set rsA = CurrentDb.OpenRecordset("Select * from qryLkpPhys where [Physician
Name]= '" & cboSelectPhys.Value & "'", dbOpenDynaset)

rsA.MoveFirst
rsA.MoveLast
Debug.Print "recordcount= "; rsA.RecordCount
If rsA.RecordCount > 0 Then
rsA.MoveFirst
strSQL = "'" & rsA("MD#") & "'"
rsA.MoveNext
Do Until rsA.EOF
strSQL = strSQL & " OR '" & rsA("MD#") & "'"
rsA.MoveNext
Loop
End If
Debug.Print "strSql= "; strSQL

ChangeQueryDef "qryPhysRpt", "SELECT Docname, Patient,Q6 from qryPhysName
where Q12=(" & "'" & strSQL & "')" _
& " OR Q13=(" & "'" & strSQL & "')" & _
" OR Q14=(" & "'" & strSQL & "')" & _
" OR Q15=(" & "'" & strSQL & "')"

Thanks!
Andi

[jimfortune]

Andi Plotsky wrote:

Can someone please tell me - what's the problem with the syntax of the Select portion of the call to the ChangeQueryDef function. My " " marks are off - but I can't figure out where -

...
Thanks!
Andi

Here's what I tried:

I changed the creation of strSQL to use commas instead of OR's.

where Q12 IN (" & strSQL & ")" & " OR Q13 IN (" & strSQL & ")" & ...

created:

where Q12 IN ('12','15') OR Q13 IN ('12','15') ...

Note that if Q12 and Q13 are Long fields you should create strSQL so
that the final string looks like:

where Q12 IN (12,15) OR Q13 IN (12,15) ...
These queries ran correctly in my test examples.

James A. Fortune