Dlookup in query

[Marinda]

Me, again...
Please help..
I've created my query and need to bring back the value of a payrate, which
is situated in another table. I want to use dlookup that looks at the "Guard
Name" and return payrate which is situated in another table.

Query Field: Guard Name (this field is the lookup source)
Query Field: Payrate (must return value of payrate from table "guard listing"
Table "Guards listing" - Field: Payrate

Expression:
DLookUp([Guard Name],[Guards Listing]![Co No],[Guards Listing]![Pay Rate])
It give me an "error" message in the query....what am i doing wrong.

Again, i used this in excel as a vlookup, i know that access works
different, but how do i use this in access. My formula in excel is:
=VLOOKUP(F2,$C$2574:$D$2690,2,FALSE)

Hopes this makes sense!

 

[Dale Fye]

Miranda,

DLOOKUP requires 2 arguments and accepts an optional 3rd value.
These values are all strings, so they must be wrapped in quotes or be string
variables.
The first of these arguments is the name of the field you want to lookup in
the table.
The second of thes arguments is the name of the table.
The third is a criteria statement. Without this element, the function will
return the "first" record it finds in the table.

So, if you are looking in the [Guard Listings] table, for the payrate of a
specific guard, then function would look something like:

DLOOKUP("[Pay Rate]", "[Guard Listings]", "[Guard Name] = 'Stevens'")

Although I would recommend using a [Guard_ID] in the last argument, because
you might have more than one "Stevens" among the guards.

HTH
Dale