Add Working Days, excluding We & hols. Function is mixing dd/mm (1 Viewer)

Jordonjd

Member
Local time
Today, 09:05
Joined
Jun 18, 2020
Messages
96
Hi everyone, im hoping this is an easy question to answer.
I found some code online for add or subtracting working days and excluding holidays utilizing a table to list the bank holidays etc.

the function seems to be switching the dd/mm if its possible,

i have a holiday date 10/04/2020 (10-april-2020) and the function is not excluding this, instead it seems to read it as 04/10/2020 (04/Oct-2020)

if the date cannot be read in this way the function works fine,

Holiday date 13/04/2020 (13-april-2020) it excludes it.

here is a screenshot example:

Table:
1602607548361.png

Query Results:
1602607774642.png


As you can see, if its possible to swap the mix the dd & mm on the date the function seems to
The 5th lines is an example to demonstrate that the function has read the 08/05/2020 from the table as (05-Aug-2020) when it should not be.

Here is the code i have copied:


Code:
Function aWorkdays(startDate As Date, _
                   NumBusinessDays As Long, _
                   Optional Adding As Boolean = True, _
                   Optional ShowDebug As Boolean = False) As Date


    Dim Enddate As Date
    Dim TempDate As Date
    Dim numWorkDays As Long
    Dim BusinessDays As Integer
    Dim i As Integer
    Dim isWeekend As Boolean  'True if the date is a weekend day
    Dim isHoliday As Boolean  'True if the date is a Holiday


10  On Error GoTo aWorkdays_Error

20  numWorkDays = NumBusinessDays
30  TempDate = startDate
    Dim TWeekDay As Integer

40  For i = 1 To 9999    'just a large number
        'reset the Holiday and weekendday flags
50      isHoliday = False
60      isWeekend = False
70      If ShowDebug Then Debug.Print "Tempdate is   " & TempDate
        'Get some facts
        'is this date a holiday
80      If DCount("*", "tblHolidays", "HolidayDate=#" & TempDate & "#") = 1 Then
90          isHoliday = True
100         If ShowDebug Then Debug.Print TempDate & "  is a Holiday--------H"
110     End If

        'is this date a weekend
120     TWeekDay = Weekday(TempDate)
130     Select Case TWeekDay
        Case 1, 7  'is weekend not a business/work day
140         isWeekend = True
150         If ShowDebug Then Debug.Print TempDate & "  is a weekend day----W"
160     Case Else
170     End Select

        'Count the Business day
180     If Not isHoliday And Not isWeekend Then
190         BusinessDays = BusinessDays + 1
200         If ShowDebug Then Debug.Print TempDate & " is a Business Day"

210     End If

        'check if we have found the end date
220     If BusinessDays = numWorkDays Then
230         GoTo Finished
240     ElseIf Adding Then           'this handles Adding
250         TempDate = TempDate + 1
260     Else
270         TempDate = TempDate - 1  'this does the subtracting
280     End If
290 Next i
Finished:
300 If ShowDebug Then
310     If Adding Then
320         Debug.Print "Adding " & numWorkDays & " business days to " & startDate & "  is " & TempDate
330     Else
340         Debug.Print "Subtracting " & numWorkDays & " business days from " & startDate & "  is " & TempDate
350     End If
360 End If
370 aWorkdays = TempDate

380 On Error GoTo 0
390 Exit Function

aWorkdays_Error:

400 MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure aWorkdays of Module mellonAccessRelated"
End Function


The other function for Counting working days between 2 dates has the same problem.

To cut a very long story short, my question is

Is there something i can add to the module to specify it should read the HolidayDate from TblHolidays in the dd/mm/yyy format?

Thanks in advance for any help
 

Minty

AWF VIP
Local time
Today, 08:05
Joined
Jul 26, 2013
Messages
10,346
You need to make sure the dates in the DLookups and any SQL statement are formatted in the default Access way which unfortunately if you are not in the USA is mm/dd/yyyy.

It's even better to use yyyy-mm-dd as you can easily see exactly what is being passed in and out of functions.

Have a read here for a full explanation http://allenbrowne.com/ser-36.html
 
Last edited:

Jordonjd

Member
Local time
Today, 09:05
Joined
Jun 18, 2020
Messages
96
You need to make sure the dates in the DLookups and any SQL statement are formatted in the default Access way which unfortunately if you are not in the USA is mm/dd/yyyy.

It's even better to use yyyy-mm-dd as you would can easily see exactly what is being passed in and out of functions.

Have a read here for a full explanation http://allenbrowne.com/ser-36.html
Okie dokes, thanks, I had a read and understand i believe. i'm just having a google as to how i format the dates in the Dlookups
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:05
Joined
Oct 29, 2018
Messages
21,322
Okie dokes, thanks, I had a read and understand i believe. i'm just having a google as to how i format the dates in the Dlookups
There's a function (SQLDate) included in that article that you can use.
 

