Hi.

I have this issue with a SQL statement that uses strings as part of the Where clause. I search this database for similar situations, found some responses and tried them but still could not get my code to work.

I'm using VB 6.0 to retrieve some rows from an MS Access table.
Here's my code:
 sSql = "Select * From SCR_Log Where BaseSCRID = " & lBaseSCRID
 sSql = sSql & " And EmailSubject = '" & constantAValue
 sSql = sSql & "' And UsedFlag = " & constantNo
 oRS.Open sSql, oConn, adOpenDynamic, adLockOptimistic

Here's the value of SQL after setting it as above in VB Watch mode:
"Select * From SCR_Log Where BaseSCRID = 45000 And EmailSubject = 'SCR2K2 Email Notification Change' And UsedFlag = No"

BaseSCRID is a Number, EmailSubject is Text and UsedFlag is Yes/No.

I also tried using double quotes but that didn't work either.
Here's the value of sSql with double quotes that also does not work:
"Select * From SCR_Log Where BaseSCRID = 45000 And EmailSubject = "SCR2K2 Email Notification Change" And UsedFlag = No"

Another thing I tried to use was chr(34) but still no success.

Any suggestions would be greatly appreciated.

Rita

did you try this: in query change UsedFlag = No " to UsedFlag = 0?
bfurman.

Hello,

It seems you might have made a small error. The sql result should look like this when you debug it:

Select * From SCR_Log Where BaseSCRID = '45000' And EmailSubject = 'SCR2K2 Email Notification Change' And UsedFlag = 'No'

To get it to look like that your sqlstring should be like this:

sSql = "Select * From SCR_Log Where BaseSCRID = " & "'" & lBaseSCRID & "'" & _
" And EmailSubject = '" & constantAValue & "' And UsedFlag = " & _ "'" & constantNo & "'"

Hope that helps
Paul

Paul

Thanks to all for your replies.

I did some further searching on this site and this time searched for the error message I was getting - "No value given for one or more required parameters".

I found one reply where it was stated that one of the parameters could be misspelled. I checked the table design (I had already done this) and on looking closely at the "EmailSubject" column saw that it actually was "Email Subject"!!

That was my problem - a misspelled parameter. I made the fix and this time the code worked (I used the one with the double quotes).

If BaseSCRID is a number, i.e. 4500, then there should be no quotes around it. Also, the Yes/No is a flag which is -1 or zero or True or False. Therefore, your end result should look like this.

"Select * From SCR_Log Where [BaseSCRID] = 45000 And [EmailSubject] = 'SCR2K2 Email Notification Change' And [UsedFlag] = False"

To get it to look like that your sqlstring should be like this:

sSql = "Select * From SCR_Log Where [BaseSCRID] = " & lBaseSCRID & _
" And [EmailSubject] = '" & constantAValue & _
"' And [UsedFlag] = False"


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks for the clarification Greg.

It is as you say but I also got it to work when I checked the Yes/No flag for No in the SQL statement. As long as there were no quotes around the NO it worked.

Rita