By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,122 Members | 1,689 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,122 IT Pros & Developers. It's quick & easy.

Can I use multiple criteria with a DLookup?

P: n/a
I have a table with 4 fields. Three are used for criteria.

I can get the DLookup to work with 1 criteria with the following but
can't get it to work with 2 or three.

NumofAppts = DLookup("[luNumofAppts]", "LookUpAppts", "[luNumofPeople]
= " & Forms![AddAppointments]!NumofPeople)

Can someone tell me how to add multiple criteria? I tried "And" but
it doesn't seem to work.

Thanks for any help

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
ShyGuy wrote:
I have a table with 4 fields. Three are used for criteria.

I can get the DLookup to work with 1 criteria with the following but
can't get it to work with 2 or three.

NumofAppts = DLookup("[luNumofAppts]", "LookUpAppts", "[luNumofPeople]
= " & Forms![AddAppointments]!NumofPeople)

Can someone tell me how to add multiple criteria? I tried "And" but
it doesn't seem to work.

Thanks for any help


Remember, string are surrounded by quotes, dates by pound signs, and
numerics by nothing.

x = Dlookup("Field","Table","DateField = " & [DateField] & " And Qty = "
& [NumberField] & " And Name = 'Joe Blow'")

Nov 13 '05 #2

P: n/a
The 3rd argument of DLookup() has to look like there WHERE clause of a query
statement. You can have 2, 3, ... up to 99 parts to it. You can mock up a
query, and then switch it to SQL View to see what it should look like (View
menu, when in query design).

The example below shows how to build the string such as:
([Field1] = 1) AND ([Field2] = "xxx") AND ([Field3] = #1/1/2004#)
Note that:
- Date/Time fields need their values delimited with #,
- Text fields need the quote mark as delimiter,
- Number fields need no delimiter.
The brackets are optional in this example.

---------code example of building the Criteria---------
Dim strWhere As String

strWhere = "([luNumofPeople] = " & Forms!AddAppointments!NumofPeople & _
") AND ([City] = """ & Forms!AddAppointments!City & "") AND ([StartDate] < "
_
& Forms!AddAppointments!StartDate, "\#mm\/dd\/yyyy\#") & ")"

NumofAppts = DLookup("[luNumofAppts]", "LookUpAppts", strWhere)
-------------end of code example-----------------

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ShyGuy" <sh****@shytown.com> wrote in message
news:vs********************************@4ax.com...
I have a table with 4 fields. Three are used for criteria.

I can get the DLookup to work with 1 criteria with the following but
can't get it to work with 2 or three.

NumofAppts = DLookup("[luNumofAppts]", "LookUpAppts", "[luNumofPeople]
= " & Forms![AddAppointments]!NumofPeople)

Can someone tell me how to add multiple criteria? I tried "And" but
it doesn't seem to work.

Thanks for any help

Nov 13 '05 #3

P: n/a
On Sat, 11 Dec 2004 22:55:26 -0500, ShyGuy <sh****@shytown.com> wrote:

Try this:
NumofAppts = DLookup("[luNumofAppts]", "LookUpAppts", "[luNumofPeople]
= " & Forms![AddAppointments]!NumofPeople & " AND SomeOtherField=" &
Forms!AddAppointments!SomeOtherControl)

-Tom.
Nov 13 '05 #4

P: n/a
Thank you both for your help.

I had to beat the crap out of this to get it to work, but it does
work. ;-)

Thanks again.
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.