DLookup With Multiple Criteria

I would like to query a database with Dlookup based on an employee id and a date. I can get it to work just using the ID.

This works:

	Code:
	Me.txtEmpID = DLookup("EmpID", "PiecePay", "EmpID='" & txtEmpID & "'")

I would also like to have a field called PieceDate equal to txtPieceDate.

The query would be:

	Code:
	Select EmpID
From PiecePay
Where EmpID = txtEmpID AND PieceDate = txtPieceDate

How would I do that with DLookup? I've tried it different ways. My problem is probably with my use of the double and single quotes.

Thanks for any help.


Post your answer or comment

comments powered by Disqus
As I put in my intro post, I often have trouble with syntax. Its so basic I know there is an expert here who can help.

Data:
I have 2 queries that have all the data I want.
The first query's data has been turned into a form.
-The 2nd query has multiple values of the same information and is broken out by a unique ID number called Risk_ID and has only values 1-55.
I want to add an unbound field from the 2nd query using a basic Dlookup with multiple criteria, that will give me the Total_Score, dependent on the Job_Station and the Risk_ID.

Code:
The following code works but only gives me the first Total_Score as it relates to the job stations
=DLookUp("[Total_Score]","qry_LineSummaryScore","[Job_Station] =" & [Job_Station])
This code gives me only the very first Total_Score whose Risk_ID is 2, regardless of the Job Station.
=DLookUp("Total_Score","qry_LineSummaryScore","Ris k_ID=2")

The syntax of the following code is wrong because it again only gives me the very first Total_Score whose Risk_ID is 2, regardless of the Job Station.
=DLookUp("[Total_Score]","qry_LineSummaryScore","[Job_Station] =" & [Job_Station] And "[Risk_ID]=" & 2)

All fields are Numbers, no text values.
I must have looked at every example available on this site and others, and read the syntax description a dozen or so times. It just isn't clicking in my head. Some direction is appreciated.

Thanks Matt.

