DLOOKUP where criteria is text - Microsoft Access Expert 10


	Back to Access Expert 10 Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

DLOOKUP where criteria is text

Kim Mittet

3 years ago

I have a field where i want to put a value from a DLOOKUP. The value is from tabel "Defaultdata" and i want to get the value from field "Value1". The criteria field in the tabel is "DefaultYear" and should be equel to a field in the current form named "Year". The "Year" field is a textformat field. My DLOOKUP looks like this :
=DLookUp("Value1";"Defaultdata";"DefaultYear=""" & [Year] & """").
But it do not work as intended :-)

	Kevin Robertson @Reply 3 years ago
Year is a reserved word and shouldn't be used as a field or control name. Access Reserved Words Also, use commas in your DLookup function, not semi-colons.

	Kim Mittet OP @Reply 3 years ago
Sorry, should have used the correct name which is "BudgetYear" I used the Expressionbuilder which uses the semi-colon

	Kevin Robertson @Reply 3 years ago
Is there a reason 'BudgetYear' is a Text field and not a Number? You say it doesn't work as intended - can you elaborate?

	Kim Mittet OP @Reply 3 years ago
The errormessage is : "The entered expression contains nonvalid syntax - There is missing a operand or operator , you may have entered a nonvalid character, a comma instead of a semicolon, a semicolon instaed of a comma, or maybe you have enter'd text without without putting quotes around the text" The errormessage is translated from another language - i hope it is understandable

	Kim Mittet OP @Reply 3 years ago
Can anyone explain why i get an error in the field for this DLookUp. I get no error when i save the expression, but when i display the form, the field with the DLookUp as control source only display : #Error Table : XXX Tablefield : YYY Tablefield2 :WWW Formfield : ZZZ (textformat) =DLookUp("YYY";"XXX";"WWW =" & "ZZZ")

	Kevin Robertson @Reply 3 years ago
`=DLookUp("YYY","XXX","WWW =""" & ZZZ & """")` DLookup Double Double Quotes

	Scott Axton @Reply 3 years ago
Check out the Common Formats section on the DLookup Page. I believe that the "StateName" lookup is most similar to what you are describing. More than likely you will want to enclose your formula in the NZ() function. See the NZ Function Video.

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

Next Unseen

New Feature: Comment Live View