SQL with variable issue

[WatsonDyar]

Hello All!

Having problems with the following:

strRole is declared and defined. Stepping through the code shows that the variable is working correctly. I just cannot get the following statement to work with the recordset.

sqlCount = "SELECT mtProgram.mbrProgramID, mtProgram.Role, mtProgram.Complete " _
& "FROM mtProgram " _
& "WHERE ((mtProgram.Complete)= 'No') And ((mtProgram.Role)=" & strRole & ";"

 

[Minty]

Assuming strRole is a string you need to enclose it in quotes and you are missing a closing bracket;

"WHERE ((mtProgram.Complete)= 'No') And ((mtProgram.Role)=[COLOR="red"]'[/COLOR]" & strRole & "[COLOR="Red"]')[/COLOR];"

Add a debug.print sqlCount and you'll see what is being passed as the complete string .

 

[WatsonDyar]

That did the trick.

Thank you Minty! I was losing my mind on that one....

 

[Minty]

No problem - there are a number of gotcha's with building VBA Queries - Paul has an excellent guide here http://www.baldyweb.com/buildsql.htm

 

[WatsonDyar]

Hi again Minty-

Any idea why this isn't working? Same type of issue.

sqlPresent = "SELECT mtAttendStatus.mbrProgramID, Count(mtAttendStatus.mbrAttendReportID) AS PresentCount " _
& "FROM mtAttendStatus " _
& "WHERE ((mtAttendStatus.Status)='Present') AND (mtProgram.mbrProgramID)=" & intPrgmID & " " _
& "GROUP BY mtAttendStatus.mbrProgramID;"

I looked at the reference you provided and couldn't figure this out!

 

[Minty]

Add a Debug.Print sqlPresent after you have created the string and see what it presents in the immediate window. (Press ctrl G in the debug code window)

Make sure that you declare a value for intPrgmID.

 

[Cronk]

Should
.... AND (mtProgram.mbrProgramID)=" & intPrgmID & " " _ ....

be
.... AND ( mtAttendStatus.mbrProgramID)=" & intPrgmID & " " _ ....