Here is the basic principle.
When you are comparing two fields in a dynamic SELECT such as you are building, your issue will be that the quotes are going to be complex. Any numeric sequence that contains punctuation - other than leading sign, possible single embedded dot (as decimal point) or comma (decimal point if you are using the alternative number delimiters) - is not a number. It is a text string. You have to treat it as such.
When you build a "like" sequence, you can use wildcards such as "*" to represent arbitrary strings. However, with concatenation, you run into problems sometimes because of the need to get the dynamic field value into place in your SELECT clause.
The trick is to remember simple rules of thumb.
1. Inside a quoted string, to show a quote takes two quotes.
2. If the source of the search value is fixed in size, a "like" search by itself might not be right. Further number-muddling might be in order.
So here is a fragment based on stQry as the string in which I am building my SELECT clause based on the contents of an unbound text box, call it [LookFor], on a form as the source and [LookIn], a bound item, as the thing that holds what I wanted to find.
stQry = stQry & "AND [LookIn] LIKE ""*"
stQry = stQry & Trim$([LookFor])
stQry = stQry & "*"""
The first line adds in AND [LookIn] LIKE "*
The next line adds in a compressed version of the [LookFor] data.
The third line adds in *" to finish the line.
You are left with
AND [LookIn] LIKE "*value*"
The numeric fields are a bit easier. For these, you cannot do a "like" search but you could do other searches including a "between" search. Look that one up if you need it.
Let us take the same fields but make [LookIn] a LONG INTEGER. Let us say we want values equal to [LookFor], again a form's text box.
stQry = stQry & " AND [LookIn] = "
stQry = stQry & Trim$([LookFor])
You just added AND [LookIn] = number-string
Since [LookIn] is numeric, you don't need quotes. But the text box on the form is still a TEXT box, not a number box. The trick here is that SQL will detect a numeric field reference and will make the correct conversions at run-time on number-string.
Remember, if [LookIn] can ever contain ANY UNUSUAL PUNCTUATION AT ALL, it is not a number and cannot be searched as such.
The error you report is probably due to unbalancing your double-quotes. The way to test this is to hand-build a string following these rules:
Base rule: Quotes come in pairs. They GO in pairs, too. VBA gets upset when they don't.
1. Any time you concatenate with "XYZ", take off the leading and trailing quotes and insert the rest, which in this case would be XYZ.
2. Any time your remainder from step 1 contains the sequence "" (i.e. AFTER you have removed the outermost quotes), remove one of the embedded quote marks and store the other.
So stQry = """XYZ""" leaves stQry containing "XYZ"
and stQry = "XYZ" leaves stQry with XYZ
But stQry = ""XYZ"" leaves VBA guessing. (Count the quotes to see why.)
Olivia, please check pairings on your use of quotes. That is what the message is telling you. And I have never had success trying to use ' in place of " anytime.
And thanks for your kind words, but please limit your replies via e-mail. If you were to check my address, you would note it is part of the USA MILNET. It is OK for a short note and no harm is done, but long messages to me or anything that included a program fragment would probably trip every alarm in the house.
So I'm not saying don't mail me. But keep it brief and make any major communications through the forum.
Hope the above helps you, Olivia.
