Proper way to use Variables in access 2007 vba sql

I have a form with a text box.

I need to use the value in the text box in an access 2007 vba sql.

I am new to this and do not know how to use variables properly in sql.

When I replace the variable strNewDonorNum with “XXXXX” in the sql it works.  

What is the proper way to use variables in sql?  

The sql is below.

It produces an error, Too few parameters.  Expected 1.

Thanks,
Cg

 strNewDonorNum = Forms.fmnuNew_Donor_Menu.txtNew_Donor_Num

Set rst = db.OpenRecordset( _
"SELECT tblDonor_Info.D_Num AS [Match], tblDonor_Info.D_Age FROM tblDonor_Info WHERE (((tblDonor_Info.D_Num)= strNewDonorNum));")
williecgAsked:
Who is Participating?
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.

ioanePlanning & Analytics ManagerCommented:
Hi williecg,

Any strings need to be encased in single quotes.

i.e. "SELECT * FROM myTable WHERE '" & strNewDonorNum & "' = 5"
0
ioanePlanning & Analytics ManagerCommented:
Sorry, bad example:

"SELECT * FROM myTable WHERE '" & strNewDonorNum & "' = 'XXXXX'"
0
lee555J5Commented:
I would put the SQL statement in a string variable first--it is a little easier to read.
Because you are matching a string, it needs the single quotes. A number match would not.
The "&" is for string concatenation.
txtNewDonorNum is the name of the textbox on the form.
The [ ] around Match are unnecessary and may cause problems. I would take them out.
Lee

Dim db As DAO.Database, rst As DAO.Recordset, strSQL As string

strSQL = "SELECT tblDonor_Info.D_Num AS [Match], tblDonor_Info.D_Age FROM tblDonor_Info WHERE tblDonor_Info.D_Num = '" & txtNewDonorNum & "';"

Set rst = db.OpenRecordset(strSQL)

Open in new window

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

lee555J5Commented:
The way I did it, you do not need the string variable strNewDonorNum.
My table name in the WHERE clause should have been txtNew_Donor_Num
Also, in this example, if this code is in this form's module, you do not need the full reference to the textbox. 'Me' represents the current form.
No: Forms.fmnuNew_Donor_Menu.txtNew_Donor_Num
Yes: txtNew_Donor_Num OR Me.txtNew_Donor_Num OR Me!txtNew_Donor_Num
@Tramtrack Both strNewDonorNum and 'XXXXX' are the variables he is trying to match. The WHERE part to the left of the "=" needs to be the table field he is trying to match as in his OP.
Lee
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
RE:

Yes: txtNew_Donor_Num OR Me.txtNew_Donor_Num OR Me!txtNew_Donor_Num

Actually, using Me is the preferred method.  And use Me DOT ... ie

Me.txtNew_Donor_Num   ... that way you have Intellisense working for you.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
RE:
"Any strings need to be encased in single quotes."

Actually, single quotes are problematic in Access ... because, if the string itself contains a single quote, it will fail ... like in O'Brian , O'Reilley, O'MeoMyo ... etc.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Forgot to add ... so use double quotes instead.

mx
0
lee555J5Commented:
RE: Actually, single quotes are problematic in Access
I agree. However, several double quotes are a pain in the 4$$ to read and troubleshoot. Pick your poison.
Lee
0
lee555J5Commented:
RE: so use double quotes instead
You mean double double quotes. ;-p
Example: ' = ""
Lee
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Meanwhile ... lets try this:


strNewDonorNum = Me.txtNew_Donor_Num

Set rst = db.OpenRecordset( _
"SELECT tblDonor_Info.D_Num AS [Match], tblDonor_Info.D_Age FROM tblDonor_Info WHERE tblDonor_Info.D_Num = " & Chr(34) & strNewDonorNum & Chr(34)

Chr(34) is a double quote and used for clarity in posting.

This assumes that tblDonor_Info.D_Num IS a text data type (?)  If it's Numeric, then you would use this

Set rst = db.OpenRecordset( _
"SELECT tblDonor_Info.D_Num AS [Match], tblDonor_Info.D_Age FROM tblDonor_Info WHERE tblDonor_Info.D_Num = " &  strNewDonorNum

mx
0

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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"Pick your poison."

That's not really the issue.  And I almost always use Chr(34) for a double quote because it's easier to read and eliminates multiple double quotes.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"You mean double double quotes. ;-p"

Well ... not exactly :-)

mx
0
Leigh PurvisDatabase DeveloperCommented:
I saw this earlier and meant to comment. (By cheating and repeating myself lol - gotta love copy paste from your own offerings. Self Plagiarism ... Hmmm...).
The problem for me with simply switching to the (theoretically) less likely delimiter (double quote) is two-fold.
Firstly the double quote isn't ANSI standard. So what? We're working with a Jet BE here and via Access. But we may not always be. A standard we adopt early can see code left very much as is for a long time.
Secondly, again for consistency, it lets you implement all fields similarly. What do I mean by this? Well Surname is moderately likely to contain a single quote - as a mate of mine, Miles O'Brien, was telling me. But what about a field named "ProductDescription". In my distant past I've had such a field and allowed a user to search upon it.
Guess what - naturally... They'd entered text such as
2x2" batton
Boom!
If you go with doubling delimiters as a standard then you're protected against either delimiter in your data.
And then, IMO, once you're doing that then you might as well stick with the single quote ANSI standard.
You can knock up a simple little function to lessen your effort and enforce consistency. e.g.
Function fSQLString(varVal, Optional strDelim As String = "'")
    If IsNull(varVal) Then Exit Function
    fSQLString = strDelim & Replace(varVal, strDelim, strDelim & strDelim) & strDelim
End Function
Which you'd call such as:
strSQL = strSQL & " WHERE TextField = " & fSQLString(strCriteria)
Functions don't come much simpler, but anything you can do to standardise your work is worthwhile - and yet leave the door open for very easy and immedite change (i.e. change the default value for strDelim and you're in a completely different mode throughout).
By the way - the above in no way indicates a distaste for double quote reading.
It's a question of determination. I, long ago, trained myself to read
"WHERE FieldName = """ & strVal & """"
just as easily as
"WHERE FieldName = " & Chr(34) strVal & Chr(34)
I just feel single quote is more standard. (And as a side result, helping show the world that Access - and Jet - can be standard is no bad thing).

Cheers.  
0
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.