Dlookup using query field as criteria

[Drand]

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]

Hi. First, we could try adding the string delimiters.

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

 

[Drand]

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]

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]

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]

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...

How to build strings in VBA

I promised a member at UtterAcess that I will write a blog about how to build a string in VBA, particularly when constructing a SQL statemen...

thedbguy.blogspot.com

 

[Drand]

Thanks very much for that. I will have a look and hopefully get the hang of this.

Thanks again for your help.

 

[theDBguy]

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]

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:

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

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