Link to home
Start Free TrialLog in
Avatar of fudgekitten
fudgekitten

asked on

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
Avatar of thenelson
thenelson
=DLookUp("[MailCity]","CompanyTBL","[CompanyID]=" & [CompanyNameID]) & ", " &  DLookUp("[MailStateID]","StateTBL","[StateID]=" & [company.StateID]) & " " & DLookUp("[MailZip]","CompanyTBL","[CompanyID]=" & [CompanyNameID])
Avatar of fudgekitten
fudgekitten

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of thenelson
thenelson
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fudgekitten
fudgekitten

ASKER

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
Avatar of thenelson
thenelson
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
Avatar of fudgekitten
fudgekitten

ASKER

Wait a minute!!!

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

WOoooHooOOOOOOOOOooooOOOOO!!!!!!!!!!

I should award myself a few points!!

Joy
Avatar of thenelson
thenelson
Your parents were very insightful!