Dlookup using query field as criteria (1 Viewer)

Drand

Registered User.
Local time
Tomorrow, 09:29
Joined
Jun 8, 2019
Messages
179
Hi Folks

I am trying to use a query field as criteria for a dlookup expression within the same query but am having trouble with it!

What I have is:

StockNo: DLookUp("StockNumber","tblStock","[StockItem]= " & [StockItem])

Where StockItem is a string.

Would appreciate some assistance as this produces an error in this format.

Many thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:29
Joined
Oct 29, 2018
Messages
21,358
Hi. First, we could try adding the string delimiters.

..."[StockItem]='" & [StockItem] & "'")
 

Drand

Registered User.
Local time
Tomorrow, 09:29
Joined
Jun 8, 2019
Messages
179
Thanks for that - it works.

It did however create another issue. The same "StockItem" may occur more than once in the table but from different suppliers.

How do I add and "supplier" to the expression where "supplier" is a string and is also a field in the query?

Sorry, but I always have trouble with the syntax on these!

Many thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:29
Joined
Oct 29, 2018
Messages
21,358
Thanks for that - it works.

It did however create another issue. The same "StockItem" may occur more than once in the table but from different suppliers.

How do I add and "supplier" to the expression where "supplier" is a string and is also a field in the query?

Sorry, but I always have trouble with the syntax on these!

Many thanks
You could try...

..."[StockItem]='" & [StockItem] & "' AND [Supplier]='" & [Supplier] & "'")
 

Drand

Registered User.
Local time
Tomorrow, 09:29
Joined
Jun 8, 2019
Messages
179
Thanks again. That fixed it for me!

As a matter of interest, do you know of a reference article where I can learn how to construct these for different types, ie. strings, dates etc?

I always get stuck on this and would like to learn it myself rather than keep bugging people for help!!

Cheers
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:29
Joined
Oct 29, 2018
Messages
21,358
Thanks again. That fixed it for me!

As a matter of interest, do you know of a reference article where I can learn how to construct these for different types, ie. strings, dates etc?

I always get stuck on this and would like to learn it myself rather than keep bugging people for help!!

Cheers
See if this helps...
 

Drand

Registered User.
Local time
Tomorrow, 09:29
Joined
Jun 8, 2019
Messages
179
Thanks very much for that. I will have a look and hopefully get the hang of this.

Thanks again for your help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:29
Joined
Oct 29, 2018
Messages
21,358
Thanks very much for that. I will have a look and hopefully get the hang of this.

Thanks again for your help.
You're very welcome. We're happy to assist. Good luck with your project.
 

plog

Banishment Pending
Local time
Today, 17:29
Joined
May 11, 2011
Messages
11,612
I am trying to use a query field as criteria for a dlookup expression within the same query

Dlookups should not be in queries.

What you are trying to do is basic SQL--a JOIN:


You bring tblStcok into your query, link it to your existing table via their [StockItem] fields and simply bring StockNumber into your query.
 

Users who are viewing this thread

Top Bottom