Syntx error

[maceslin]

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

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

Thanks
Dave

 

[Stuart McCall]

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

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

Thanks
Dave

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.

 

[Dirk Goldgar]

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.

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'"

?

 

[Stuart McCall]

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

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'"

?

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

 

[Dirk Goldgar]

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?

At run time? No. My guess is that it would be easier to maintain, just
because the logic is clearer.

 

[Stuart McCall]

At run time? No. My guess is that it would be easier to maintain, just
because the logic is clearer.

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).