Dcount returning incorrect results

demogorgan

Registered User.
Local time
Today, 21:07
Joined
Nov 29, 2017
Messages
37
Hi,

So I am using DCount on a unbound txt box to total results from a query using criteria based on a number of days. I.E count all records with "1".

The query is displaying the correct number of results for "1", in this case 14 - however the DCount is displaying 13. I have also tried this on other counts I.E "2" etc, again the query displays different counts than DCount ?

This is what I am using
Code:
=DCount("[CreateVrequ]","qryCrtVsConEx","[CreateVrequ]= '1'")

Any ideas ?

Thanks in advance
Demo
 
Try removing the [], the single quotes around 1 and counting records as below

Code:
=DCount("*","qryCrtVsConEx","CreateVrequ= 1")
 
What is the sql for qryCrtVsConEx?
 
What is the sql for qryCrtVsConEx?

Hi
As below, the query is returning the correct results however.

Code:
SELECT tblOrder_Details.Creation_Date, tblOrder_Details.Date_Booking_Confirmed, Work_days([Creation_Date],[Date_Booking_Confirmed]) AS Difference, tblOrder_Details.Order_Number, tblOrder_Details.Export, tblOrder_Details.DateBooking_Requested, Work_days([Creation_Date],[DateBooking_Requested]) AS CreateVrequ, tblOrder_Details.IssueKey
FROM tblOrder_Details
GROUP BY tblOrder_Details.Creation_Date, tblOrder_Details.Date_Booking_Confirmed, tblOrder_Details.Order_Number, tblOrder_Details.Export, tblOrder_Details.DateBooking_Requested, tblOrder_Details.IssueKey
HAVING (((tblOrder_Details.Export)=True) AND ((tblOrder_Details.DateBooking_Requested) Is Not Null) AND ((tblOrder_Details.IssueKey)=0));
 
Just to be clear, what does the function "Work_Days" return? String? Integer? Date/Time?
 
Is there a particular reason for using HAVING rather than WHERE in your query?
WHERE is applied before grouping; HAVING after ... so is generally slower.

If you haven't a good reason, try a similar query using WHERE then do the count again

BTW you could do the count using the aggregate query itself.
 
Is there a particular reason for using HAVING rather than WHERE in your query?
WHERE is applied before grouping; HAVING after ... so is generally slower.

If you haven't a good reason, try a similar query using WHERE then do the count again

BTW you could do the count using the aggregate query itself.

Hi,

Apologies I am still new to access and SQL this is the code that the query creator used.

The query runs against all the orders in the table and then displays the date difference between the order date and booking date excluding weekends. I then get a list of of all orders and then then difference between them - I am then looking to count the amount of orders with the "1" day in one field, "2" days in another etc.
I thought this would be a good way rather than creating a query for each required count and thus why I am not counting them in the query.

Guessing I have thought wrong :banghead:
 
See if this works - bear in mind I have no data so there may be errors:

Code:
SELECT Count(Work_days([Creation_Date],[DateBooking_Requested])) AS CountCreateVrequ, tblOrder_Details.Creation_Date, tblOrder_Details.Date_Booking_Confirmed, Work_days([Creation_Date],[Date_Booking_Confirmed]) AS Difference, tblOrder_Details.Order_Number, tblOrder_Details.Export, tblOrder_Details.DateBooking_Requested, Work_days([Creation_Date],[DateBooking_Requested]) AS CreateVrequ, tblOrder_Details.IssueKey
FROM tblOrder_Details
GROUP BY tblOrder_Details.Creation_Date, tblOrder_Details.Date_Booking_Confirmed, Work_days([Creation_Date],[Date_Booking_Confirmed]), tblOrder_Details.Order_Number, tblOrder_Details.Export, tblOrder_Details.DateBooking_Requested, Work_days([Creation_Date],[DateBooking_Requested]), tblOrder_Details.IssueKey
HAVING (((tblOrder_Details.Export)="True") AND ((tblOrder_Details.DateBooking_Requested) Is Not Null) AND ((tblOrder_Details.IssueKey)=0));

As you can see, I've included the Count as the first field in the query.
I haven't filtered it for the value 1 but if it works you can easily do so if you wish.

NOTE: it still uses HAVING rather than WHERE. I would need to use 2 queries to change that which probably isn't worth it
 
See if this works - bear in mind I have no data so there may be errors:

