Correct DLookup Expression

[kobiashi]

hi

im trying to build a dlookup from a table and a query

the table is
Table_RunInLocationNames
Id
RunInLocation
LocationName

SubQuery is
SubQuery_ENG_MCU_InServiceIssues_ReturnTimeLocation
Vehicle_ID
VehicleNo
ReturnTime
ReturnLocation


what im trying to do is change the field data in returnlocation, these are set to S,N,BED1,BED2,BED3 and are in "ReturnLocation" in the subQuery

so im trying to change them to this
S = POD
N = POD
BED1 = Beckton Depot
BED2 = Beckton Depot
BED3 = Beckton Depot

and these are in Table_RunInLocationNames "LocationNames"

and the Tables_RunInLocationNames "RunInLocation"
are S,N,BED1,BED2,BED3

so here is what i thought would work but didnt

VReturnTime: DLookUp("LocationName","Table_RunInLocationNames","RunInLocation=SubQuery_ENG_MCU_InServiceIssues_ReturnTimeLocations.ReturnTime")

 

[Ranman256]

if you reference a text box, it must be outside of the quotes

DLookUp("LocationName","Table_RunInLocationNames","RunInLocation='" & me.txBox & "'")

 

[Minty]

Can you not simply join the tables and run an update based on that?

 

[kobiashi]

hi

its not a text box, its a field from a subquery

 

[kobiashi]

Can you not simply join the tables and run an update based on that?

thanks for the idea, look promising, just one question, as the data that needs to be changed riesides in the same field, can i change all the different data, in one query or will i have to make a query for each change?

 

[kobiashi]

so i had to create two update queries, but worked a treat.

 

[Minty]

Glad to help!