Wont recognise 1/1/2014 - 10/1/2014 (1 Viewer)

Leroy04 · Feb 4, 2014

Hi
Don't really know where this fits in cause I don't know where the error is occurring.
First off Access 2010

I have a database which uses a module to calculate workdays excluding holidays. The Holidays are listed in a reference table which is checked by the module. this is output by a make table (select) Query.

Problem is it doesn't recognise the dates from 1/1/2014 - 10/1/2014.

It will recognise any dates either side eg 23/12/2013 - 31/12-2013 and 13/1/2014 and on, so I don't believe it is a date format problem.

This is the module I have borrowed to do this if any help and I have used it extensively in the past with no problems

===================================================
Public Function calcWorkDays(dteStart As Date, dteEnd As Date) As Long
Dim i As Long 'day counter
Dim dteCurDay As Date
'set i = 1 if you want the first date to count as a full day
'or i = 0 if you do not want the first day to count as a full day

i = 0
dteCurDay = dteStart
Do Until dteCurDay >= dteEnd
'check date against holiday table
If 0 = DCount("[HolidayDate]", "tblHolidays", "[HolidayDate] = #" & dteCurDay & "#") Then
'continue checking for weekdays i.e. i increases only if week day value is not 1(Sunday) and not 7(Saturday)
If Weekday(dteCurDay) <> 1 And _
Weekday(dteCurDay) <> 7 Then

i = i + 1
End If
End If
dteCurDay = DateAdd("d", 1, dteCurDay)
Loop

calcWorkDays = i
'based on Calculating Networkdays
End Function
---------------------------------------------------------

Any Ideas
Thanks
Leroy

pr2-eugin · Feb 4, 2014

Hello Leroy04, Welcome to AWF

Try wrapping them like this..

Code:

"[HolidayDate] = " & Format(dteCurDay, "\#mm\/dd\/yyyy\#"))

Dates are always treated in the American format mm/dd/yyyy. The reason 31/12/2013 is taken correctly is because Access understands there is no 31 month, so it flips it correctly, but 1/12/2013, it will take as 12th January 2013 not the actual 1st December 2013.

namliam · Feb 4, 2014

Do as Paul suggested, or a simular function:

Code:

"[HolidayDate] = #" & Format(dteCurDay, "mm\/dd\/yyyy")) & "#"

If your function is checking large periods you might be better off changing the function to something that is a little faster, for example the function that Pat made in his sample database, eons ago

http://www.access-programmers.co.uk...p?t=238821&highlight=Count+number+of+workdays

