Issue with DLookup : Errors

[Anil Bagga]

I am fairly new to Access. I have 2 issues



(1)

I was creating a form wherein I need to capture values from a Table based on some key fields SONo (Text), ErpFGCode (Text), DiscountRate (calculated number)

In the Form there is an unbound text box where I used the formula as below. SONo is a field in the Form (SOInvfrm) and also in the table CustSOTbl. Idea is to locate the MewarInvUnit in CustSOTbl where SONo captured in the SOInvfrm, matches with the SONo in the CustSOTbl

=DLookUp([CustSOTbl]![MewarInvUnit],[CustSOTbl]![SONo]="& [SONo] "). I got an error #Name? in the form view. No compilation error. On checking different sites, I changed the formula as below

=DLookUp([CustSOTbl]![MewarInvUnit],[CustSOTbl]![SONo]= '"&[SONo]&"'"). This gives a syntax error!!

(2)
In the same form I want to locate the DiscountRate from the table (this is a calculated field - Rate*Discount in the table). The SoNo is common for several items (ErpFGCode) in the CustSOTbl and each item has a different rate. Therefore I need I need to search for the rate based on concatenated string SONo+ErpFGCode. I tried several formulas but get a compilation error with all

=DLookUp([CustSOTbl]![DiscountRate],"([CustSOTbl]![SONo])" & "([CustSOTbl]![ErpFGCode] )"= " & "[SONo] & [ItemCode]")

=DLookUp([CustSOTbl]![DiscountRate],"([CustSOTbl]![SONo])" & "([CustSOTbl]![ErpFGCode] )"= " & "([SONo] & [ItemCode])")

=DLookUp([CustSOTbl]![DiscountRate],"([CustSOTbl]![SONo])" & "([CustSOTbl]![ErpFGCode] )"= " & ("[SONo]" & "[ItemCode]"))

I want to then eventually multiply quantity field of the form with the above rate fetched from the table

=[QtySent]*[ItemRate]

Please help a novice find his feet

[Anil Bagga]

I just tried this for case 1

=DLookUp("MewarInvUnit","CustSOTbl","SONo=" & [SONo]). No syntax error but I get #Error in the Form view

I checked and applied the Nz function as below to compensate if SO nos are not found in the table

=DLookUp("MewarInvUnit","CustSOTbl","SONo=" & Nz([SONo],0)) but this gives a syntax error!

[Bob Fitz]

(1)Try:
=DLookUp("[MewarInvUnit]","[CustSOTbl]"."[SONo]= '"&[SONo]&"'")

If the tables are related, you should be able to add the "Lookup" field in the form Record Source query.

[Anil Bagga]

=DLookUp([CustSOTbl]![MewarInvUnit],[CustSOTbl]![SONo]=[SONo]) gives an error &Name

=DLookUp([CustSOTbl]![MewarInvUnit],[CustSOTbl]![SONo]=Nz([SONo],0)) gives a compilation error

Surprisingly the expression below gives no error in another unbound text field

=DLookUp("[DebtName]","[CustSOTbl]","[SONo] = " & Nz([Forms]![CustInvFrm]![SONo],0))

I tried replacing this field name of DebtName with MewarInvUnit - circular reference error

[Bob Fitz]

(2)Try:
=DLookUp("[DiscountRate]","[CustSOTbl]" & "[ErpFGCode]= '" & [SONo] & [ItemCode] & "'")

[Anil Bagga]

The tables are linked on SO no

I copy pasted the expression. =DLookUp("[MewarInvUnit]","[CustSOTbl]"."[SONo]= '"&[SONo]&"'")

I get a compilation error. The . should be , possibly. On correction I get Circular ref error!

[Anil Bagga]

Bob,

On (2) no compilation errors just that I get #Error due to SO no not found in the table

[Bob Fitz]

I get a compilation error. The . should be , possibly. On correction I get Circular ref error!

Yes, sorry. The full stop should be a comma.

If the tables a related on the "SONo" field, then why not add the "CustSOTbl" table to the forms query which would enable you to have a text box bound to the "MewarInvUnit" field that you are trying to return with DLookup(). That would be the usual way