dcount using multiple fields and criteria Results


Sponsored Links:



Hi,
I need validation on my data entry form before update, where it validates multiple fields.

table name = data
fields : case_no, type, start_date, end_date

sample case :
data entry 1 = 1JKT0123, Patient, 23-01-11, 25-01-11
data entry 2 = 1JKT0123, Escort, 23-01-11, 25-01-11
(result oke since type is different)

data entry 3 = 1JKT0123, Patient, 26-01-11, 28-01-11
(result oke, date is different)

data entry 4 = 1JKT0123, Escort, 25-01-11, 27-01-11
(msg box should appear saying "data is invalid due to duplicate/overlapping date". its start date overlap with entry no 2)

to do this, what I understand - I need to have a query in before update form, to retrieve data row based on 4 criteria above
put in a record set, make a count (dcount?), should if count is > 1 then
its msg box appear and undo data.
should all passed, data entry saved into record.

however I dont know how to create all those syntax.
Please help,

Regards,
~Johan~




Hello,

I have an Access 2010 VBA/Query problem I’d like to see if the forum can address. Basically, I have two problems:

1. My query runs very slowly (over 2.5 hours to pull 2.5M records) since I have 2 custom VBA functions to aid in calculating date ranges. Yes, it’s a lot of records and its growing!
2. The other being that I cannot filter a reports query when the criterion for the filter is applied to a custom VBA function. I get a “Data type mismatch in criteria expression” error.

I'll do my best to give all the relevant information now, apologies if it’s long, I want to be thorough – it’s probably overkill. The gist of the data: ~2.5M records which track line item detail of order shipments. So, multiple lines make an order while a single line is an item in an order. I need to analyze this data and part of that is determining if orders shipped on time, etc. The table has a number of fields but the relevant ones are:

· ORDER NUMBER
· REQUESTED SHIP DATE – (aka the date the order is received)
· DATE ORDER SHIPPED/RETURNED – RETURNED label is irrelevant
· NEW ITEM FLAG – determines if an item is a 90 day exempt item

From these I calculate (Field name – field values: description & formula):

