Proper way to use Variables in access 2007 vba sql

Hi williecg,

Any strings need to be encased in single quotes.

i.e. "SELECT * FROM myTable WHERE '" & strNewDonorNum & "' = 5"

Sorry, bad example:

"SELECT * FROM myTable WHERE '" & strNewDonorNum & "' = 'XXXXX'"

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)

Select allOpen in new window

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

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

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

Forgot to add ... so use double quotes instead.

mx

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

RE: so use double quotes instead
You mean double double quotes. ;-p
Example: ' = ""
Lee

"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

"You mean double double quotes. ;-p"

Well ... not exactly :-)

mx

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.