DLookup - DLookup Multiple Criteria in Microsoft Access, Part 2


	Back to DLookup Multiple Criteria 2 Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

DLookup

Robert Blanchette

2 years ago

I have studied all the lessons I could find on DLookup.
It did help me to do almost all I want to do.
However, I can't succeed in writing a simple DLookup with 2 numeric criteria.
I mean, 2 numeric criteria that I write directly like 20 or 30 and not taken from a control.
I'll include a jpg to help you see what I mean.
This should be easy but I tried many, many different ways, and I can't get it to work.
I hope this is not due to the fact that I use a french version of access. It usually translates the english statement in a french statement. For example, Dlookup will become RechDom, and AND will become ET.

Thanks for your time.

	Robert Blanchette OP @Reply 2 years ago

	Adam Schwanz @Reply 2 years ago
What is Et? Is that the word And in French? I'm going to guess this is a french thing.

	Robert Blanchette OP @Reply 2 years ago
Dlookup will become RechDom, and AND will become ET. I always write the statements in english as tought by Richard and Access does the translation. Up to now, it has never failed. So I guess if you could tell me how this statement should be written in english, it would probably work for me. Thanks

	Adam Schwanz @Reply 2 years ago
Is Units a number field and customerID an autonumber?

	Robert Blanchette OP @Reply 2 years ago
In this test case, Both are numeric. Is an autonumber field treated differently than a numeric field? Thanks

	Kevin Robertson @Reply 2 years ago
I built a table with the same field names and same records as you and my result was the one I expected. I don't have the French language version installed so I can't test it.

	Kevin Robertson @Reply 2 years ago

Kevin Yip @Reply

2 years ago

The criteria in DLookup is an SQL component, and SQL is always in English. So you need to use AND, not ET. In SQL, you need to write "SELECT ... FROM ... WHERE ..." because those are reserved words and cannot be changed, not "CHOISIR ... DANS ... OU ..."

What's more, dates in SQL also need to be in the American format mm/dd/yyyy. If you write 5/11/24, Access will always treat it as May 11th, not Nov 5th.

Robert Blanchette OP @Reply

2 years ago

Makes sense, actually, before reading your post, I had played with the statement Kevin had suggested and I finally figured that what did work was to use a combination of French for the DLookup part (RechDom) and stick with the english AND instead of the French ET.
I thought it was a bug in the French version of Access but your post gave me the explanation why it works this way.
Thanks to all who answered.

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

Next Unseen

New Feature: Comment Live View