473,554 Members | 3,205 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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]", "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

Nov 13 '05 #1
4 21075
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]", "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


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

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!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" <sh****@shytown .com> wrote in message
news:vs******** *************** *********@4ax.c om...
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

Nov 13 '05 #3
On Sat, 11 Dec 2004 22:55:26 -0500, ShyGuy <sh****@shytown .com> wrote:

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

-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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
20757
by: KLAU | last post by:
I have a field that retrieves information from an expression in a query. I have used a DLookup function to get the calculated field from the query. However, the relationship is 1-to-many so one site could have many units. How do I have the DLookUp field value change to a newly calcuated field when I navigate through the units? please see...
3
3571
by: Steven Stewart | last post by:
Hi there, I have posted about this before, but yet to arrive at a solution. I am going to try to explain this better. I have an Employees table and a Datarecords table (one-to-many relationship). I have fields called InventoryOut and BalanceEnd that are calculated on the fly for reports and when displayed on forms (they are not part of...
4
2335
by: ShyGuy | last post by:
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("", "LookUpAppts", " = " & Forms!!NumofPeople) Can someone tell me how to add multiple criteria? I tried "And" but it doesn't seem to work.
5
5658
by: Beacher | last post by:
I've noticed that you can only have a sub datasheet pointing to one table... is there anyway to change this? for example I have Customer | ---------- Customer/Product | ----------- Customer Grp/Sales Grp
6
10710
by: bjaj | last post by:
Hi How do I use a boolean criterian with the funktion DLookup ? I know the syntax for strings, numeric and date as follows For numerical values: DLookup("FieldName" , "TableName" , "Criteria = n") For strings: (note the apostrophe before and after the value)
3
4514
MSeda
by: MSeda | last post by:
I have a loop that is controlled by a Dlookup statement with two criteria. Both criteria fields are checkboxes. I have tried an assortment of quotation marks in the criteria section and cannot get the Dlookup to run. My statement looks like this (I have removed the extraneous quotes since they are not working anyway): Do Until...
4
3320
by: JHNielson | last post by:
I have a query that I'm trying to update with a dlookup with multiple criteria This is the string: EVNT_DT: DLookUp("","","( .EVNT_QTR=.) & (.=.)") When i run it it says it can't find the name PRC_15011 - Find_Vendor_Mgmt_Fees.EVNT_QTR. But I know the names are right, I've checked them three times.
2
3638
by: Denise | last post by:
Front end is Access 2002, back end is linked Oracle tables. My users need to describe things in feet and inches and want to use the standard ' and " abbrevations. On a testing form I go to a record with double or single quotes in the location description and click a button to see if Access can tell me the ID number and find its way back to...
2
5553
by: dlevene | last post by:
Hi - newbie here, be gentle. In Access 2003, I've created a report (based on a query) to produce mailing labels for all records where = "Community". is a combo-box field with the values coming from a seperate table. Problem is, some of these don't have addresses (ie. , and are null). So I want to use the address of the Administrator for...
0
7583
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7783
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8019
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7542
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7873
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5143
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3546
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3534
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2007
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.