Quotes in a query: when to use single, double and none at all

[tvsmvp]

Well, I've shot the last six hours or so trying to figure out where, how many and what type of quotes to use. I know this has got to be pretty simple - but I can't seem to see the rhyme and reason to how/when to use them.

I'm loading up the row source for a list box with a query. Picking up with the "where" portion... This (below) is the closest I've gotten. It's close - but it throws the correct values into the query (design view) surrounded by single quotes. I can't seem to get just the value. Both fields listed in the "where" portion are numeric.

WHERE (((Articles.ID) = '" & Me!ID.Value & "') And ((GetWordCount([KeyWord], 1, [Article])) > '" & Me.KeyWord_1.Value & "' )) ORDER BY KeyWords.RS DESC;"

Do I need to use double/triple quotes somewhere to "escape" the ones I've used?

 

[tvsmvp]

Okay - I stuck with it, burned a few more gray hairs and figured it out. The winning code looks like this:

WHERE (((Articles.ID) = " & [ID] & ") And ((GetWordCount([KeyWord], 1, [Article])) > " & Me.KeyWord_1.Value & ")) ORDER BY KeyWords.RS DESC;"

Apparently the two variables had to be treated differently because the control behind one was simply the auto number "ID" (text box) while the other was created by a UDF. At least that's my best guess after 8 hours of hair-pulling.

You'll see that this worked for one: " & [ID] & " while this worked for the second: " & Me.KeyWord_1.Value &

 

[PHV]

Both fields listed in the "where" portion are numeric
So, no single quote neaded.
Examples (for JetSQL):

strWHERE = "WHERE [numeric field]=" & [numeric control] _
 & " AND [text field]='" & [text control] & "'" _
 & " AND [date field]=#" & [date control] & "#"

Safer way:

strWHERE = "WHERE [numeric field]=" & [numeric control] _
 & " AND [text field]='" & Replace([text control], "'", "''") & "'" _
 & " AND [date field]=#" & Format([date control], "yyyy-mm-dd") & "#"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tvsmvp]

Thanks for the info - I'll use it and keep that bookmarked.