Dcount with multiple criteria using AND between criteria

Hats off to Jdraw's example. Solved my immediate problem, but would really like to know the code for the alternate statement.

Jdraw shows two examples to get the same result, I got the code to work one way, but I could not get the code to work using the AND between the last two criteria.

NewPadNumber is a proposed number - that is checked to determine if it has been used within th ID_Area.


	Code:
	Debug.Print  DCount("Pad_Number", "Wells_PAD_Name", "ID_Area & Pad_Number = " & Me.ID_Area & NewPadNumber) 
   '   returned   1   ' this one works great


Many Credits to Jdraw - see the last example with the AND between two criteria:
http://www.access-programmers.co.uk/...ookup+criteria


Post your answer or comment

comments powered by Disqus
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.

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.

Hi Guys
Can you look at my code and tell me where I may be blind! HospitalSiteID is a text field and PatientID is an autonumber field. When I break the criteria down it is fine but when I have multiple criteria using And then I get the datatype mismatch error. I am thinking it may have something to do with the And operator and the quotes?
Many thanks


	Code:
	 
Dim HospitalSiteID As String
Dim PatientID As Integer
Dim strCriteria As String
 
strCriteria = "[HospitalSiteID] = '" & HospitalSiteID & "'" And "[PatientID] = " & PatientID
 
rs.FindFirst strCriteria
...



I have the following control souce for a text boxt that I just can't seem to get to function correctly :

=DCount("[Facility]"," qry_Placement ","[ Facility] = 'Denied' And [AdmitFacility] ' Non-Collaborative Facility'")

Essentially I would like to have the database find the Facility field in qry_Placement where the field is equal to Denied and where the AdmitFacility field is equal to Non-Collaborative Facility. All the fields I am working with are text only. I'm not sure what I'm doing wrong.

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.

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

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

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.

Is there a way to use the DCount function based on the multiple criteria selected in a list box (taken from the .ItemsSelected property?) ie.("Athletes", tblAthletes", criteria would be all the sports selected from the Sports list box, eg. Sport = 'Football or Sport = 'Basketball', etc.). Thanks.

Steve

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.

I'm trying to use DCOUNT to count the number of members in my database who live in Baltimore and whose hobby is Golf and display this number on a report. There are 125 records in my database. Of these, 90 live in Baltimore and 75 list Golf as their hobby. When I try the first line of code below, it simply returns the total number of records in my database (125). When I try the second line of code, it correctly returns the number of those that live in Baltimore (75).

=DCount("[CITY]", "tblMembers", "[CITY] = 'Baltimore'" AND "[HOBBY] = 'Golf'")

=DCount("[CITY]", "tblMembers", "[CITY] = 'Baltimore'")

I've tried following some examples found here but they are much more complex than what I'm trying to accomplish. I know I can probably create queries, but I'd rather learn to do it this way.

Thank you all.

Kindly,
Mr Dirk

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

I've got a form called frmBooking that I want to search (proabably using DoCmd.FindRecord). It has 2 fields, 1 for employee id and 1 for course id (and a third which is the booking id).

I'm using employee id and course id to create a booking record (to book them on a course) so I want to search the bookings already made to see if the person is already booked on the course. Is there anyway to do this in VB?

I am working on an employee training db and I need to be able to search entire tables for monthly dates. I have tried using a query and all operators, yet the search returns problems. If I used the "and" operator it only returns employees that have that certain month "May" in each class field, Which most of the dates are different, so data returned is not what Im looking for. The same with the "or" operator, and filters do the same. I need it to return only those fields that have a "May" date for instance. I need a tool that will allow me to search multiple employees with multiple class fields and return only the dates that are in the certain month i am searching for. In the sample I am posting there are only three names and 4 training courses, but the db is much larger than this. I would need to build a search for each month of the year, in each department, and the year is not as important as the Month, for instance "5/**/****" would be fine. What I would like is to be able to search an entire table, and have just the employees and the courses they completed in that particular month return.
I am pretty seasoned at using access, so this is giving me fits. attached to this post is sample of what Im doing, so take a look and tell me if what im tryin to do is even possible, any help would greatly appreciated.

Hi

I've done searches on this and am guessing I probably need Index and Match but have gotten completely confused with the answers that are there and can't see if any of them apply to my query.

I have a sheet with Primary Diagnosis, First Secondary Diagnosis, Second Secondary Diagnosis, up until 6th.

I have a list of diagnoses and want my formula to return the first one of any matches that appear in the range.

Sheet 2 is the list of values that I want to use to lookup from.

Sheet 1 is the main data, with column C where I want to put my formula.

What I want this to do is return in each row, the first instance any of the values from the lookup match, ie in the first one it will be I209. Not every row has more than one instance.

I have played with Lookup, HLOOKUP, Match and Index and can't figure it out.

Any help would be really greatly appreciated.

Thank you

Ally

I'm trying to filter a subform with multiple combo boxes and eventually some text fields tied to one command button to execute the search/filter. I want the user to determine which filter or combination of filters to use.
I started with the combo boxes and can get one to work, however, when I add the second one I am forced to use both or an error is produced. The error is Runtime Error '2448' you can't assign a value to this object.
Here is a sample.

Private Sub cmdFilter_Click()
sfrm_Server_Data.Form.Filter = "Loc_ID=" & cboLoc.Value & _
"AND Cab_ID=" & cboCab.Value

sfrm_Server_Data.Form.FilterOn = True

End Sub

If I add a value in both combo boxes everthing works fine but I don't want to force the use of the second combo box.

How would I write this to allow for one or more filter selections tied to one command button?

Thanks,
Jim

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.

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