dlookup frustration

[Sue Howard]

Have read most of the entries in the various forums re dlookup and still
cant make mine work!

my code is Dlookup("NEWSHANAME","NEWAUTHORITIES", NEWPCTNAME =
[NEWPCTNAME]")

Hve I misunderstood the concept?
dlook up finds and displays - the value of field newshaname in table
newauthorities where the value of field newpctname in that table is
equal to the newpctname entered on my form

am getting same result each time rather than relevant result and I
presume I must somehow concantenate in my criteria , but to no avail

 

[Sue Howard]

DOH! Have just realised where I'm going wrong
I have misunderstood the concept - got my criteria a about t!

my code is now DLookup("NEWSHANAME", NEWAUTHORITIES", NEWPCT =
[NEWPCTNAME]")
and it works - hope no-one scratched their heads to hard

 

[Duane Hookom]

The number of quotes in your expression don't foot out correctly. Try:
Dlookup("NEWSHANAME","NEWAUTHORITIES", "NEWPCTNAME =""" & [NEWPCTNAME] &
"""")
This assumes NEWPCTNAME is text.

 

[Nikos Yannacopoulos]

Sue,

Domain aggregate functions can be a pain until you figure out the
syntax! Remember: all three arguments are text; in the WHERE condition,
you also need a text string, but constructed so that if there is a
refernce to, say, a form field (your case), Access can tell which part
is the reference, so it reads and "plugs" the returned value in the
string. Have a look at this:

Dlookup("NEWSHANAME","NEWAUTHORITIES",
"[NEWAUTHORITIES].[NEWPCTNAME] ='" & [Forms]![YourFormName]![NEWPCTNAME]
& "'")

(all in one line despite newsreader wrap)

Note:
Preceding the field name in the WHERE clause with the domain (table or
query) name is required so Access can tell which object it refres to,
since the table field and the form control share the same name (usually
avoided practice);
The value returned by the form control reference is enclosed in (single)
quotes because the expression expects a text string (I take it
NEWPCTNAME is a text field); if it were a number, the single quotes
would be omitted.

Make any sense out of it?

Nikos