Nested Dlookup syntax


I have a text box for MailCity, MailState  MailZip

City and Zip are straight from table "Company", but State is a numeric table/query field in Company that looks  at table "State" using field "StateID" as the common identifier.

=DLookUp("[MailCity] & ', ' & [MailStateID] & '  ' & [MailZip]","CompanyTBL","[CompanyID]=" & [CompanyNameID])

will return Seattle, 47  98272  which is the correct stateID, but didn't go find the state name from table "State"

=DLookUp("[MailCity] & ', ' & DLookUp("[MailStateID]","StateTBL","[StateID]=" & [company.StateID]) & '  ' & [MailZip]","CompanyTBL","[CompanyID]=" & [CompanyNameID])

just gives me an error, so I figure I'm kind of on the right track with nesting the lookup, but have the wrong syntax for it.

Help?

Thanks,
Joy
fudgekittenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

thenelsonCommented:
=DLookUp("[MailCity]","CompanyTBL","[CompanyID]=" & [CompanyNameID]) & ", " &  DLookUp("[MailStateID]","StateTBL","[StateID]=" & [company.StateID]) & " " & DLookUp("[MailZip]","CompanyTBL","[CompanyID]=" & [CompanyNameID])
fudgekittenAuthor Commented:
Almost.  It returns "#name?"

Cause it needs to somehow get the [CompanyID]=" & [CompanyNameID] in there? how do you do a nested  state from tblstate where company from tblcompany?

I'm so confused....

Joy
thenelsonCommented:
Missed that piece, how about this:
=DLookUp("[MailCity]","CompanyTBL","[CompanyID]=" & [CompanyNameID]) & ", " &  DLookUp("[MailStateID]","StateTBL","[StateID]=" & Dlookup("StateID","CompanyTBL","[CompanyID]=" & [CompanyNameID])) & " " & DLookUp("[MailZip]","CompanyTBL","[CompanyID]=" & [CompanyNameID])

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

fudgekittenAuthor Commented:
Yes!

We both had our states and mailstates and such mixed around, but with some laying it all out and checking the tables and names, I came up with

DLookUp("[State]","MailStateTBL","[MailStateID]=" & DLookUp("[MailStateID]","CompanyTBL","[CompanyID]=" & [CompanyNameID]))

as the middle section and it works!! Thank you for your help!  I Knew it was nesting syntax thing I was doing wrong!

Peace,
Joy
thenelsonCommented:
And thank you!  I didn't realize you could put two fields in one lookup as in:
=DLookUp("[MailCity] & ', ' & [MailZip]","CompanyTBL","[CompanyID]=" & [CompanyNameID])

Ah, peace,
Would be nice -- very nice.
Nelson
fudgekittenAuthor Commented:
Wait a minute!!!

*I* actually taught someone *else* something??!?!??!??!???

WOoooHooOOOOOOOOOooooOOOOO!!!!!!!!!!

I should award myself a few points!!

Joy
thenelsonCommented:
Your parents were very insightful!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.