P.S. Intrestingly Pat isnt forcing the MM/DD/YYYY format in code...
P.S.2. You can force the format all you like, if you call the function like:
calcWorkDays(01/01/2014, 10/01/2014)
You are still going to get "hurt",
1) dates need to be wrapped in ##
2) They need to be in US format (i.e. 10/01 is Oct 1, not 10 Jan)
Or putting it all together:
calcWorkDays(#01/01/2014#, #1/10/2014#)

gemma-the-husky · Feb 5, 2014

and you would have had the same problem with 11/1/14 and 12/1/14 but these are a weekend.

the underlying problem is that SQL tries to treat dates as US if it can

so 10/1/14 is treated as October 1st

but 13/1/14 is unambiguously 13th January.

----
the solutions suggested force SQL to treat the date in the order you want.

10/1/14 is forced to be treated as 10th January 2014

Mihail · Feb 5, 2014

I don't understand what is the meaning of the "\" character. Can you explain this for me ? Thank you.
I deal with the dates by using this function:

Code:

Public Function FormatDateTime(UzualDateTime) As String
    FormatDateTime = Year(UzualDateTime) & "-" & Month(UzualDateTime) & "-" & Day(UzualDateTime) & " " & _
                        Hour(UzualDateTime) & "." & Minute(UzualDateTime) & "." & Second(UzualDateTime)
End Function

pr2-eugin · Feb 5, 2014

Mihail said:
I don't understand what is the meaning of the "\" character. Can you explain this for me ?

The Back slash escapes special characters. So it will treat it as char instead of giving it a special meaning. Simple test.

Code:

? Format(Date, "\#mm\/dd\/yyyy\#")
#02/05/2014#
? Format(Date, "#mm/dd/yyyy#")
4167mm/dd/yyyy5

Mihail said:

I deal with the dates by using this function:

Code:

Public Function FormatDateTime(UzualDateTime) As String
    FormatDateTime = Year(UzualDateTime) & "-" & Month(UzualDateTime) & "-" & Day(UzualDateTime) & " " & _
                        Hour(UzualDateTime) & "." & Minute(UzualDateTime) & "." & Second(UzualDateTime)
End Function

The problem I find with this code is that, its overloading, many functions - Year, Month, Day, Hour, Minute, Second. When it could simply use one Format function.

On top of that it still will fail when you try to use it in Queries. That's my honest opinion ! I might be wrong !

namliam · Feb 5, 2014

The \ works as an escape character for the /

"proper" us dates are MM/DD/YYYY, which includes the / part
"proper" us time / international time is HH:MM:SS

You are using the (guessing) german local setting or the ISO date/time notation, though again for ISO time would again be HH:MM:SS not HH.MM.SS.
Possibly then post fixed with the UTC timezone.

Mihail · Feb 5, 2014

Thank you !

namliam · Feb 5, 2014

pr2-eugin said:
The Back slash escapes special characters. So it will treat it as char instead of giving it a special meaning. Simple test.

Code:

? Format(Date, "\#mm\/dd\/yyyy\#") #02/05/2014# ? Format(Date, "#mm/dd/yyyy#") 4167mm/dd/yyyy5

Which one is the escape character has something to do with your regional settings....

Code:

?Format(date(), "MM\/DD\/YYYY")
02/05/2014
?Format(date(), "MM/DD/YYYY")
02/05/2014
?Format(date(), "MM\DD\YYYY")
02D5Y1436 << Can you make sence of this? Most people cant :)

Not exactly sure how it works, just that \/ seems to more or less guarantee it... where as only / or only \ can fail here and there.

The problem I find with this code is that, its overloading, many functions - Year, Month, Day, Hour, Minute, Second. When it could simply use one Format function.

Definatly, simple format will do the same thing: Format(now(), "YYYY-MM-DD HH.MM.SS")
For some people some \ may be in order

On top of that it still will fail when you try to use it in Queries. That's my honest opinion ! I might be wrong !

The function should work just fine in any query

And WTF with you and me and the cross postings, Note to self: dont post with Paul online :banghead:

Mihail · Feb 5, 2014

Because you are more skilled than me, take a look here:
http://bytes.com/topic/access/answers/925341-help-trouble-handling-date
What you understand from this discussion ?

pr2-eugin · Feb 5, 2014

namliam said:
Not exactly sure how it works, just that \/ seems to more or less guarantee it... where as only / or only \ can fail here and there.

Yes totally agree. that's why I go with the default syntax for Dates.

namliam said:
The function should work just fine in any query

If that is to do with regional settings, yes it "might" work ! Then again, the main reason we force any date to stick to a format is to override the Regional settings and give Access only what it wants.

namliam said:
And WTF with you and me and the cross postings,

Ha ha ! because great minds think alike?

namliam said:
Note to self: dont post with Paul online :banghead:

Please don't, I might give some stupid answers, people like you can get me on track !

pr2-eugin · Feb 5, 2014

Mihail said:
Because you are more skilled than me, take a look here:
http://bytes.com/topic/access/answers/925341-help-trouble-handling-date
What you understand from this discussion ?

That is exactly what I am trying to say, using the Format function we force any date to an Access understandable date. Thus avoiding the whole confusion of International/Regional Date ambiguity.

In the thread, the user has created a function to do that, you don't really need that, but could be handy !

namliam · Feb 5, 2014

pr2-eugin said:
In the thread, the user has created a function to do that,

Paul, doublecheck the function... it is a text function and it is accepting a date value.

