Retrieve an ID with Dlookup - DLookup in Microsoft Access


	Back to DLookup Comments List

Upload Images Link Email

Retrieve an ID with Dlookup

Ester Grande

20 months ago

Hi, I have the need to retrieve the ID of a table based on two parameters. I have thought to use Dlookup.

Basically I have a form where I manually write a numeric code (Codice) and a number (Numero), based on these two information I should retrieve the ID of that article.

The way I tried though didn't work.

I'll post a picture of with the table where to retrieve the ID, the form (both in design and form view) and the dlookup code.

Can you help?

Thanks

	Ester Grande OP 20 months ago

	Kevin Robertson 20 months ago
Codice is a String so you need to use Double Double Quotes DLookup will generate an error if no value is returned so I would wrap it in the NZ Function. `IDProdotto = Nz(DLookup("IDProdotto", "ElencoProdottiT", CodArticolo=""" & Codice & """ And Variante=" & Numero),0)`

	Ester Grande OP 20 months ago
Thanks Kevin, but unfortunatly still doesn't work. I don't get a debug error, but in any case it doesn't retrieve the idprodotto from the table. I have even tried not to associate the IDProdotto field but just nothing happens. I ll show the query in case the problem is there

	Ester Grande OP 20 months ago

	Adam Schwanz 20 months ago
I think Kevin accidentally missed a quotation mark before the CodArticolo, does that work? `IDProdotto = Nz(DLookup("IDProdotto", "ElencoProdottiT", "CodArticolo=""" & Codice & """ And Variante=" & Numero),0)`

	Kevin Robertson 20 months ago
Good catch Adam I do that often.

	Ester Grande OP 20 months ago
Thanks Adam, I had seen the missing quotation mark, and it doesn't work

	Kevin Robertson 20 months ago
Did you put a space before the 'And'? I noticed it was missing in the original function.

	Adam Schwanz 20 months ago
Just to verify, in the table, Codice is type Short Text and Variante (Numero?) is type number?

	Ester Grande OP 20 months ago
Yes there was the space, and yes to Adam as well. Anyway I compacted and repair the database, shut it down, reopened it and now it is working. Thanks to both for the help

This thread is now CLOSED. If you wish to comment, start a NEW discussion in DLookup.