DLookup Function

[SHAWTY721]

I am receiving #Error in my unbound textbox where I am trying to use the DLookup Function to lookup the value that corresponds with "ADPCompany" and "Location Number" fields. Here is what my DLookup Function looks like:
' =DLookup("BranchNo","tblAllADPCoCodes","ADPCompany=" & Forms![frmMarthaPerPayPeriodDeduction]![ADPCompany])&","& DLookup("BranchNo","tblAllADPCoCodes","LocationNumber=" & Forms![frmMarthaPerPayPeriodDeduction]![LocationNo]) '

I'm not sure if I have the right syntax to include to things in the criteria.

 

[acent]

I'm not sure if DLookups can be nested like that. Have you tried taking apart each of them to make sure they equate properly?

Could you morph this into a query and then execute the query via the dLookup?

Hope this helps.

"If it's stupid but works, it isn't stupid."
-Murphy's Military Laws

 

[LarrySteele]

I believe the double quotes in the middle of your DLookup functions should be single quotes. I believe the following syntax is what you're looking for:
[tt]' =DLookup("BranchNo","tblAllADPCoCodes","ADPCompany=" & Forms![frmMarthaPerPayPeriodDeduction]![ADPCompany]) & ',' & DLookup("BranchNo","tblAllADPCoCodes","LocationNumber=" & Forms![frmMarthaPerPayPeriodDeduction]![LocationNo]) '
[/tt]

HTH,
Larry

 

[SHAWTY721]

I tried using the code above and "#Error" appears in the textbox.

 

[MajP]

This does not look logically correct nor syntaxtically correct unless you have a strange non normalized data structure.

you want to return a branch number where
ADPCompany = 'someCompany,BranchNumber'

Does ADPCompany data look like "MyCompany,1234"? If it really does that seems non-normalized. But if that is the case then I think:

"ADPCompany='" & Forms![frmMarthaPerPayPeriodDeduction]![ADPCompany]) & ',' & DLookup("BranchNo","tblAllADPCoCodes","LocationNumber=" & Forms![frmMarthaPerPayPeriodDeduction]![LocationNo]) & "'"

How should this really look?
"ADPCompany = '????'

 

[randy700]

How about...

=DLookup("BranchNo", "tblAllADPCoCodes", "ADPCompany = '" & Forms!frmMarthPerPayPeriodDedection!ADPCompany & "' AND LocationNumber = '" & Forms!frmMarthPerPayPeriodDeduction!LocationNo & "'")

Randy