Jordonjd

Member
Local time
Today, 09:05
Joined
Jun 18, 2020
Messages
96
There's a function (SQLDate) included in that article that you can use.
ah ok, i didn't understand then, so i paste that function into a new module and then i call it from the aworkdays function somehow?

Sorry this my knowledge is impressively limited
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:05
Joined
Feb 19, 2002
Messages
42,872
In addition to what Allen says, do NOT format dates in queries that you will use for comparison or sorting because using Format() converts a date to a string. So inside a query:
Where date1 >= date2
Will never have a problem because the actual numeric value is what is being compared. But:
Where Format(date1, #dd/mm/yyyy#) <= Format(date2, #dd/mm/yyyy#)
Will ALWAYS have a problem because strings are being compared and
01/10/2019 is always less than 02/10/2018 because 1 is less than 2 and strings are compared character by character, left to right.

Where you DO have to format dates is when you are building where clauses using VBA that involve form fields.

Where date1 <= Forms!myform!mydate

In this case Forms!myform!mydate must be specifically formatted as either #mm/dd/yyyy# (which is the US default) OR as #yyyy/mm/dd# which is always unambiguous.

However

If Me.date1 <= Me.date2 does NOT require formatting as long as both date fields are defined as date data types. The comparison will take place based on the actual numeric value of the date rather than the string date that humans are used to.

Internally Access (Excel and SQL Server and other RDBMS also) stores dates as a double precision number. Access uses Dec 30, 1899 as its origin or 0 value. Dates prior to that are stored as negative values. Dates subsequent to that are stored as positive values so:

Dec 28, 1899 = -2
Dec 29, 1899 = -1
Dec 30, 1899 = 0
Dec 31, 1899 = 1
Jan 1, 1899 = 2

Time is stored as a decimal value so
1.5 = noon on Dec 31, 1899
1.75 = 6 PM on Dec 31, 1899
 

namliam

The Mailman - AWF VIP
Local time
Today, 09:05
Joined
Aug 11, 2003
Messages
11,696
Simply replace all TempDate
By format(TempDate, "MM/DD/YYYY")

or if you prefer SQLDate(TempDate)
 

Jordonjd

Member
Local time
Today, 09:05
Joined
Jun 18, 2020
Messages
96
Thanks for the info about the formatting where and when guys,
And thank you for the suggestions.

Im sure this is very clear for you but im really not sure how to actually execute these suggestions. sorry guys

SQLDate, i have no idea if i tried it correctly, the function was not working when i tried,

Replacing TempDate, again i blindy tried to find all TempDate text and reoplace it with what was said, sometimes it stopped the function from working so i changed bits around etc but i still have not got the results working correctly.

Is there a chance that someone could point out exactly what i have to do, and where?

I know that sounds very lazy but i have genuinely been trying to google and get it work for a long time
 

Jordonjd

Member
Local time
Today, 09:05
Joined
Jun 18, 2020
Messages
96
I've attached a copy here if anyone would like to play around at all
 

Attachments

  • WorkDays.accdb
    536 KB · Views: 426

theDBguy

I’m here to help
Staff member
Local time
Today, 01:05
Joined
Oct 29, 2018
Messages
21,322
I've attached a copy here if anyone would like to play around at all
Hi. I think maybe you're having problems because you modified the SQLDate() function. You're supposed to simply use it.

PS. I gave it a quick try, please see attached and let us know if it works or not. Cheers!
 

Attachments

  • WorkDays.zip
    40.6 KB · Views: 440
Last edited:

Jordonjd

Member
Local time
Today, 09:05
Joined
Jun 18, 2020
Messages
96
Hi. I think maybe you're having problems because you modified the SQLDate() function. You're supposed to simply use it.

PS. I gave it a quick try, please see attached and let us know if it works or not. Cheers!
Yer thats brilliant it seems to have done the trick. so:

aworkdays function it was just
Code:
80      If DCount("*", "tblHolidays", "HolidayDate=" & SQLDate(TempDate)) = 1 Then

The SQLDate was because i changed the format? it seems so weird that this function has mm/dd/yyyy format and yet its fine

Did you change anything on the countworkdays function?

Anway that is a massive help.

And thankyou to everyone else for their advice and input, and patience as well
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:05
Joined
Oct 29, 2018
Messages
21,322
Yer thats brilliant it seems to have done the trick. so:

aworkdays function it was just
Code:
80      If DCount("*", "tblHolidays", "HolidayDate=" & SQLDate(TempDate)) = 1 Then

The SQLDate was because i changed the format? it seems so weird that this function has mm/dd/yyyy format and yet its fine

Did you change anything on the countworkdays function?

Anway that is a massive help.

And thankyou to everyone else for their advice and input, and patience as well
Hi. Yes, I changed both modules on the same lines.

It might seem strange to use mm/dd/yyyy format, but that's what was needed. I personally use the yyyy-mm-dd format.

Good luck with your project.
 

namliam

The Mailman - AWF VIP
Local time
Today, 09:05
Joined
Aug 11, 2003
Messages
11,696
despite the fact that your tables "show" DD-MM-YYYY, the dates are stored as MM/DD/YYYY
Or as per Pat's post actually as a double, but access by default recognizes dates as MM/DD/YYYY unless that is not possible like 15/01/2020 or 2020/01/05

This makes things confusing because sometimes the euro format works, sometimes it does not.
In VBA and SQL -all things at technical level- you have to use MM/DD/YYYY
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:05
Joined
Feb 19, 2002
Messages
42,872
despite the fact that your tables "show" DD-MM-YYYY, the dates are stored as MM/DD/YYYY
The date data type is NUMERIC. It is not a string. Dates are stored as I described above with Dec 30, 1899 as the zero date. Other RDBMS and Excel use different origin dates but the concept is the same. I think SQL might use Jan 1, 1900 as the zero date..

As I mentioned, when both fields in a compare are date data types, NO formatting is necessary but if you do format, you are likely to have incorrect comparisons unless you format to the unambiguous yyyy/mm/dd format.

The ONLY time you should format a date is if you are working with a string as with an SQL string in VBA or the where clause of a domain function, or referencing a field on a form from a queydef. In those cases, you must convert the date to mm/dd/yyyy or the unambiguous yyyy/mm/dd because SQL assumes date strings are mm/dd/yyyy unless the string is unambiguous such as 30/01/2020. So that's why some dates work and others don't. Ambiguous dates are assumed to be mm/dd/yyyy because SQL has to assume something and that's what it assumes. Good for those of us who use mm/dd/yyyy as our default format. Bad for those of you who don't. But something we all need to understand.
 

namliam

The Mailman - AWF VIP
Local time
Today, 09:05
Joined
Aug 11, 2003
Messages
11,696
@Pat Hartman

Pretty much what I said, or meant to say....
When using #10/15/2020# in anything that is access (that means anything sql like, including domain functions) you need to format it MM/DD/YYYY.
I.e. ... where SomeDate = #" & Format(date, "MM/DD/YYYY") & "#"

Alternatively for "advanced users" that understand this, you can also safely do
where SomeDate = "& cdbl(Date)
or
where SomeDate = "& cdbl(Now)

But most users will not understand that 44119 = 2020/10/15
Simularly #2020/10/15# - #2020/10/10# = 5
Which is the same as 44119 - 44114
Simularly, adding 5 days 2020/10/15 + 5 = 2020/10/20

Conversly, #2020/10/10# - #2020/10/15# = -5, which in a date "format" will return ######### but -5 days still is a true result.

The "real magic" offcourse happens when 2020/10/31 turns over to 2020/11/01 which is nothing more than 44135 going to 44136, but this is where people start to misunderstand the "magic"

Other magic stuff, which makes working with time much easier.... realizing that adding 1 is the same as 24 hours
(#2020/10/15 18:00:00# - #2020/10/15#) * 24 = 18 hours
(#2020/10/15 01:05:00# - #2020/10/15#) * 24 * 60 = 65 minutes
(#2020/10/15 00:05:13# - #2020/10/15#) * 24 * 60 * 60 = 313 seconds
Allow for small rounding differences!! As allways when working with doubles.
For readers here, you can paste above formula's into the immediate window (CTRL+G):
?(#2020/10/15 18:00:00# - #2020/10/15#) * 24
To double check this if you dont believe the ease of working with dates, when you realize this.

This though doesnt work in all database systems.
 

Jordonjd

Member
Local time
Today, 09:05
Joined
Jun 18, 2020
Messages
96
Sorry for the late reply.

Thanks for the extra help and explanation, its really appreciated.

I really need to find some good starting point for learning VBA, SQL etc, im ok with incredibly basic if's and me.something = but beyond that whenever i try to read what someone has written it just goes straight over my head
 

Minty

AWF VIP
Local time
Today, 08:05
Joined
Jul 26, 2013
Messages
10,346
I found the best way was to actually dive in with a specific task in mind.
The real secret to success with a new thing like VBA is to break it down into very small steps.

So if you need to manoeuvre something around (like a time/date) do the basics in a query, use the QBE editor to select a small sample of the data and create a calculated field :

MyResult: Dateadd("dd",2,[YourDateField])

This shows you the basics and makes it easy to correct any syntax errors as the query window will tell you the problem.
You can then use that as a basis for making the code to do the same thing.

If you need to save that query then do some more manipulation on it. Baby steps until you get to where you want.
And when using VBA Debug.Print is your big friend - chuck anywhere into a loop to show you the values as it runs.

And as the @namliam suggests a lot of the code and functions can be used in the immediate window to prove a point or a simple calculation step.
 

Users who are viewing this thread

Top Bottom