• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Dlookup using query field as criteria (1 Viewer)

Drand

Registered User.
Local time
Tomorrow, 04:47
Joined
Jun 8, 2019
Messages
65
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, 10:47
Joined
Oct 29, 2018
Messages
12,532
Hi. First, we could try adding the string delimiters.

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

Drand

Registered User.
Local time
Tomorrow, 04:47
Joined
Jun 8, 2019
Messages
65
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, 10:47
Joined
Oct 29, 2018
Messages
12,532
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, 04:47
Joined
Jun 8, 2019
Messages
65
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, 10:47
Joined
Oct 29, 2018
Messages
12,532
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, 04:47
Joined
Jun 8, 2019
Messages
65
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, 10:47
Joined
Oct 29, 2018
Messages
12,532
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, 12:47
Joined
May 11, 2011
Messages
10,076
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