Thus it converts date to string, which obviously already is a date inside the database ... Thus it isnt related to forcing the access/us date format persee....

It is however using the ISO format, which will work 100% for access as well as US dates. However to most people ISO dates are even more confusing than US date formats and well US dates IS what access at its core uses,requires and needs.... Not using ISO prevents any possible future issues....
(an)other "nice" way to deal with date formatting issues is something like:

Code:

"Select ... from ... where ... and yourdate = dateserial(" & format(Anydate, "YYYY,MM,DD")  & ")"

pr2-eugin · Feb 5, 2014

namliam said:
Thus it converts date to string, which obviously already is a date inside the database ... Thus it isnt related to forcing the access/us date format persee....

It is converting a Date to a String, that is formatted to a US format, with the ## surrounding it. So simply using it like. (A)

Code:

DCount("*", "someTable", "someDateField = " & formatSQLDate(Date))

The above can again be translated as.

Code:

DCount("*", "someTable", "someDateField = [COLOR=Red][B]#[/B][/COLOR]" & Format(Date, "mm/dd/yyyy") & "[COLOR=Red][B]#[/B][/COLOR]")
DCount("*", "someTable", "someDateField = " & CDbl(Date))

The above two will give the same result as (A).

namliam · Feb 5, 2014

The function as per given by Mihail, doesnt ... FormatDateTime

Mihail · Feb 5, 2014

namliam said:
The function as per given by Mihail, doesnt ... FormatDateTime

But, somehow, it work.
Here is the most recent when I used it:
http://www.access-programmers.co.uk/forums/showthread.php?p=1329140#post1329140
but I used it many times before with no issues.

Can you, ore someone else, to imagine a case where this don't work ? A small DB will be the best as example.
Will not be a problem to change my regional settings in order to see what happen.

Please, don't misunderstand.
Just I like to understand how to deal with dates.
To understand, not to fill a format because "it works".

namliam · Feb 5, 2014

Mihail said:
But, somehow, it work.

Not somehow... like I said earlier...

It is however using the ISO format, which will work 100% for access as well as US dates. However to most people ISO dates are even more confusing than US date formats and well US dates IS what access at its core uses,requires and needs.... Not using ISO prevents any possible future issues....

ISO dates work, without any issues... Since ISO doesnt leave any room for interpertation.... the implicit converversion from ISO to US date will not fail ever (that I know of currently, in current versions, cant vouch for future versions)

However it remains an implicit conversion, from ISO to US date format which might go wrong some day some where. Only way to guarantee it works (in current versions) is to use the date format that access requires between the ## marks, which is the US date format.

Or alternatively use the DateSerial function as I posted earlier for date only dates.... DateSerial may just be the savest guarantee since if MS do deside to some day in the future change stuff around to another date format (i.e. Unix timestamp, dunno why by hey) They will have to continue support of such functions as DateSerial...

Long post made short, US date format is the only proper and guaranteed way to work with dates.

On top of which, Mihail, you are using 6 functions where you can use only the one Format. Will not make a huge deal of difference right now with current computers. However 0.01 second multiplied by 10.000 records/itterations starts counting.... Though I dont thing many people care much more about performance and the "tidiest" way of writing code...
I.e. the difference between:

Code:

Mysql = "bal bal "
mySQL = MySQL & "Bla"
vs
Mysql = "bal bal " & _ 
        "Bla"

Leroy04 · Feb 6, 2014

Thanks Guys,
As soon as I read that it was obvious,
Your suggestion worked a treat
Ciao

Mihail · Feb 6, 2014

Thank you too !

Wont recognise 1/1/2014 - 10/1/2014 (1 Viewer)

New member

Super Moderator

The Mailman - AWF VIP

Super Moderator

Registered User.

Super Moderator

The Mailman - AWF VIP

Registered User.

The Mailman - AWF VIP

Registered User.

Super Moderator

Super Moderator

The Mailman - AWF VIP

Super Moderator

The Mailman - AWF VIP

Registered User.

The Mailman - AWF VIP

New member

Registered User.

Similar threads

Users who are viewing this thread