Dlookup issue

[Tark221]

Hi everyone,

I'm using a Dlookup in expression builder and it's only returning the value of the first record rather than the criteria I specify. Has anyone come across this issue before? Could someone give me an example of a dlookup.

Thank you

 

[Pat Hartman]

DLookup("fieldname", "tableOrqueryname", "criteria")

Me.SomeField = DLookup("FirstName", "tblEmployees", "LastName = '" & "Hartman" & "'")
Using criteria that could potentially return more than one record will result in an indeterminate answer. Sometimes this query will return "Pat" and sometimes, it will return "Mary".
Changing it to
Me.SomeField = DLookup("FirstName", "tblEmployees", "EmployeeID = 1234")
Will always return "Pat" because EmployeeID is unique.

If you post your expression, we will look at it.

 

[Tark221]

Hi,

Thanks for the quick reply

im using this in the control source in properties, this is tied to a textbox

=DLookUp("[STAFF_AREA]","[RAW]","[REFERENCE_NUMBER]=[tbID]")

 

[AlexHedley]

Is [REFERENCE_NUMBER] the Primary Key or a unique number in the Table?

 

[Pat Hartman]

You need to remove tbID from inside the string so Access can pass in the ID of the current record.

=DLookUp("[STAFF_AREA]","[RAW]","[REFERENCE_NUMBER]= ") & tbID

 

[pr2-eugin]

Sure that was a typo Pat..

=DLookUp("[STAFF_AREA]","[RAW]","[REFERENCE_NUMBER][COLOR=Red][B]= " & tbID)[/B][/COLOR]

 

[Pat Hartman]

Thanks. I just washed my hands and can't do a thing with them.