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]", "LookUpAppt s", "[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]

Re: Can I use multiple criteria with a DLookup?

ShyGuy wrote:
[color=blue]
> 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]", "LookUpAppt s", "[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
>[/color]

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

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

[Allen Browne]

Re: Can I use multiple criteria with a DLookup?

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!AddAppoin tments!NumofPeo ple & _
") AND ([City] = """ & Forms!AddAppoin tments!City & "") AND ([StartDate] < "
_
& Forms!AddAppoin tments!StartDat e, "\#mm\/dd\/yyyy\#") & ")"

NumofAppts = DLookup("[luNumofAppts]", "LookUpAppt s", 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" <shyguy@shytown .com> wrote in message
news:vsfnr05ej0 v6sk8jl15rf7ti4 gamji2ec2@4ax.c om...[color=blue]
>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]", "LookUpAppt s", "[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
>[/color]

[Tom van Stiphout]

Re: Can I use multiple criteria with a DLookup?

On Sat, 11 Dec 2004 22:55:26 -0500, ShyGuy <shyguy@shytown .com> wrote:

Try this:
NumofAppts = DLookup("[luNumofAppts]", "LookUpAppt s", "[luNumofPeople]
= " & Forms![AddAppointments]!NumofPeople & " AND SomeOtherField= " &
Forms!AddAppoin tments!SomeOthe rControl)

-Tom.

[ShyGuy]

Re: Can I use multiple criteria with a DLookup?

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.