rfreud
asked on
Syntax for a numeric value in where clause in VBA using MS Access
I want to open a recordset (rst1) based on a numeric value (called "ControlNumber"). The value of the ControlNumber is obtained from an input box. The ControlNumber is an integer value.
Here is the line of code that doesn't work:
rst1.Open "Select * From qry_Myquery Where ControlNumber = """ & varControlNumber & """"
I can't figure out where the tick marks go. Thanks in advance.
Ron
Here is the line of code that doesn't work:
rst1.Open "Select * From qry_Myquery Where ControlNumber = """ & varControlNumber & """"
I can't figure out where the tick marks go. Thanks in advance.
Ron
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Neil Russell
rst1.Open "Select * From qry_Myquery Where ControlNumber = " & cstr(varControlNumber)
" The ControlNumber is an integer value. "
rst1.Open "Select * From qry_Myquery Where ControlNumber = " & CInt(varControlNumber)
rst1.Open "Select * From qry_Myquery Where ControlNumber = " & CInt(varControlNumber)
" The ControlNumber is an integer value. "
Yes and you want to add it to a string!
Hence cstr(varControlNumber)
Yes and you want to add it to a string!
Hence cstr(varControlNumber)
OP states the Control Number is numeric. Input Box changes it to Text ... CInt() convert back to Numeric.
The WHERE condition is looking for Numeric, not Text since Control Number is Numeric.
The WHERE condition is looking for Numeric, not Text since Control Number is Numeric.
Neither of the conversion functions is required. The "&" is the VBA concatenation operator. Access "knows" you are building a string (since that is the only output type after concatenation) and handles whatever conversion is necessary. That makes cstr() redundant and cInt() is unnecessary since ControlNumber is already an integer.
The where condition might be looking for numeric but you are using a string to contain the SELECT statement. That string uses & to concatenate the varControlNumber. There is absolutely no reason on earth to change that to an integer and you are concatenating it onto a string.
@PatHartman, that is not in reply to your post, posted as I was typing. And yes in principle you are correct, I used the cstr as a force of habit as that is what is being used, a string.
ASKER
Thank you all for your quick response. I tried the first solution from omgang, which did not require changing the value of the control number obtained in the input box from a string back to a value as suggested by others, and it worked just fine.
ASKER
Can't thank you guys enough for the help you provide. Great work!!
"There is absolutely no reason on earth to change that to an integer and you are concatenating it onto a string."
I'm sorry, but you are incorrect. Look at the Accepted Solution ... same as mine, w/o the Cint().
rst1.Open "Select * From qry_Myquery Where ControlNumber = " & varControlNumber
rst1.Open "Select * From qry_Myquery Where ControlNumber = " & CInt(varControlNumber)
In a WHERE clause, Numeric values require no double quotes. Text data types require double quotes and Date data types pound signs.
I'm sorry, but you are incorrect. Look at the Accepted Solution ... same as mine, w/o the Cint().
rst1.Open "Select * From qry_Myquery Where ControlNumber = " & varControlNumber
rst1.Open "Select * From qry_Myquery Where ControlNumber = " & CInt(varControlNumber)
In a WHERE clause, Numeric values require no double quotes. Text data types require double quotes and Date data types pound signs.
And that is exactly what I posted....
rst1.Open "Select * From qry_Myquery Where ControlNumber = " & cstr(varControlNumber)
No double quotes.
Looking at the accepted solution means nothing about what I said. there is absolutely no reason to convert to an INT. end of.
rst1.Open "Select * From qry_Myquery Where ControlNumber = " & cstr(varControlNumber)
No double quotes.
Looking at the accepted solution means nothing about what I said. there is absolutely no reason to convert to an INT. end of.
"but you are using a string to contain the SELECT statement."
" and you are concatenating it onto a string"
That is where (no pun?) you are confused. That is not how Access SQL WHERE clauses work, per the three examples I posted above.
" and you are concatenating it onto a string"
That is where (no pun?) you are confused. That is not how Access SQL WHERE clauses work, per the three examples I posted above.
Unfortunately it is you who is confused. The where clause is INSIDE a string. The whole string as per my post would evaluate to....
rst1.Open "Select * From qry_Myquery Where ControlNumber = 1" when varControlNumber is equal to 1
And that is the correct statement. Any way enough of hijacking a question "DatabaseMX"....
rst1.Open "Select * From qry_Myquery Where ControlNumber = 1" when varControlNumber is equal to 1
And that is the correct statement. Any way enough of hijacking a question "DatabaseMX"....
Hijacking ? LOL.
Actually, without seeing more of the code, Joe is correct.
It's considered a best practice to explicitly type a variable whenever you have an unknown data type , In this case nothing really bad will happen because as Pat pointed out, the concatenation operator will do the conversion to a string from whatever data type you supply if it can, but it may not do what you think.
There are some operations though (i.e. working with Dates) where you can have subtle problems come in if you don't explicitly type something. gustav just answered a question last week on dates and the issue was because the variable involved wasn't explicitly typed. So while it works, your playing it fast and loose a bit.
Given that you have an input box and the user might type anything, you probably want to either explicitly type it or perform a range of checks to ensure that it is an integer. Simplest would be to do the cast, but either works.
In this case, if the user typed 12345A1 and had a typo, it won't get caught. But if you used Cint() it would (the concat would stop at the letter with no error looking for 12345, but the Cint() would give you a type mis-match at input).
It's also true that Cstr() is not really needed, but there's no harm in using it. The concatenation operator is going to see that what is being appended is a string, and will just skip the conversion.
Jim.
It's considered a best practice to explicitly type a variable whenever you have an unknown data type , In this case nothing really bad will happen because as Pat pointed out, the concatenation operator will do the conversion to a string from whatever data type you supply if it can, but it may not do what you think.
There are some operations though (i.e. working with Dates) where you can have subtle problems come in if you don't explicitly type something. gustav just answered a question last week on dates and the issue was because the variable involved wasn't explicitly typed. So while it works, your playing it fast and loose a bit.
Given that you have an input box and the user might type anything, you probably want to either explicitly type it or perform a range of checks to ensure that it is an integer. Simplest would be to do the cast, but either works.
In this case, if the user typed 12345A1 and had a typo, it won't get caught. But if you used Cint() it would (the concat would stop at the letter with no error looking for 12345, but the Cint() would give you a type mis-match at input).
It's also true that Cstr() is not really needed, but there's no harm in using it. The concatenation operator is going to see that what is being appended is a string, and will just skip the conversion.
Jim.