Syntx error

  • Thread starter Thread starter maceslin
  • Start date Start date
M

maceslin

I am getting a syntax error message in the following, any suggestions

strReviewDateCriteria = "Not (tblStatusChoices.status) = "action
complete""

Thanks
Dave
 
I am getting a syntax error message in the following, any suggestions

strReviewDateCriteria = "Not (tblStatusChoices.status) = "action
complete""

Thanks
Dave
Click to expand...

Try:

strReviewDateCriteria = "Not (tblStatusChoices.status) = 'action complete'"
strReviewDateCriteria = Replace(strReviewDateCriteria, "'", Chr$(34))

ie use single quotes when you build a string, then use the Replace function
to substitute them with double-quotes.
 
Stuart McCall said:
Try:

strReviewDateCriteria = "Not (tblStatusChoices.status) = 'action
complete'"
strReviewDateCriteria = Replace(strReviewDateCriteria, "'", Chr$(34))

ie use single quotes when you build a string, then use the Replace
function to substitute them with double-quotes.
Click to expand...


Is there any actual need to replace the single-quotes with double-quotes in
this case? I don't see any. And what's wrong with this:

strReviewDateCriteria = "tblStatusChoices.status <> 'action complete'"

?
 
Dirk Goldgar said:
Is there any actual need to replace the single-quotes with double-quotes
in this case? I don't see any.
Click to expand...

Neither do I now you mention it. Still, it demonstrates a useful enough
technique...
And what's wrong with this:

strReviewDateCriteria = "tblStatusChoices.status <> 'action complete'"

?
Click to expand...

Yes that would work too. Do you think it'd make a difference?
 
Stuart McCall said:
Neither do I now you mention it. Still, it demonstrates a useful enough
technique...
Certainly.


Yes that would work too. Do you think it'd make a difference?
Click to expand...

At run time? No. My guess is that it would be easier to maintain, just
because the logic is clearer.
 
Dirk Goldgar said:
At run time? No. My guess is that it would be easier to maintain, just
because the logic is clearer.
Click to expand...

You have a point. I was concentrating on the quotes issue and missed that.

Another thing just occurred to me: your sql syntax is also more portable. If
you were to be querying via say VB using DAO, the word Not wouldn't be
recognised by Jet because it's interpreted by the VBA system (which works in
Access because the folks at Redmond made it so by allowing VBA keywords in
sql strings).
 
Back
Top