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
rfreudAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
rst1.Open "Select * From qry_Myquery Where ControlNumber = """ & varControlNumber & """"

Wrapping the variable varControlNumber in double quotes is saying "this is a string value"

Try
rst1.Open "Select * From qry_Myquery Where ControlNumber = " & varControlNumber

OM Gang

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Neil RussellTechnical Development LeadCommented:
rst1.Open "Select * From qry_Myquery Where ControlNumber = " & cstr(varControlNumber)
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
" The ControlNumber is an integer value.  "


rst1.Open "Select * From qry_Myquery Where ControlNumber = " & CInt(varControlNumber)
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Neil RussellTechnical Development LeadCommented:
" The ControlNumber is an integer value.  "

Yes and you want to add it to a string!

Hence cstr(varControlNumber)
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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.
PatHartmanCommented:
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.
Neil RussellTechnical Development LeadCommented:
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.
Neil RussellTechnical Development LeadCommented:
@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.
rfreudAuthor Commented:
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.
rfreudAuthor Commented:
Can't thank you guys enough for the help you provide.  Great work!!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"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.
Neil RussellTechnical Development LeadCommented:
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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"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.
Neil RussellTechnical Development LeadCommented:
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"....
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Hijacking ? LOL.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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 more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.