DLookup criteria issues - does form need to be refreshed?

[tvsmvp]

I've got "dlookup" working just fine: I enter a zip code, it looks up the correct city and state name abbreviation. But... I when I enter that zip, I also want to retrieve the full state name (in "proper" format) from another table based on the state abbreviation retrieved. It ain't working. Is it because the table or record has to be saved or refreshed before I can pull the proper state name from that second table?

 

[rjoubert]

What does your DLookup function look like? Can you post the expression? Since the state name is in a separate table, you may need to create a query that links the two tables together and do your DLookup on the query.

 

[tvsmvp]

The two lines that work look like this:
Me![City].Value = DLookup("[City]", "ZipCodes", "[ID] = " & Forms!Facilities!ZipCode)
Me![StateAbbreviated].Value = DLookup("[State]", "ZipCodes", "[ID] = " & Forms!Facilities!ZipCode)

The one (the third) that doesn't work is this:
Me![StateNotAbbreviated].Value = DLookup("[StatesProper]", "StatesList", "[StatesAbbrev] = " & Forms!Facilities!StateAbbreviated)

I also just tried saving the record (before that third line) with this: DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 - and unfortunately that doesn't help things.

 

[rjoubert]

I think you need single quotes in your third DLookup call...

Me![StateNotAbbreviated].Value = DLookup("[StatesProper]", "StatesList", "[StatesAbbrev] = '" & Forms!Facilities!StateAbbreviated & "'")

 

[rjoubert]

Sorry, that's probably tough to read...

Me![StateNotAbbreviated].Value = DLookup("[StatesProper]", "StatesList", "[StatesAbbrev] = '" & Forms!Facilities!StateAbbreviated) & "'"

 

[tvsmvp]

I figured it out! Yes - I love it when this happens. If you're having the same problems, it's 'cause you're using text instead of numbers as part of your criteria. You need to add single quotes (maybe obvious to some, not to this newbie) Do a search for the following in access help: "Textual Criteria from a Control on a Form

 

[rjoubert]

Hmmm...add single quotes...you mean like I did in my last reply to your post?

 

[tvsmvp]

Yes, exactly - thanks for the help. Apparently I hadn't received an "auto email" re your answer by the time I'd posted by last email.

Thanks again