Code:
SELECT Count(Work_days([Creation_Date],[DateBooking_Requested])) AS CountCreateVrequ, tblOrder_Details.Creation_Date, tblOrder_Details.Date_Booking_Confirmed, Work_days([Creation_Date],[Date_Booking_Confirmed]) AS Difference, tblOrder_Details.Order_Number, tblOrder_Details.Export, tblOrder_Details.DateBooking_Requested, Work_days([Creation_Date],[DateBooking_Requested]) AS CreateVrequ, tblOrder_Details.IssueKey
FROM tblOrder_Details
GROUP BY tblOrder_Details.Creation_Date, tblOrder_Details.Date_Booking_Confirmed, Work_days([Creation_Date],[Date_Booking_Confirmed]), tblOrder_Details.Order_Number, tblOrder_Details.Export, tblOrder_Details.DateBooking_Requested, Work_days([Creation_Date],[DateBooking_Requested]), tblOrder_Details.IssueKey
HAVING (((tblOrder_Details.Export)="True") AND ((tblOrder_Details.DateBooking_Requested) Is Not Null) AND ((tblOrder_Details.IssueKey)=0));
As you can see, I've included the Count as the first field in the query.
I haven't filtered it for the value 1 but if it works you can easily do so if you wish.

NOTE: it still uses HAVING rather than WHERE. I would need to use 2 queries to change that which probably isn't worth it

Hi,
This just seems to put a 1 next to each record ?

Am I correct in assuming that this should count all "1" , "2" , "3" etc so I can then refer to them in the DCount ?

Thanks for your help thus far.
 
Oops - slight error in the query.
I'm also wondering whether your Work_Days function may be incorrect.
See this very similar thread & the example database I uploaded earlier today
https://www.access-programmers.co.uk/forums/showthread.php?t=298179

Try again - this time with 2 queries:

qryOrder_Details - this filters the data:
Code:
SELECT tblOrder_Details.Creation_Date, tblOrder_Details.Date_Booking_Confirmed, tblOrder_Details.Order_Number, tblOrder_Details.Export, tblOrder_Details.DateBooking_Requested, tblOrder_Details.IssueKey, Work_days([Creation_Date],[Date_Booking_Confirmed]) AS Difference, Work_days([Creation_Date],[DateBooking_Requested]) AS CreateVRequ
FROM tblOrder_Details
WHERE (((tblOrder_Details.Export)=True) AND ((tblOrder_Details.DateBooking_Requested) Is Not Null) AND ((tblOrder_Details.IssueKey)=0));

qryCountOrders - this does a count based on the above query:
Code:
SELECT qryOrder_Details.CreateVRequ, Count(qryOrder_Details.Order_Number) AS RecordCount
FROM qryOrder_Details
GROUP BY qryOrder_Details.CreateVRequ;

See attached database with some dummy data and a Work_Days function - similar to that in the example above - substitute your tblOrder_Details with the one I did & see how it works out.

Hopefully you can adapt if it isn't quite what you want
 

Attachments

Oops - slight error in the query.
I'm also wondering whether your Work_Days function may be incorrect.
See this very similar thread & the example database I uploaded earlier today
https://www.access-programmers.co.uk/forums/showthread.php?t=298179

Try again - this time with 2 queries:

qryOrder_Details - this filters the data:
Code:
SELECT tblOrder_Details.Creation_Date, tblOrder_Details.Date_Booking_Confirmed, tblOrder_Details.Order_Number, tblOrder_Details.Export, tblOrder_Details.DateBooking_Requested, tblOrder_Details.IssueKey, Work_days([Creation_Date],[Date_Booking_Confirmed]) AS Difference, Work_days([Creation_Date],[DateBooking_Requested]) AS CreateVRequ
FROM tblOrder_Details
WHERE (((tblOrder_Details.Export)=True) AND ((tblOrder_Details.DateBooking_Requested) Is Not Null) AND ((tblOrder_Details.IssueKey)=0));
qryCountOrders - this does a count based on the above query:
Code:
SELECT qryOrder_Details.CreateVRequ, Count(qryOrder_Details.Order_Number) AS RecordCount
FROM qryOrder_Details
GROUP BY qryOrder_Details.CreateVRequ;
See attached database with some dummy data and a Work_Days function - similar to that in the example above - substitute your tblOrder_Details with the one I did & see how it works out.

Hopefully you can adapt if it isn't quite what you want


Hi,

The first query returns the same totals as my original query - which is correct.

The second query does total, again matching the first query. I am afraid I am getting out of my depth here, how am I linking this to the DCount that I need on my form ?

Code:
=DCount("RecordCount","qryCountOrders","CreateVrequ  >6")

