SQL question

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Hi Anne,
If StudyType is text then you need to delimit the criteria with quotes:

myCriteria = " Where StudyType ='" & Me.cbo_StudyType & "'"
 
Thanks very much Dan! Worked like a charm!

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

myCriteria = " Where StudyType ='" & Me.cbo_StudyType & "'"
Click to expand...
 
Back
Top