I'm struggling with the syntax for a dlookup with multiple criteria. (Also, you'll see that my Msgbox is wrong (as I can't recall how to handle a string variable).

Any assistance would be appreciated very much.



Dim iDirectorID As Integer
iDirectorID = Nz(DLookup("nameID", "tblFunctions", "episodeID = " & _ Me!cboSelectEpisode And "Function ='Director'"), 0)

If iDirectorID > 0 Then

Dim strDirector As String
strDirector = DLookup("FirstName" & " " & "LastName", "tblNames", "nameID" = iDirectorID)
MsgBox ("strDirector is already listed as the Director of this episode.")
Exit Sub

End If

Is there a way to do a DLookup with 3 criteria, I have tried this with no luck...


=DLookUp("[Price]","tblPrice","[LocationID] = " & [Location] & " And [Date]=#" & [TodaysDate] & "#" And "[MonthForward] = #01/01/2004#")

HELP!

Hello all.

I am trying to perform an Average IF calculation in a query with multiple criteria.

This is what I came up with:

A+AvgBal>1: Avg(IIf([qry_Cardholder Data for RePrice Through Oct 2012]![FICO 0512] Between 730 And 9000,[qry_Cardholder Data for RePrice Through Oct 2012]![Balance]>0,[Balance],0,))

So basically, average [Balance] if [FICO 0512] is between 730 and 9000 and [Balance] is greater than zero

I am getting 'the expression you entered has a function containing the wrong number of arguments.

Where am I going astray? Thank you.

Hi,

I am pretty new to Access (first project)

I know how to make a query by form where I can query using selected information on a form.

I have 2 specific questions:

1. How do I do this with multiple criteria? Also, if I add multiple criteria, is it okay if sometimes some of those criteria are left blank?!

2. How can I make a date range a criteria?

Thanks!

I am trying to use the Dlookup function but am running into some issues when I try to use multiple criteria.

I have a table that has the a price for a given range of thicknesses. I want the Dlookup to pull the price from the correct range. The ranges are defined by two fields, LowThickness and HighThickness.

For example, let's say I have a different price if the thickness is 0 to 1, 1 - 2, and anything over 2. I want the user to be able to input a number, say 1.556, and have it lookup the correct cost from the 1-2 range.

Here is what I have:

MaterialCost = DLookup ( "[Cost]","[tbl_MaterialCost", "[LowThickness]< " & Thickness And "[HighThickness] >= " & Thickness & "")

What am I doing wrong?

Edit: If there is a better or simpler way to accomplish this, including redesiging the tables involved, I'm all ears.

I'm having trouble with DLookup() using dates as part of a multiple criteria. I'm using the following;

	Code:
	If IsNull(DLookup("ExchRate", "TBL_DDPExchRates", "CurID = " & Me.Combo4 & " AND ExchDate = #" & Me.ShipOBDate & "#")) And
Me.Combo4  2 Then

It seems that if ShipOBDate is any date between the first and tenth of the month the DLookup fails to locate the appropriate record. I can't understand why as ShipOBDate and ExchDate are both formatted as Short Date and ExchDate is being populated via an OpenArgs which is derived form the field ShipOBDate

Is the fact the the date is getting converted from a date to string and back to a date some how upsetting things

For some reason I can not seem to get my lookup worded correctly so I am reaching out for help. Let me try to explain what I am trying to do.
I am building a form and would like for it to do a dlookup based on Employee Number and Date in an Attendance Table. (For each empnum there will be multiple dates, and vice versa). In the Attendance there is a field called points, and that is what I am trying to look up.
So, after I enter the EMPNUM and DATEofOccurance in my Warning form, I would like it to pull the Points from the Attendance Table (with the corresponding EMPNum and Date) and put them in the Form.
Again, I don't know why I am struggling so much with this, but I would really appreciate any help. ( I hope I explained this well.)
Jessica

Hi,

I'm having a tough time getting the following Dlookup to work:

DLookup("[ConfirmedLoad]=0", "qryDriver4a2", "DeliveryDate=#" & Format(Forms!frmLoadAllocation!cboAllocationDate.V alue, "mm/dd/yyyy") & "#" & "AND [StateID]='1'")

My original Dlookup (which worked fine!) didn't require StateID but it is necessary to include it now. Can anyone help me with the correct syntax for the StateID portion? Thank you.

Wondering if someone could help me with a subquery (i only learned they existed today) Is it possible to have multiple criteria in them? For instance i want a TU_CODE where they're in a certain faculty, tu_only is false AND where the number of students they already have (gotten by me trying to do a subquery) is not over the chamber size AND another subquery where none of that tutors entries in tblPreference are for a certain course?? Here's what i have so far, and all it gives me is a syntax error

strsql2 = "Select tblTutor.TU_CODE " _
& "From tblTutor " _
& "Where tblTutor.[TU_FAC_NO] = " & fac & " AND tblTutor.[TU_ONLY] = false AND (Select tblStudents.STU_TU_CODE from tblStudents where tblStudents.TU_CODE = tblTutor.TU_CODE AND ([tblTutor.TU_CHAMBER_SIZE] > (Count([tblStudents].STU_TU_CODE)))) " _
& "AND WHERE NOT EXISTS (Select tblPreference.TU_CODE from tblPreference where tblPreference.TU_CODE = tblTutor.TU_CODE AND tblPreference.[TU_COURSE] = '" & course & "')"

Any help would be really gratefully appreciated!

I'm trying to find a record based on multiple criteria.


	Code:
	Dim db As Database
Dim rs As Recordset
Dim Criteria As String

Set db = CurrentDb
Set rs = db.OpenRecordset("tblCalendarEvents")

Criteria = "[date_of]=#" & ctldate & "# AND [sbjID]='" & strSbjID & "' AND [choices]='" & strChoice & "'"
       
rs.FindFirst Criteria

I'm getting the error 3251
"Operation is not supported for this type of object"

I use the same criteria string for my DLookup without any problems.
Any ideas?

Hello All,

I have a table in Excel 2003 in which I have to do a countif with the multiple criteria.

Say, I have to count the number of people of a particular Race in a country in a continent

(Eg: Count of Caucasian Race in a country (Japan), which is in continent (Asia).

I have got the pick list for continent and the Country pertaining to that continent, but how to count the Race, based on these criteria....
Like this, I have got to count different races in a same country.

Please help on how to do this in Excel 2003.

Hi all,

I am trying to use Dsum criteria with multiple values but it's not working for me. Please see below I am trying to sum up 4 state's total but its not working for me.

=DSum("[S/G(M)]","QryCurrentRequirements(ClientHeads)","[State]=2 Or 4 Or 6 Or 7")


Please advice.

Dear all,

I have a table of data (tbl_marginsummary) with a record for every combination of Customer, Season, Brand, Month and StockCode.

What I am trying to achieve with my frm_summary is some kind of DSum (or a query of possible) based on any, all or some of the comboboxes I have at the top of the form.

For example, if the Brand was selected as "Brand 1" but the rest of the comboboxes were blank, I would like to see the totals below for just that Brand.

If both Brand and Customer had a selection, I would therefore want the totals below for both the Brand and Customer combination.

Does anyone know the best way to go about this?

I was thinking of a DSum with multiple criteria but I don't know how to account for Null values in each separate criteria.

Thanks!
Nick

Hai,

I want to use a Dlookup() function with multi criteria of two Numeric fields in Access 2010. The function give me result when I use them individually. When I combine both criterias, it is not filtering records to t combined criteria. Can anybody tell me what I am doing wrong here? The following statement I use as the control source of that field:

IIf([ToolNumber]=0,"N/A", DLookUp("[bslocation]","[ballscoop]","[noofballs] =" & [Forms]![ASSEMBLYTOOLS]![ToolNumber] And "[ballsize] =" & [Forms]![ASSEMBLYTOOLS]![bsballsize]))

Fields [noofballs], [bsballsize] and [ToolNumber] are numeric

Second Question:

In a tabl, I want to use a field to enter data like 1/16, 3/32, 1/8, 5/16, 1/4 etc etc... and do calculations with that field, what format I should choose?. When I choose the field as number type, it converts the value to decimals. Can I use Text format and do calculations with those fields? Or is there any special type available in Access 2010 to accomodate such formats?

Thank you and best regards

Hi,

I am using MS Access 2010 and have an issue with one of my forms.
First off, I have a table called coefficient which contains a column MIN, a column MAX, and a column COEFF.
In my form, called CALC, I want to grab a number that it is in textbox [Text12] and place the related coefficient number in textbox [Text15].


I placed a control source in [Text15] using =DLookup("[COEFF]","coefficient","[Text12]>[MIN] And [Text12]

Hello,
I am confused by why my code is not picking up the where criteria in a docmd.openform line. I have a form based on a select statement (join two tables, Transactions and DocRec, and then select the records where Transactions.PRC2 is null). This is done for dates between two values that the user specifies. This form is opened in datasheet view from another form, call it MonthReportPreparation_OTPS. On MonthReportPreparation_OTPS, the user types the reporting start date into a text box and hits buttons to view different forms and reports. All the forms open using docmd.openform- but they are all listing only one item in the Where Condition. When I try to use the same approach for this PRC2 form that requires more than one where condition, the parameters are not picked up by the code and a paramter entry box is displayed for me to type them in manually.

This is what I have for the on click event of the problematic button:

PHP Code:
DoCmd.OpenForm "Purchasing_PRC2_withoutInvoice_form", View:=acFormDS, wherecondition:="Transactions.PRC2DocID Is Null And DocRec.[Class Code]=100 and [Date of Record] between #" & Forms!MonthReportPreparation_OTPS.startdateparOTPS & "# and #" & EndOfMonth(Forms!MonthReportPreparation_OTPS.startdateparOTPS) & "#" 

I tried putting the first two items in the where condition (the 'Transactions.PRC2DocID Is Null And DocRec.[Class Code]=100' parts) into the sql query itself, and leaving only the date criteria in this vb line, but that doesn't work either. All the field names exist and are spelled correctly.
Would anyone be able to tell me why docmd.openform works with a single where criteria but asks for parameters input with multiple criteria?
Thank you!

Hi,

I'm trying to open a form at a specific record based on several search criteria, at least one of which must be used but not all.

I've used the wizard to do this with one criteria and converted into VBA to get this:


	Code:
	Docmd.OpenForm "LOG SHEET - ORDERS", acNormal, "", "[CustomerName]=" &" '" [&CustomerName] & "'", , acNormal

but can I expand this to another criteria? (no!).

I have another field "OpName" that the user could use as well as or instead of searching for CustomerName but I cannot for the life of me get the VBA correct. If anyone could please expand the above code to accomodate I should be OK from there on.

Oh and this is the first time I have used a forum like this so please accept my apologies for any omissions and transgressions!

Many thanks.

I have a table in which I have a field that contains comma's. The field contains a salesprice stored in European format. I need to do a Dlookup where this field is part of the criteria.
If I do a lookup, I get an error 3075 (comma failure).
If I single or duoble quote I get an error 3464 (type mismatch).

Any suggestions on how to use Dlookup with numeric criteria, containing a comma (other then changing the regional settings and converting comma to dot).

See code below. The problem is in Me![CustPrice] (which contains for instance 1,25).

If Not IsNull(Me![CustPrice]) Then
If Dlookup("[Cust-id]", "Pricetable", "[Cust-id] = " & Me![Cust-id] _
& " And [CustPrice] = " & Me![CustPrice]) & _
& " And [Product-id] = " & Me![Product-id]) Then
msgbox "Already exist"
Cancel = True
End If

Good Morning. I am trying to tackle a DLookUp with double critieria.

Me.MPOIAuditItemsTB = DLookup("AuditItemsID", "FindingsElements", "[Element]= '" & Me.MPOIElementTB & "'" And "[AuditID]=" & Me.MPOIAuditTypeTB)

I can get it to work before the And and if I strip it out, after the AND, but not together. What am I missing???

Hi everyone,

I need help with one very simply update query which I need to run.
I have made a simple update query with one criteria:
It works great.

UPDATE tblArticle INNER JOIN qrsLimPriceNEW ON tblArticle.ArticleID = qrsLimPriceNEW.ArticleID SET tblArticle.PriceROZCoef = 4
WHERE (([qrsLimPriceNEW].[Expr2] Between 2.21 And 2.95));

Now my next step is to create query where i have mulltiple creteria for example:
UPDATE tblArticle INNER JOIN qrsLimPriceNEW ON tblArticle.ArticleID = qrsLimPriceNEW.ArticleID SETtblArticle.PriceROZCoef = 2 WHERE (([qrsLimPriceNEW].[Expr2] Between 0.61 And 1.50));
tblArticle.PriceROZCoef = 3 WHERE (([qrsLimPriceNEW].[Expr2] Between 1.51 And 2.2));
tblArticle.PriceROZCoef = 4 WHERE (([qrsLimPriceNEW].[Expr2] Between 2.21 And 2.95));
All together I have 7 different conditions.

I simply don't know what funcition I need to use to make it work in one query together not 7.

I tried searching there some similar problems but I still don't understand.
Thanks for help in advance.

I can run my query with one criteria just fine, but when I have two "event" criteria it simply ignores them both. Strangely, it still runs the date criteria. Here is what I have:

Date Event
>Date() Not "Event"
>Date() Not "Event2"

I've also tried "Event" which returns the same results. Am I missing something stupid or basic? I've been doing this a while and never had a problem with multiple criteria queries.

I am stuck on the following code. I keep trying to make changes to it and continue to get errors. The code listed below gives me a type mismatch error. (I have seen syntex errors also)
Any suggestions on what I am doing wrong? (The Code is text, EmpNum is a number and the other is a date. I have checked the tables this relates to and the formatting matches)

Code1 = DLookup("
", "Attendance Data", "[EmpNum]= " & Forms![Warning Data]!EmpNum & """" And "[AttendanceDate]= #" &
Forms![Warning Data]![AttendanceDate1] & "#")

Thanks for all of your help
Jessica

Hi,

I am pretty new to Access (first project)

I know how to make a query by form where I can query using selected information on a form.

I have 2 specific questions:

1. How do I do this with multiple criteria? Also, if I add multiple criteria, is it okay if sometimes some of those criteria are left blank?!

2. How can I make a date range a criteria?

Thanks!


Not finding an answer? Try a Google search.