Dlookup in Query

[fergiepl]

I have a query based on a single table. I need to do a dlookup based on a value in the query into another table.
For example:

Water: DLookUp("[rateleveli]","[tblLUrate]"," [tempcode]=

")

Tempcode =is in my query
code = is in tblLUrate

both tempcode and code are text fields

I have tried many variations on the syntax to no avail.
Thank you in advance.

 

[Steve101]

Try the following syntax:
[tt]
Water: DLookUp("[rateleveli]","[tblLUrate]","[tempcode]='ÿ'")
[/tt]

Note the changes:

(a) The square brackets are only required if a table name or field name has special characters embedded in it (eg. a space). In your case, they're not required.

(b) Having said the above, they do no harm, except in the last expression, where the closing square bracket surrounds both the field name AND the value you're attempting to compare it with. This is a problem.

(c) Finally, it appears that the thing you're atte3mpting to compare [tempcode] against is a text string. It thus needs to be surrounded by quotes. Single quotes are used, as double quote characters are already being used in the broader expression.

Hope this helps,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[danvlas]

Water: DLookUp("rateleveli","tblLUrate","[tempcode]='" & [TempCodeFieldInQuery] & "'&quot

this will look up the value of tempcode returned in each query row.

However, this will run slower than a simple left join:

Include the tblLUrate in your query, create the join on the tempcode field, right click the line, choose Join Types and select option 2 or 3, whichever results in an arrow pointing to tblLUrate. And include the tempcode field from tblLUrate table in the query.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html