· STATUS – 5, 25 or 90: 5 days if it shipped in 5 days or if 5 days have yet to elapse, 25 days (“backorder”) if it didn’t ship in 5 days or 5 days have already elapsed and 90 days (“exempt”) if the item has an “N” in NEW ITEM FLAG.
o Status: IIf([NEW ITEM FLAG]="N",90,IIf([DATE ORDER SHIPPED/RETURNED] Is Null,IIf((NETWORKDAYS([REQUESTED SHIP DATE],Now()))>5,25,5),IIf((NETWORKDAYS([REQUESTED SHIP DATE],[DATE ORDER SHIPPED/RETURNED]))>5,25,5)))
· ACTUAL SHIPPING TIME – Positive Integer: uses UDF NETWORKDAYS to calculate shipping time dependent on whether or not the item has shipped.
o Actual Shipping Time: IIf([DATE ORDER SHIPPED/RETURNED] Is Null,NETWORKDAYS([REQUESTED SHIP DATE],Now()),NETWORKDAYS([REQUESTED SHIP DATE],[DATE ORDER SHIPPED/RETURNED]))
· TARGET SHIP DATE – Date: uses UDF NETOWRKDAYS to calculate the day it should’ve shipped accounting for weekends and specific holidays.
o TargetShipDate: TargetShipDate([REQUESTED SHIP DATE],[Status])
· ON TIME – On Time, Late, Pending: analyzes the other calculated fields with DATE ORDER SHIPPED/RETURNED to determine if it was on time, late or pending.
o On Time: IIf(([DATE ORDER SHIPPED/RETURNED] Is Null And Now() dtEndDay. If so, then switch the dates If dtStartDay > dtEndDay Then dtNominalEndDay = dtStartDay dtStartDay = dtEndDay dtEndDay = dtNominalEndDay End If 'Here are how many weeks are between the two dates lngTotalWeeks = DateDiff("w", dtStartDay, dtEndDay) 'Here are the number of weekdays in that total week lngTotalDays = lngTotalWeeks * 5 'Here is the date that is at the end of that many weeks dtNominalEndDay = DateAdd("d", (lngTotalWeeks * 7), dtStartDay) 'Now add the number of weekdays between the nominal end day and the actual end day While dtNominalEndDay 0 Then If Left(strField, 1) "[" Then strField = "[" & strField & "]" End If Do strCriteria = strField & _ " = #" & Format(TSD, "mm/dd/yyyy") & "#" rs.FindFirst strCriteria If Not rs.NoMatch Then TSD = TSD + 1 End If Loop Until rs.NoMatch End If End If Loop Until Not IsWeekend(TSD) If Status > 1 Then Status = Status - 1 TSD = TSD + 1 GoTo TestNextDate End If ExitHere: TargetShipDate = TSD 'Debug.Print TSD Exit Function ErrHandler: ' No matter what the error, just ' return without complaining. ' The worst that could happen is that the code ' includes a holiday as a real day, even if ' it's in the table. Debug.Print "Error: " & Err & " - " & Error Resume ExitHere End Function

	Code:
	Private Function IsWeekend(dtStartDate As Date) As Boolean
'test for weekends
Select Case Weekday(dtStartDate)
   Case vbSaturday, vbSunday
       IsWeekend = True
End Select
End Function


Thank you,

~Chris




Hi
I am putting a sort of statistical reporting form into my Db and need a little help.
I have built an append query which pulls all the relevant data for the set period into a table. I then plan to use DCount to analyse on various criteria.
One area I am having difficulty is in identifying the number of unique items in various fields.
For instance one patient may have many scripts. Equally scripts may have one or more content lines.
I am reasonably OK with DCount where certain criteria are met (e.g. a field = Smith) but can I set it to only count the first instance of each repeating number e.g. 1, 1, 1, 2, 3, 4, 4, 5 gives a result of 5 rather than 8?
My current solution seems clumsy in that I append the data to a temporary table which has the field set as a key index so it will exclude all multiple instances and then I DCount on that table.
It may be that this is the way to do it but I will end up building a lot of temporary tables so thought I would ask sooner rather than later since there is probably a simpler way to do it.
Any help most appreciated




I've been doing a bit of looking around for some DCount/DLookup stuff to help w/ some auto-magic stuff on a new database. After the using enters a new record, I need to check for duplication of criteria. If so, I need to have it capture some info from the form and open a report. The code I have now simply opens a msgbox when there's duplication.

But, here's where the problem comes in. I have the VBA for a DCount function for one of the fields, and it works. When a duplicate entry is made in this field, during the BeforeUpdate the DCount works and opens a nice little message box...thanks to other threads on this forum. The code is below for the fields' BeforeUpdate function.


	Code:
	If DCount("[TailNo_Main]", "tbl_Main", "[TailNo_Main] = '" & Me.TailNo_Main & "'") > 0 Then
   MsgBox "Tail # is already listed.", vbOKOnly + vbCritical, _
              "DUPLICATE FOUND"
   'Cancel = True
End If

The problem w/ this is that it only checks the TailNo_Main field. What I actually need to do is check for matches between two different fields (TailNo_Main and WUC_Main). If there's a match between those two fields in a previous record, then I need to flag the user. A simple msgbox will be fine for now....I can fill in the details of the "flag" later.

Unfortunately, I can't seem to be able to use DCount or DLookup for multiple fields. I also haven't been able to locate any examples of this anywhere. Is it possible? Am I looking in the wrong direction for what I am trying to accomplish?




I'm new to programming and this forum, but it appears that people are willing to help newbies like me so I thought I'd give it a try.

I have 2 forms, the first is to enter a patient number, if it exists in the table, I want to open to the record, if not, I want to open the form to a new record. New records are not a problem. However, the second form does not load the record specified in the OpenForm criteria. The DCount works correctly for single and multiple field criteria.

I actually have a few pairs of these. On one form, it loads blank, on another it opens to the first record.

The strangest part is that I had this working yesterday afternoon, somehow broke it, fixed it today and just broke it again. Is there something outside my code that the form is looking to when it opens?

Thanks for any help!


Private Sub cmdEnter_Click()
If DCount("[PtID]", "tblPatients", "[PtID]='" & Me!enterPtID & "'") > 0 Then
MsgBox "Patient already in database." & vbCrLf & vbCrLf & "Please verify Data."

DoCmd.OpenForm "frmPatients", , , "[PtID]='" & Me!enterPtID & "'"

Else: MsgBox "Enter new patient Data"
DoCmd.OpenForm "frmPatients", , , , acFormAdd
Forms!frmPatients!txtPtID.Value = Forms!EnterPatient!enterPtID
End If

DoCmd.Close acForm, "EnterPatient"

End Sub




Which SQL function is used to calculate the frequency of a paticular number in a number field.
I have tried both count and Dcount but dont get the exact results im looking for.

Using count i get the number of records, but not the number of records that contain, say, the number 9.

Using Dcount i can add a criteria eg. "[field] = 9" which give me the correct results, but i also have a keyfield called [ID] and i wish to calculate the frequency of say 9 for each unique ID.
Maybe you could add multiple criteria to Dcount, or add a [Please enter ID] criteria to the [ID] field?

Many thanks Kris.




I need to add a multiple criteria to a DCount function I am using. I am using the value of the RecordCountACH all over the db so I am hoping for a quick fix to my DCount function. The [VendorNumber] field is formatted as text.

'this works but now I need to add two account numbers...
Dim RecordCountACH As Integer
RecordCountACH = DCount("*", "tblInvoices", "[VendorNumber] = '123456'")

Now I need to count the number of records for two vendor numbers; '123456' and '987654'

'this is not working...any suggestions?
RecordCountACH = DCount("*", "tblInvoices", "[VendorNumber] = In('123456','987654')")

How can I do that will a simple DCount function?

Thanks in advance for your help!




I have a mailing list with 60,000+ records in an access 2000 database. Each record is assigned a category in a field called “Code” (for example, “MX001” or “MX002”) that corresponds to the mail piece required.



I need to add an incremental count to each record in each category. For example, if there were 15 records with Code = MX001, the first record with that code would be numbered 1, and the last would have the number 15. Or, if there were 120 records with Code = MX002, each record in that group would need a sequence number starting with 1 and ending with 120.


I’ve had success adding sequence numbers to mailing lists in the past using an update query with a DCOUNT function. In the function below, “id” is an autonumber assigned when the file is imported.


DCount("id","tbl_Data_List","id




Hi all. How's it going? I'm new here, but I like to think of myself as being reasonably good with Access. However, I'm totally stuck with a multiple DCOUNT and would really appriciate any advice... I'll give the formula, and the background is below if you're interested....

This works as a single criteria:
Required: DCount("*","works order query availability","midpartsusedin =" & [midpack])

These don’t:
Required: DCount("*","works order query availability","midpartsusedin =" & [midpack] AND "Next WC =" & [wc des])

Error "The specified field [WC Des] could refer to more than one table in the FROM clause of your SQL statement."

If I specify the table ‘export’ I get error Extra ) in query expression ‘export.[DCount("*","works order query availability","midpartsusedin =" & [midpack] AND "Next WC =" & [wc des])]

If I add export to the query text so that it reads Required: DCount("*","works order query availability","midpartsusedin =" & [midpack] AND "Next WC =" & [export]![wc des])

It works, but counts ALL the records, not just those that meet both criteria.

Does it matter that [Next WC] and [WC Des] are fields whereas “midpartsusedin” and “mid pack” are both expressions within queries???

I’ve tried all different combinations of “ & AND []() for example saying; [midpartsusedin]=[midpack], but no other combination except "midpartsusedin =" & [midpack]) seems to work

Getting nowhere with this…. Anybody know the correct formula or possible solutions.

********

Background.... I get a huge list of production orders every day in excel - which curently we print and hand out to the various machine operators (Work Centers) in the factory. We then have a supervisor manually add up the time to make each order to calculate demand vs. capacity to determine how many people we need to run each machine and whether we need overtime or not. Then finally when complete we type these orders back into a computer and fill in another sheet as a QA record.

So I want to display the orders in Access, avoid all the printing and distribution time, automate the demand/capacity calculations, avoid lost and missing orders and eliminate a lot of the duplicated admin. Savings should be about 8-16 hours a day.

So far so good, but here's where I'm stuck. In the paper system, when a order for a 'sub-part' is complete, the printed order is walked to the next machine. When all the sub-parts are made, this is the trigger to that work center that says the raw material parts are available for them to assembly into a finished product.

In the database, I just want the machine operator to tick a box when the sub-part order is complete. When all the sub-parts are ticked complete, this should automatically mark the order in final assembly appear as "available".

Each line in the spreadsheet is an order (Midpack), giving the product name and qty to make, time to make, what department & machine (WC) to make it on, what machine to move it to next for further processing and what product it is used in (Midpartsusedin) - which is another line in the spreadsheet, except for the finished product where Midpartsusedin = Customer,

Midpack / Part / Qty / Time / Department / WC Des / Next WC / Midpartsusedin

So I have a query that counts all the records where midpartsusedin = midpack. This tells me how many sub-parts are required for the order.

Required: DCount("*","works order query availability","midpartsusedin =" & [midpack])

Then I have query that counts all the records - in a 'complete' query where midpartsusedin = midpack. This tells me how many sub-parts have been complete.

Available: DCount("*","works order query availability complete","midpartsusedin =" & [midpack])

When required > available the form puts a red cross next to the order. When required = available then the order gets a green Y on the form.

I then do a sum of the total time of all the "available orders". So that if a work center is running out of work (say less than 30 mins available work), it flags up warning highlighting the need to expedite sub-parts to that work center. This keeps work flowing between the work centers.

I need to add a second criteria to the DCOUNT so that it finds the required and available sub-parts where the [midpartusedin] = [midpack] AND where the [WC Des] = [Next WC]. This also makes sure it only counts 1st level sub-parts, not all the sub-part right through the supply chain. Strangly the formula doesn't seem to work if I used the square brackets as per the other DCOUNT advice I've seen.

Been banging my head against a wall for about 2 days on this now.

Wouldn't be surprised if the answer is obvious.

Thanks for any help!

Mick




I am working on a database in which I must check the records in one table against the records of another table, and return a list of the one's that don't match. The tables contain salary accounting information. For each employee, they can have up to 5 fund/orgn/acct combinations, with a different percentage of their salary coming from each of the fund/orgn/acct combinations. In one table (call it Table A), there is just 1 record for each employee, with 5 sets of columns for the fund/orgn/acct. In the other table (call it Table B), there is 1 record for each fund/orgn/acct combo for each employee.

After thinking through how I could do this, the easiest way I could think of was to write a VBA function that is passed the employee ID and the fund/orgn/acct combos from Table B, and uses DCount() to check this against each of the 5 sets of columns for the fund/orgn/acct combos. If it finds a match, the function returns true; if, after checking all 5 sets of columns, it fails to find a match, the function returns false. In my query, I would like to include this as the criteria - I want to view all records where this query returns false. However, anytime I set I enter criteria on this function, Access gives me a "Data type mismatch" error. I've spent the last several hours trying a variety of things to trouble shoot this and I can't figure it out. I've switched the function's return type to integer (rather than boolean, since that returns either a 0 or -1 anyway), and sure enough, it returns an integer. If I do a make table out of my query, the resulting field is of type integer. Within my query, I can add numbers to the function's return - so clearly, it is returning an integer. However, no matter how I type in my criteria (i.e. 0, = 0, '0', = '0', "0", = "0"), I still get a datatype mismatch error. I've tried turning this into a multiple-step query as well, but I'm still having no luck.

Does anyone have any ideas for what is causing this?




Hi All,
I'm trying to create a query that contains an expression that allows me to count the number of open tasks per project. I was able to successfully count the total number of open tasks with the following expression in my query:

Expr1: DCount("[completed]","[tbTasks]","[completed]=false")

I currently have a criteria for the same query to select open projects but would like the associated number of tasks per project to be displayed in a field called Open_Tasks.

Can someone please help me? I know I need to set an additional criteria in the expression but I'm not sure what to use.

I already have a parent/child relationship set up between the tasks and projects table, if that helps.

Thanks in advance




I am using the following textbox to count records within a report group header:
=DCount("[ID]","Query1","[Text] = " & "Text30"), where "Text30" is the grouping criteria and is displayed in the group header. This format works well, but I would like to use something similar to group by month with a [Date] field: =DCount("[ID]","Query1","[Date] = " & "??????").
I have tried multiple variations of syntax without success. Any suggestions would be appreciated.
Thanks.