SQL question

[Guest]

I have the following code that populates a list box, and am running into a
problem. Case 2 works fine - cbo_SupportPerson passes a number. Case 4
doens't work. cbo_StudyType is one of the following - pre-market,
post-market or leads therapy. When I pick option 4, I get a pop-up telling
me to enter a parameter value for pre.
Any thoughts?

mySQL = "SELECT tbl_Studies.StudyID, tbl_Studies.StudyName,
tbl_Studies.Supported_By, tbl_Studies.StudyType FROM tbl_Studies"
Select Case opt_FilterBy
Case 2 'Studies by support person (chosen via cbo_SupportPerson)
myCriteria = " Where Supported_By =" & Me.cbo_SupportPerson
Case 4 'Studies by study type (chosen via cbo_StudyType)
myCriteria = " Where StudyType =" & Me.cbo_StudyType
End Select
mySQL = mySQL & " " & myCriteria
mySQL = mySQL & " ORDER BY tbl_Studies.StudyName;"

Thanks in advance, Anne

 

[Jim Evans]

Run the SQL from the immediate window. I believe you will see that you are
creating a continuous string with no spaces. you must have spaces.
" Where Supported_By =" & Me.cbo_SupportPerson Should be:

" Where Supported_By = " & Me.cbo_SupportPerson notice the space between
== and "

I can see that this is a problem but I do not know if it is the entire
answer for you. Try to adjust concatenations to allow appropriate white
space and try it.

Jim Evans

 

[Dan Artuso]

Hi Anne,
If StudyType is text then you need to delimit the criteria with quotes:

myCriteria = " Where StudyType ='" & Me.cbo_StudyType & "'"

 

[Guest]

Thanks very much Dan! Worked like a charm!

Hi Anne,
If StudyType is text then you need to delimit the criteria with quotes:

myCriteria = " Where StudyType ='" & Me.cbo_StudyType & "'"