DLookUp

[Keke Lee]

I failed to process a DLookup Query. Herewith my sample:

columns in table [#code]: #Sec Code,#QtyOfSum
columns in table

: Sec Code,QtyOfSum

My work is to return the value of [QtyOfSum] by looking up if [Sec Code]
=[#code].[#Sec code].

My query:
SELECT [#code].[#Sec Code], [#code].[#QtyOfSum],
DLookUp("[QtyOfSum]","[code]","[Sec Code]"="[#code].[#Sec Code]") AS dluTable
FROM [#code];

The above returns no value from "dluTable" column .

Indeed my true work is more complicated than above sample. My DLooKup has to
be a query upon another query indeed, though I failed from above simple
sample (query to table) anyway

Many thanks Keke

 

[Allen Browne]

Concatenate the value of the current field into the criteria for your
DLookup() expression, like this:
SELECT [#code].[#Sec Code],
[#code].[#QtyOfSum],
DLookUp("[QtyOfSum]", "

",
"[Sec Code] = " & Nz([#code].[#Sec Code],0)) AS dluTable
FROM [#code];

If [#Sec Code] is a Text field (not a Number field), you need extra quotes:
DLookUp("[QtyOfSum]", "[code]",
"[Sec Code] = """ & [#code].[#Sec Code] & """") AS dluTable

For an explanation of the quotes, see:
http://allenbrowne.com/casu-17.html

If your table is large, it may be quicker to use a subquery:
http://allenbrowne.com/subquery-01.html

 

[Keke Lee]

Gorgeous!

Concatenate the value of the current field into the criteria for your
DLookup() expression, like this:
SELECT [#code].[#Sec Code],
[#code].[#QtyOfSum],
DLookUp("[QtyOfSum]", "

",
"[Sec Code] = " & Nz([#code].[#Sec Code],0)) AS dluTable
FROM [#code];

If [#Sec Code] is a Text field (not a Number field), you need extra quotes:
DLookUp("[QtyOfSum]", "[code]",
"[Sec Code] = """ & [#code].[#Sec Code] & """") AS dluTable

For an explanation of the quotes, see:
http://allenbrowne.com/casu-17.html

If your table is large, it may be quicker to use a subquery:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

[QUOTE="Keke Lee"]
I failed to process a DLookup Query.  Herewith my sample:

columns in table [#code]: #Sec Code,#QtyOfSum
columns in table [code]: Sec Code,QtyOfSum

My work is to return the value of [QtyOfSum] by looking up if [Sec Code]
=[#code].[#Sec code].

My query:
SELECT [#code].[#Sec Code], [#code].[#QtyOfSum],
DLookUp("[QtyOfSum]","[code]","[Sec Code]"="[#code].[#Sec Code]") AS
dluTable
FROM [#code];

The above returns no value from "dluTable" column .

Indeed my true work is more complicated than above sample. My DLooKup has
to
be a query upon another query indeed, though I failed from above simple
sample (query to table) anyway

Many thanks Keke
[/QUOTE]
[/QUOTE]