Syntax with quotations

[AUGuy]

Syntax with quotations / DLookup

I've been attempting to use DLookup in a function i'm writing and had many problems getting it to work properly. My first attempt after reading the MS help file was:

[B][COLOR=red]?Dlookup("[UserName]","tbl_AuthUserList","[UserID] = strUserID")[/COLOR][/B]

Which didnt work... So i tried all manner of variations of putting:
"[UserID] = ' & strUserID & '") for the criteria as well as many others. Basically just trying to fiddle with it until it worked, right?

Well none of that worked so i searched aruond on the forums and found a poster who was using Dlookup in a similar manner. Their code was:

[B][COLOR=blue]?DLookup("[UserName]", "tbl_AuthUserList", "[UserID] = '" & strUserID & "'")[/COLOR][/B]

Which, when i plugged this in, it worked perfectly. My question is about the syntax of the single and double quotations. This isnt the first time I've been stuck on this and I think I'm missing some fundamental understanding here, so any explanation would help. Why does it have to be in this manner to work? Why is there a Double then Single then Double Qutation instead of a Double then Double then Single?

Both UserName and UserID are text fields.

Thanks! Guy

 

[hk1]

any time you are using sql statements to reference values in a text field you have to surround the given text parameter with single quotes. So the actual value you are passing in actually looks like this:
[UserID] = 'JohnDoe'

 

[AUGuy]

any time you are using sql statements to reference values in a text field you have to surround the given text parameter with single quotes. So the actual value you are passing in actually looks like this:
[UserID] = 'JohnDoe'

I guess I wasn't really thinking of it as a SQL statement because I was coding in VBA. I suppose it is, though. Good to know

 

[hk1]

Yeah, it's basically a WHERE statement minus the WHERE. This same thing is true if you ever use DAO's FindFirst method.

rst.FindFirst "FirstName = '" & John & "'"
If rst.NoMatch = False Then
'Do Something
End If

 

[boblarson]

Also, in the second instance the variable is OUTSIDE of the normal quotes because you want to pass the VALUE of the variable, not the variable name itself.

So this:
?DLookup("[UserName]", "tbl_AuthUserList", "[UserID] = '" & strUserID & "'")
keeps strUserID outside of the WHERE clause string so it will get the VALUE of strUserID instead of it trying to evaluate what strUserID means.

Also, using single quotes is fine but if you have say a business name field, what happens when you have Sid's Bar? It will blow up on you because of the single quote. So I use Chr(34) which is an ASCII character code for a double quote. You can also use triple-double quotes but that to me gets confusing (""").

?DLookup("[UserName]", "tbl_AuthUserList", "[UserID] = " & Chr(34) & strUserID & Chr(34))

or

?DLookup("[UserName]", "tbl_AuthUserList", "[UserID] = " & """ & strUserID & """)

 

[AUGuy]

Great info, thanks to both of you