DLookup confusion - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

DLookup confusion

Bruce McCormick

2 years ago

HAPPY THANKSGIVING!!!

And thank you for considering this-

Herefollows my trials in the immediate window in the VBE:

-------------------
? sZip
22405-1929
? DLookup("UseTax", "tblTaxRate", "Zip = ""22405-1929""")
0.053
? DLookup("UseTax", "tblTaxRate", "Zip = " & sZip)
Null
? vartype(sZip)
8
-------------------

If my eyes and brain are accurate i get the correct return on the literal conditional statement, but when i use a variable that is set to the exact same thing and is proven to be of string vartype (-8-), it comes back null. In the tblTaxRate the Field Name of Zip is verified as Short Text.

I am rather confused here - am i missing something?

Thanks very much!
-Bruce

	Bruce McCormick OP @Reply 2 years ago
Wondering if somehow the "-" in the variable sZip is being interpreted as an operation rather than a character and possibly i might need to use a "chr" type of expression somehow or another permutation of the criteria portion. I specifically note that in the first implementation of DLookup i did NOT use: "Zip = " & ""22405-1929""") As they say, back to the drawingboard...

	Kevin Robertson @Reply 2 years ago
You need Double Double Quotes `DLookup("UseTax", "tblTaxRate", "Zip=""" & sZip & """")`

	Bruce McCormick OP @Reply 2 years ago
You know, that's what i thought also! Herebelow was my attempt just a bit earlier than followed by my trying a copy of that which you wrote just in case i misspelled something or other dumb move: ? DLookup("UseTax", "tblTaxRate", "Zip = """ & sZip & "") Null ? DLookup("UseTax", "tblTaxRate", "Zip=""" & sZip & """") Null

	Bruce McCormick OP @Reply 2 years ago
Ooops: "than" s/b "then", and i honestly did try it wih three " at the end, along with every combination i could come up with ranging into four of them there " marks just out of frustration (I believe my actions are called "shooting in the dark", but sometimes i actually hit something!)

	Kevin Robertson @Reply 2 years ago
Your variable probably has nothing in it. Try it in a button. `Dim sZip As String sZip = "22405-1929" Debug.Print DLookup("UseTax", "tblTaxRate", "Zip=""" & sZip & """")`

	Bruce McCormick OP @Reply 2 years ago
Oh $%&&. You were 99.9% right originally and i did not test that instance previously! The only modification i had to made was another one of my desperation shooting in the dark moves: i stuck a space between the = and """ (i copied the code directly from your post as i didn't trust myself) and now it works!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View