Never, never, never, did I say never? format dates except for display on a form or report and in those cases, use the format property of the control if you want something other than the default date format for your region. All the problems you experience are caused by formatting a date which converts its data type from Date/Time to String. If you always define your controls as Dates, Access "knows" how to handle them and understands how to convert the display format to the numeric format it uses internally. In the rare cases where you need to use literal dates in SQL, you will need to either use the standard US date format of mm/dd/yyyy or an unambiguous format such as yyyy/mm/dd or dd/mmm/yyyy.
It will help if you stop thinking of dates as strings and start thinking of them as numbers. In the case of Access, the origin date (0 value) is Dec 30, 1899. Negative values are dates prior to that date and positive values are after that date. The decimal part of the double precision number represents the time as a fraction of a day. SQL Server, Oracle, etc. and Excel use a similar concept for internal date storage but use different origin dates. They also use different field sizes and so support different valid date ranges.
While we are on the subject of bitching about date formats watch out for this incompatibility between MS SQL Server and Access that I stumbled into recently.
I use this function to format dates for SQL strings.
Code:
Public Function DateSQL(RawDate As Date, Optional UseTime As Boolean = False) As String
Dim strFormat As String
If UseTime Then
strFormat = "\#mm\/dd\/yyyy hh\:nn\:ss\#"
Else
strFormat = "\#mm\/dd\/yyyy\#"
End If
DateSQL = Format(RawDate, strFormat)
End Function
Midnight.
I can’t find a link to the reference but I recall from a few years ago midnight as being defined by the Greenwich Observatory as the first instance of the new day. Meaning that midnight is AM not PM and therefore there is only one midnight per day.
Extract from above link…
>>And finally, why 30 December 1899? Why not, say, 31 December 1899, or 1 January 1900 as the zero day? Actually, it turns out that this is to work around a bug in Lotus 1-2-3! The details are lost in the mists of time, but apparently Lotus 1-2-3 used this date format but their devs forgot that 1900 was not a leap year. Microsoft fixed this bug by moving day one back one day.<<
--------
Not all negative dates are prior to midnight 30 December 1899.
Rain pointed this out in post #19 but nobody took up on it.
0.5 is midday on the 30th Dec 1899
as too is…
-0.5 is midday on the 30th Dec 1899
It could be visualized as (+/-)Whole Days (.) +Fractional Days
The integer part of the date (days) can move backwards in time but the fractional part of the date (time) always moves forwards in time. So in the example shown by Rain zero, both positive and negative, are added to the date and so is the Time. Days might move backwards or forwards but ‘time marches on’. So when calculating on raw numbers, across day zero, we need to be careful else we could be out by 1 day.
--------
Galaxiom mentioned in post #22 that Access can work wonders with dates, even if we would prefer it not to.
It may be going overboard to say this but the IsDate() function is lenient to the point of uselessness. A double check can be made by using the Eval() function but it has the problem of not recognizing some dates which might be otherwise useful.
--------
Access can also use the Hijri Calendar.
Access 2003: Tools>Options>International>Use Hijri Calendar
Unless you need it, turn it off else it will make your eyes water.
Dates get really messy if you consider the implimentation of the Gregorian calendar. It was progressively adopted throughout the world from 1582 through to 1923. The late adopters had to skip an increasing number of days. For example, in the the British Empire , Thursday 14th September 1752 directly followed Wednesday 2nd September 1752.
Access simply ignores this and all dates are calculated by the Gregorian calendar regardless of the system in force at the time and what would have been the designated date of the period.
The DateTime format in MS SQL Server avoids the issue for those countries which were part of the British Empire in 1752 by designating a minimum date of 1st January 1753.
Short DateTime which has a resolution of one minute has a minimum date of 1/1/1900.
There are so many different ways to store dates and times. There are at least six in MS SQL Server alone. The maximum resolution is 100 nanoseconds (0.0000001 seconds) so there must be some interesting rational to optimise those.
There are storage models based entirely on counting the seconds or fractions of a second with no day component at all. (For example, Unix time which is the number of seconds since 1/1/1970.)
I have a vague memory of one datetime format that splits the binary value using some bits to count days and others for fractions of a second. Consequently the value as a whole has no direct interpretation.
For those of us who don't yet quite have the depth of understanding, I think a few rules of thumb would be useful? I hope these are correct and clear up some of the confusion I experienced?:-
1. The date format property of the column in table design-view, does NOT define the column in the normal sense (as with, say, LONG INTEGER, MEMO or whatever) but simply dictates they way the date is displayed when in the table's open-view.
Neither does this property affect the way controls bound to that column are displayed. Altering the column format in table-design therefore will not affect date displays in forms or reports.
2. Adding Dates into Tables Using Execute.SQL_String method.
a) When using the VB built-in functions such as NOW() and DATE() (as opposed to SQL's NOW):-
- These functions will return the Date in accordance with the Windows locale settings so they will need to be 're-formatted' when outside U.S. , e.g. for U.K.
Code:
Dim SQL_Str As String
Dim Add_Date As Date
Add_Date = NOW()
SQL_Str = "UPDATE Your_Table SET Date_Added = #" & Format(Add_Date, "dd-mmm-yyyy hh:nn:ss") & "# WHERE ....."
CurrentDb.Execute SQL_Str
b) For user-supplied dates via an Unbound control into a Date Column:
- Use the format property of the control for General Date, Long Date etc as preferred. Dates supplied by the user will need to be trapped and 're-formatted' when outside U.S. , e.g. for U.K.
Code:
Dim SQL_Str As String
Dim Add_Date As Date
Add_Date = Your_Control_Name
SQL_Str = "UPDATE Your_Table SET Date_Added = #" & Format(Add_Date, "dd-mmm-yyyy hh:nn:ss") & "# WHERE ....."
CurrentDb.Execute SQL_Str
You will, of course, have to satisfy yourself that the first two digits supplied by the control are indeed intended to be the day. (I also add a 'format legend' in a label to these controls to make sure the user knows exactly which way to supply them)
3. Adding Dates into Tables Using via RSs in VB
a) Again, when using the VB built-in functions such as NOW() and DATE() they will need to be 're-formatted' when outside U.S. , e.g. for U.K.:-
Code:
Dim SQL_Str As String
Dim Add_Date As Date
Add_Date = DATE()
YourRS![Date_Added] = #" & Format(Add_Date, "dd-mmm-yyyy") & "#"
YourRS.Update
b) Via an RS in VB with user-supplied date(s) from unbound controls.
As with 2.b , you will need to be sure that the first two digits are the day. They will then need 're-formatting' when outside U.S. , e.g. for U.K.:-
Code:
Dim SQL_Str As String
Dim Add_Date As Date
Add_Date = You_Control
YourRS![Date_Added] = #" & Format(Add_Date, "dd-mmm-yyyy") & "#"
YourRS.Update
4. For user-supplied dates via a control bound to a Date Column:
- Simply use the format property of the control for General Date, Long Date etc as preferred. No other action is required. Even though the format appears to be local, Access will enter /retrieve them correctly.
5) For user-supplied dates via a control bound to a Text Column (I know, I know... why? but for the sake of completeness):
- Access will enter them as a String in exactly the format you supply. Storing dates as strings is especially dangerous when it comes to retrieving/manipulating dates.
6. Retrieving Dates from Tables Using RS.Open method.
a) For user-supplied dates via Unbound controls from Date Columns:
- Use the format property of the control for General Date, Long Date etc as preferred. Dates supplied by the user will need to be trapped and 're-formatted' when outside U.S. , e.g. for U.K.
Code:
Dim SQL_Str As String
Dim Start_Date As Date
Dim End_Date As Date
Start_Date = Your_Start_Control_Name
End_Date = Your_End_Control_Name
SQL_Str = "SELECT * FROM Your_Table WHERE Date_Added BETWEEN #" & Format(Start_Date, "dd-mmm-yyyy hh:nn:ss") & "# AND #" & Format(End_Date, "dd-mmm-yyyy hh:nn:ss") & "#"
Your_Rs.Open SQL_Str etc etc
b) For user-supplied dates via Bound controls from Date Columns:
- Use the format property of the control for General Date, Long Date etc as preferred. Supply dates provided by the User directly into the SQL_Str
Code:
Dim SQL_Str As String
SQL_Str = "SELECT * FROM Your_Table WHERE Date_Added BETWEEN #" & Your_Start_Control_Name & "# AND #" & Your_End_Control_Name & "#"
Your_Rs.Open SQL_Str etc etc
7. When Comparing Dates in VB.
a) Using the date column only... No 're-formatting' required e.g.
Code:
If Date_Column_1 > Date_Column_2 then
....
End if
b) When One Date is a User Supplied Date from an Unbound Control some 're-formatting' is req'd when outside U.S. , e.g. for U.K.
Code:
Dim End_Date As Date.
End_Date = CDATE(Format(Your_End_Control_Name, "dd-mmm-yyyy hh:nn:ss"))
If Start_Date_Column > End_Date then
.....
end if
Please feel free to comment or even demolish these!
1. The date format property of the column in table design-view, does NOT define the column in the normal sense (as with, say, LONG INTEGER, MEMO or whatever) but simply dictates they way the date is displayed when in the table's open-view.
The above is incorrect. Data TYPE of a column is set by MEMO, LONG INTEGER or Date/time or whatever, in quite normal sense.
FORMAT is a different animal, only affecting the display of the data, and interpretation, in case of ambiguous dates.
When implicit conversion to string takes place, you need to replace that with an explicit conversion with reformatted dates, outside US.
When no conversion takes place, you don't.
That is all that there is to it.
When dealing with variable assignments or comparisons, for as long as there is no conversion to string, no reformatting is needed.
YourRS![Date_Added]=Date()
works just fine, no matter where, because this is an assigment statement, saying, in effect, "grab the contents of Date(), whatever that may be, and stuff it into
YourRS![Date_Added]". No conversion to string takes place here.
In any constructions of strings
"SELECT something WHERE MyDate=" & Date
Date is implictily converted to string, and must be EXPLICTLY converted to string outside US.
DOUBLE or SINGLE are also DATA TYPES, but their FORMAT is affected by locale settings ("," vs. "." as decimal separator). Decimal numbers in SQL STRINGS are affected just like dates : when implicit string conversion takes place, your SQL gets screwed up, if in a locale where "," is used as separator. (Use Str-function in such cases, to EXPLICTILY do the string conversion).
The locale setting does not affect comparisons or assignments, neither for dates nor for decimal numbers.
Thanks for reviewing my rules-of-thumb... Yes, apologies, my point 1. was ambiguous.
Interesting point about string-to-date conversions, thanks.
When msgbox'ing DATE() on my machine it returns local UK format of dd-mm-yyyy... from what you say, this is because it has been intercepted by Windows/Access on it's way to Msgbox and has been 'auto-formatted' in local format? This explains why your example of
YourRS![Date_Added]=Date()
does not need formatting?
I notice that you do not have # symbols in "SELECT something WHERE MyDate=" & Date... Are they not needed by SQL? And in UPDATEs too?
It is mightily confusing... Also, I seem to recall I got differing results with updating dates with ADO and DAO RS's... drove myself nuts one day trying to get a date updated with #'s and without them?
Just a futher quick question string-to-date conversion .... if an unbound textbox is used to supply a date into a module, I'm assuming there is an explicit conversion to string (irrespective of the textbox format value) so outside of the U.S. one would need to reformat the input from the textbox... so my point 2.b) is true?
I'll hang about a little longer to see if I get further feedback then I'll update my notes/examples... I need them desperately so I guess others might too.... cheers
There are many different scenarios when using dates, so many so that it is hardly worth trying to remember them all. By all means keep notes but, better still, try to learn how to test them.
For example (and being rather pedantic): the problem is not that people are inside or outside the US, or that their code is running inside or outside the US. The problem is that the code is running under the influence of regional settings and regional settings can change even on a machine used in the US.
There is a problem that applies specifically to people in the US, and maybe some other countries as well. When the software is first loaded on a machine in the US the load is generally done with the default US regional settings. Those default regional settings simply work correctly for them and that is a problem for people in the US. They do not see a problem and so are the worst at writing code which is suitable for all regional settings.
There is no problem for them and so why should they learn how to fix something they can not see? It’s not a skill level thing on their part; it’s simply that they have not had the necessity to learn about something they can not see.
These days it seems to be politically incorrect to even suggest that, generally speaking, people of a nation have a problem but all it is is a fact. Political correctness has no part to play with executable computer code. It hinders learning and if it is wrapped in so many layers of sweetness to become palatable the point may be lost. The most politically correct question we can expect from a computer is; would you like to enter debug mode?
All that, in a roundabout sort of way, leads to the following…
1.
a. Don’t even try to remember all the ways regional settings can screw code.
b. Simply remember they can, and often do, screw code.
c. Regional settings are for people not computers.
d. If something needs to be displayed, use regional settings.
e. If something needs to be executed, don’t allow regional settings to interfere.
f. SQL strings are for execution, not for display.
g. SQL strings are code.
h. Test each instance of code by changing the regional settings to see if they interfere.
i. If, by some chance, the IT department says you are not allowed to change regional settings then tell them you can not test your code without the change.
2.
a. As with 1.g above, SQL strings are code. Booleans entered into SQL strings are subject to the language pack installed on the computer. The vast majority of people reading this will be using the English language pack and therefore there is no problem. So people using the English pack are in the same boat as people in the US with their regional settings…we can not see the problem and therefore have no necessity to learn.
b. A Boolean has the values of (0) for False XOR (not 0) for True. When inserted into an SQL string False and True are inserted not (0) XOR not (not 0). But that’s for the English language pack. With the Dutch language pack Onwaar XOR Waar is inserted for Booleans. SQL strings are parsed in English and so Onwaar and Waar both fail. There are ~ 35 different language packs so who knows what is inserted for other language packs?
c. b. (above) can’t be tested without other language packs installed, it can’t even be seen. To overcome the Boolean insertion into SQL strings problem the answer is quite simple. Don’t use Booleans but use Integers instead. A Boolean is word language pack specific but an Integer remains a number in all language packs. Your compute loves numbers and hates words and so, if you use numbers, it will not invite you into debug mode.
d. Default names of controls can be a problem. The default name for the ‘Detail’ section on a Form is ‘Detail’ in the English language pack. But it’s not ‘Detail’ is other language packs. The change may be as small as ‘Details’ (note the ‘s’ on the end) or it may be something I doubt if I could type for the Norwegian language pack. As with the solution with the Booleans in c. above the solution is to escape from the words and go with the numerical. Not Me.Detail.BackColor = 255 but Me.Section(acDetail).BackColor = 255 where acDetail equates to the zero (0) index into the Form’s section collection in all language packs. Again… your compute loves numbers and hates words and so, if you use numbers, it will not invite you into debug mode.
3.
a. Why does it even matter if a person only writes code for the US market? Well, the real answer to that question is somewhat politically incorrect…at face value. If all that a person wants to do is to satisfy a closed market then that is fine. (By closed market I mean a local system locked down to a rigid computer install and operation within a specific company.) But if the intension is to post sample code on the www then it is appropriate to understand that the code can be accessed from almost anywhere, and by almost anyone, in the world.
b. It seems to me that a professional approach would be to write code that works, first time (after debug), under all possible scenarios (Holy Grail maybe). It means being aware of the failures that may apply under different world conditions. It also means to me that the requirement for writing fault tolerant code can not be practiced by people writing code under a default or forced environment.
c. Default or forced environment. This also means to me not using references, they can be too easily broken. Late binding gets us closer to the Holy Grail but is not the Holy Grail. Late binding can alleviate serious problems when moving from one version to another. But there is a, perhaps minor, problem with late binding. Late binding expects the version, bound to at runtime, to be compatible with the version under which the application was developed. Sometimes that is not the case but, in general, late binding removes a lot of the hassles. Late binding means not using references and that means all references which can’t be removed.
4.
And so; what to do? Simply be aware of the problems.
a. With dates test with different regional settings. French (Switzerland) is good for testing dates but not much good for testing times. For times use Spanish (Puerto Rico) or English (New Zealand) they both use a period between AM or PM as in a.m. or p.m.
b. With Booleans test by looking at the SQL string. If you can see words like False XOR True then swap to using Integers so you see numerical values like 0 XOR (Not 0)
c. If you can see the name of a Form Section then change it to the numerical value of the index into the Form Section collection.
d. If you are using a reference, other than the two references you can’t remove, then consider rewriting the code to late bind it and remove the reference. Do this at the procedure level even if you have other procedures that may require the same reference. Once all procedures comply with late binding the reference can be removed.
That certainly would be incorrect. Where the value of a variable or control is concatenated into the string it is arriving as a string and therefore the developer must treat it as a string and include delimiters.
Access will send the query string directly to the database engine. Such queries are very vulnerable to special characters being inside the string and they often fail when quote marks are included.
The alternative is to refer directly to the control in the SQL string.
SELECT something WHERE MyDate = Forms!FormName.ControlName
In this case the control is arriving as a reference. Access will convert this reference to a parameter query and send it to the engine. The parameter is passed with a DataType and is not converted to a string at any point.
They are very reliable because the parameter effectively contains any special characters.
However such a query can only be run by DoCmd.RunSQL which is effectively the same process as running a stored query. They don't work as CurrentDb.Execute because this command speaks directly to the database engine without Access there to translate the control values. Execute has no knowledge of the forms.
It also means they are not portable. They must always refer to that particular control whereas a a value inserted from a string can come from anywhere. You can of course construct the parmaeterised version dynamically as a string but that is somewhat more complicated.
Thanks ChrisO, MissingLinq, SpikePL, Pat, Smig, RainLOver and G.A.H. (hope I did not miss anyone) for such amazingly comprehensive answers. This has filled out further gaps (some known and, to follow ChrisO's theme, some I didn't even realise I didn't know!).
When one is totally self-taught it's easy to assume something from false positives in testing.. that is to say when something appears to produce the correct result but for a coincident reason rather than for one's assumed reason... especially so with dates...and off we wander into the Access jungle doing something that sooner or later is going to bite us back. These threads help immensely.
I take ChrisO's point re- the number of possible uses for dates and consequent examples... and, equally, that it's far better to understand than to use the parrot method. Nevertheless, I think the updating my examples would serve as a good aide memoire and if it helps other intermediates so much the better. I'll get around to this in the next day or two... it would be great if you experts would run an eye over them?
Thanks to all..... I've read mountains of threads on dates but I think I understand the problem so much better now.
P.S. ChrisO - on the topic of Local settings... Since you are an Aussie, I'm surprised to note you use the word 'period' rather than 'full-stop' ..I think your personal regional settings may need looking at..
P.P.S. Re- G.A.H's advice about DoCmd.RunSQL... I tend to use compound transactions and I don't think that can be done with that particular method but I'll include one in my notes anyway.
Period and Full Stop is like And and Ampersand. We say Full Stop and And but know them to be a Period and an Ampersand. Unlike the Americans we do, or should I say, should use the Queen's English.
@ChrisO - a note about booleans
from my experience if using booleans and keep them as True/False access will recogize the True/False words even in non English systems (I use Hebrew)
But if you format it as Yes/No Access will try to use the local word for it Trying to put a Yes/No as the default value will result in the local word.
I think people using the English language pack will remember it better if they can see just one instance of what can go wrong.
So I wonder if you could do us a small favour and try this with the Hebrew language pack?
Code:
Sub Test()
Dim strSQL As String
Dim blnBool As Boolean
blnBool = True
strSQL = "SELECT * FROM MyTable WHERE SomeBoolean = " & blnBool
MsgBox strSQL
End Sub
When the MsgBox displays strSQL could you please post back the displayed string?
Trying to put a Yes/No as the default value will result in the local word.