DLookup problem

  • Thread starter Thread starter Leslie Isaacs
  • Start date Start date
L

Leslie Isaacs

Hello All
I have the following query which won't work:

SELECT [select pvt items].[ref number],
DLookUp("[private]![doctor]","[private]","[private]![ID]=[ref number]") AS
Expr1
FROM [select pvt items];

I have tried it with and without the quotes, and with and without the
[private]! bit, but to no avail. As it is above, access give the message
that it can't find 'ref number' - which is the only other field in the
query.

I would be very grateful if someone could help.
Thanks
Les
 
Try:
SELECT [select pvt items].[ref number],
DLookUp("[doctor]","[private]","[ID]=" & [ref number]) AS DrName
FROM [select pvt items];

If [ref number] is text, use:
SELECT [select pvt items].[ref number],
DLookUp("[doctor]","[private]","[ID]=""" & [ref number] & """") AS DrName
FROM [select pvt items];

I'm not sure why you don't add the table Private to your query and join
[Select Pvt Items]![Ref Number] to [Private]![ID]...
 
Duane

Many thanks for your reply - your first suggestion worked fine.
I don't know if this is a good explanation, but the reason I hadn't joined
the table into the query is that in fact I needed to embed the lookup in
another lookup: I have ended up (following the advice in both your
suggestions!) with ...

SELECT [select pvt items].[ref number],
DLookUp("[doctors]![practice]","[doctors]","[drname]=""" &
DLookUp("[private]![doctor]","[private]","[ID]=" & [ref number]) & """") AS
practice
FROM [select pvt items];

.... but I also I needed an updateable query, and when I set the Recordset
type to Dynaset(inconsistent updates) I wasn't getting the right values for
[drname] or [practice].

Was there a better way of doing this?
Thanks again for the help.
Les



Duane Hookom said:
Try:
SELECT [select pvt items].[ref number],
DLookUp("[doctor]","[private]","[ID]=" & [ref number]) AS DrName
FROM [select pvt items];

If [ref number] is text, use:
SELECT [select pvt items].[ref number],
DLookUp("[doctor]","[private]","[ID]=""" & [ref number] & """") AS DrName
FROM [select pvt items];

I'm not sure why you don't add the table Private to your query and join
[Select Pvt Items]![Ref Number] to [Private]![ID]...
--
Duane Hookom
MS Access MVP



Leslie Isaacs said:
Hello All
I have the following query which won't work:

SELECT [select pvt items].[ref number],
DLookUp("[private]![doctor]","[private]","[private]![ID]=[ref number]")
AS Expr1
FROM [select pvt items];

I have tried it with and without the quotes, and with and without the
[private]! bit, but to no avail. As it is above, access give the message
that it can't find 'ref number' - which is the only other field in the
query.

I would be very grateful if someone could help.
Thanks
Les
Click to expand...
Click to expand...
 
Back
Top