Can I use multiple criteria with a DLookup?

[ShyGuy]

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

[Salad]

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'")

[Allen Browne]

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

[Tom van Stiphout]

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.

[ShyGuy]

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.