469,623 Members | 1,800 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,623 developers. It's quick & easy.

Can I use multiple criteria with a DLookup?

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

Similar topics

3 posts views Thread by Steven Stewart | last post: by
5 posts views Thread by Beacher | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.