Is there an advantage to running 2 querys rather than my 1 original and just running a DCount on that ? the query returned the correct data, it was just the DCount that was not totaling correctly.

My date calculation seems to work except for having to minus 1 day to get what I require which I have come to expect when working with dates and my company.

I really appreciate you creating that database for me, however when I populate the data the dates go crazy and do not calculate correctly. as in multiple orders showing over 190 days when in fact it has been a week.

I am probably being dense but I am still learning and would appreciate the explanations.

Thanks in advance

Demo
 
Demo,

What format are you entering dates in? Also, does this match what ACCESS is expecting (based off of regional setting / control setting) for the dates? To test, click on the little calendar icon to see if it puts in dates in a different format than you are typing.

My guess is either the form expects DDMMYYYY and your putting in MMDDYYYY or vice versa.
 
1. Is there an advantage to running 2 querys rather than my 1 original and just running a DCount on that ? the query returned the correct data, it was just the DCount that was not totaling correctly.

2. My date calculation seems to work except for having to minus 1 day to get what I require which I have come to expect when working with dates and my company.

3. I really appreciate you creating that database for me, however when I populate the data the dates go crazy and do not calculate correctly. as in multiple orders showing over 190 days when in fact it has been a week.

I am probably being dense but I am still learning and would appreciate the explanations.

1. Yes ... if it works!!! See below

2. Exactly as I suspected - the OP in the other thread had EXACTLY the same issue with his Work_Days function - it was even more confusing when bank holidays were involved. That's why I created a new function for him & it worked correctly. However, if you aren't based in England, you need to adjust the holiday dates in the table I provided to use my code.

3. This is almost certainly due to issues with date formats. For SQL these must be in or converted to mm/dd/yyyy format

4. No problem - hope that helps. Working with dates isn't easy - even harder when calculating holiday dates
 
Demo,

What format are you entering dates in? Also, does this match what ACCESS is expecting (based off of regional setting / control setting) for the dates? To test, click on the little calendar icon to see if it puts in dates in a different format than you are typing.

My guess is either the form expects DDMMYYYY and your putting in MMDDYYYY or vice versa.

Hi Mark,

The dates are entered using DDMMYYYY - they are all entered using the built in calender. They all match what I expect when I examine the details in the table.
I have, however no issues with the dates I can live with having to take 1 day off the totals as I am used to doing this with all dates at work currently due to the way the ordering works.

Many thanks
Demo
 
I really appreciate you creating that database for me, however when I populate the data the dates go crazy and do not calculate correctly. as in multiple orders showing over 190 days when in fact it has been a week.

So the dates were NOT off by 190 days and were not going crazy?
 
1. Yes ... if it works!!! See below

2. Exactly as I suspected - the OP in the other thread had EXACTLY the same issue with his Work_Days function - it was even more confusing when bank holidays were involved. That's why I created a new function for him & it worked correctly. However, if you aren't based in England, you need to adjust the holiday dates in the table I provided to use my code.

3. This is almost certainly due to issues with date formats. For SQL these must be in or converted to mm/dd/yyyy format

4. No problem - hope that helps. Working with dates isn't easy - even harder when calculating holiday dates


Hi,

Now I am even more confused, I am trying to digest the other thread currently - however I do not need to exclude public holidays currently as they are all worked, the only exceptions are weekends. I will however look at changing this over to build in future proofing.

your query and my query both return the same results so the dates must be working fine ?

How am I referencing your code in the DCount on my form?
The sample code I posted does not work.

Many thanks
Demo
 
So the dates were NOT off by 190 days and were not going crazy?

I am afraid the dates are out quite significantly using the database that was very kindly posted earlier.

A0zsb4KtC4xvAAAAAElFTkSuQmCCAA==
 

Attachments

  • Untitled.jpg
    Untitled.jpg
    56.7 KB · Views: 126
Last edited:
As I suspected, it's a date format issue

Try entering the following in the VBE Immediate window
The results I get are shown below

Code:
?DateDiff("d",#19/12/2017#,#10/01/2018#)
 286 
?DateDiff("d",#12/19/2017#,#1/10/2018#)
 22

The second result correctly shows there 22 days between 19 Dec 2017 & 10 Jan 2018
The first result is wrong as 10/01/2018 is interpreted as 1 Oct 2018.
Just to make matters worse, 19/12/2017 is correctly interpreted as 19 Dec 2017 as it can't be anything else!

Where are you based & what is your default date format?

You will need to adjust for mm/dd/yyyy format to get correct results
 

Users who are viewing this thread

Back
Top Bottom