Dcount returning incorrect results

[Mark_]

OK, try the attached out. It doesn't need a record for EVERY day used, just those that are holidays. I've also heavily annotated the function including putting in messages to show what it is doing as the function does them.

I've avoided using DateDiff and just treated the dates as numbers. This tends to avoid some.... issues... from what I'm seeing. It can also be rather easily modified if your "week" doesn't begin on Sunday of if you want to skip the whole "Weekends off" bit.

 

[demogorgan]

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

?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

Hi,

My querys show there are 22 days between them as well ? so I do not understand how it can be a date issue ?

I am based in the UK and my default date format is DD/MM/YYYY

I am confused how this affects the DCount that I was having issues with ? the difference between the 2 dates gives me a number, then the DCount searchs for criteria and then returns a value. My original query is correctly returning the days between the dates and when I manually count them I get a different number than the DCount which is where I thought my issue was.

I really do appreciate all the help you are giving me but I am struggling to get my head around the reasons why we are doing what we are doing.

Thanks again
Demo

 

[demogorgan]

OK, try the attached out. It doesn't need a record for EVERY day used, just those that are holidays. I've also heavily annotated the function including putting in messages to show what it is doing as the function does them.

I've avoided using DateDiff and just treated the dates as numbers. This tends to avoid some.... issues... from what I'm seeing. It can also be rather easily modified if your "week" doesn't begin on Sunday of if you want to skip the whole "Weekends off" bit.

Hi,

I appreciate the time and effort you have taken to create the attached and I will try and digest the details - however my query and current date function seems to work as far as I can tell. Unless you can see different?

I am afraid I cannot remember where I aquired this code & I am still trying to learn myself what it all does - but it does appear to work.

Function Work_Days(Creation_Date As Variant, Date_Booking_Confirmed As Variant) As Integer

 Dim WholeWeeks As Variant
 Dim DateCnt As Variant
 Dim EndDays As Integer

 On Error GoTo Err_Work_Days

 Creation_Date = DateValue(Creation_Date)
 Date_Booking_Confirmed = DateValue(Date_Booking_Confirmed)
 WholeWeeks = DateDiff("w", Creation_Date, Date_Booking_Confirmed)
 DateCnt = DateAdd("ww", WholeWeeks, Creation_Date)
 EndDays = 0

 Do While DateCnt <= Date_Booking_Confirmed
 If Format(DateCnt, "ddd") <> "Sun" And _
 Format(DateCnt, "ddd") <> "Sat" Then
 EndDays = EndDays + 1
 End If
 DateCnt = DateAdd("d", 1, DateCnt)
 Loop

 Work_Days = WholeWeeks * 5 + EndDays

Exit Function

Err_Work_Days:

 ' If either Creation_Date or Date_Booking_Confirmed is Null, return a zero
 ' to indicate that no workdays passed between the two dates.

 If Err.Number = 94 Then
 Work_Days = 0
 Exit Function
' Else
' If some other error occurs, provide a message.
' MsgBox "Error " &; Err.Number &; ": " &; Err.Description
End If

End Function

Many thanks
Demo

 

[isladogs]

I'm also unclear where this is at currently...

So that Mark or I can give you a working version, please upload your database with work days function & with sample data in the relevant tables(s)

If possible also give the results you are expecting

 

[Mark_]

One issue that could COULD give...
Do local setting changes what format returns when you format as "ddd"? You are using constants to compare against. WeekDay returns a number so it would be consistent. I'll have to work through some dates to see.

 

[demogorgan]

I'm also unclear where this is at currently...

So that Mark or I can give you a working version, please upload your database with work days function & with sample data in the relevant tables(s)

If possible also give the results you are expecting

Hi,

I will work on this to send through later as I will need to replace some data.

I will clarify - my querys return the required data as far as I can tell anyway.

My original issue still remains. I have a form that displays the totals of the querys using DCount using criteria (See attachment) I.E total of orders request within 1 day.
My issue is that the DCount does not return the correct data from the query I.E query shows 14 records matching the criteria and the DCount returns 13 and so on.

This is the code I use in the unbound box's, obviously criteria is changed as to what is requred

=DCount("CreateVrequ","qryCrtVsConEx","CreateVrequ=" & 1)

Many thanks
Demo

 

[demogorgan]

Hi,

Apologies for the delay - life has a way of getting in the way..

I wanted to thank you all for the help, the issue seemed to be the form not refreshing correctly - I now get all the required numbers correctly

I will certainly look into incorporating the date functions you have recommended for V2 of this database once I have got this usable for it's purpose in my alloted time scale.

Many thanks
Demo