Douglas J. Steele said:
Matt: Read my reply elsewhere in this string. Much of what you're suggesting
is incorrect.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Actually, the date value is an 8 byte floating point number, where the
integer portion of the value represents the date as the number of days
relative to 30 Dec, 1899, and the decimal portion represents time as a
fraction of a day.
When you are talking about strictly the date, you describe it as the integer portion - this is to what I was referring. If I take a
datevalue of 4/5/2005 and convert it to a long integer I get the number 38447, a 5 digit number. I am not debating that a date &
time value is stored as an 8 byte float, but instead referencing the date portion which is 4 bytes, which is a 32 bit long integer.
Why? Store the date as a date, and use a format statement to display it how
you want. Remember: the date is stored as a number, not in a specific
format.
I would like to give my answer to your question: I found that storing dates as text values has merit. I work daily with data
derived from Excel files and sometimes there are problems with date values being either improperly entered or scores of null date
values because of conditions where a date does not exist yet for a record. You may ask: so what are the problems?
To elaborate: when I import dates as text I have the means to review improper date representations such as 5\15\2005, and can fix
this to represent a proper date 05/15/2005. I replace null values with a filler before import because I found that this prevents
data corruption that sometimes occurs when there are scores of null values in a column of dates obtained from a large Excel file.
I have experienced random shifting of column values under these circumstances (large amounts of null values in a column containing
date values) where some values will migrate to an adjacent field/column intermittantly producing unwanted results in the newly
created table derived from the imported file. I have found that this can be prevented by filling in the Excel cells having null
date values with a placeholder, of which I chose a '-' symbol, and then choosing to import as a text value.
If later I need to operate on a date then I can choose all the values <> "-", convert to a date, then calculate date differences, or
other date related comparison as needed.
Another reason for preferring to store a date as text would be personal preference. Perhaps I would prefer a format choice of
YYMMDD or DD/MM/YYYY, and with text I can have it my way. I can choose my preferred format easily and it requires no additional
efforts to later display the formatted date representation.
To date I have never had any problem with storing dates as text and have never lost date information. My preferred storage format
is text in this pattern: mm/dd/yyyy, and this has worked well for me.
What I offer is a choice for the user, and this choice can be very effective depending on the circumstance. Its nice to have
options.
These are both iffy, since you're relying on Access to coerce the value from
a string into a date before it applies the format. Unless you can be certain
of what the user's Short Date setting is, it may not coerce the way you want
it to.
I found 100% accuracy in my tests, under my date settings. It would be very easy to adjust the formulas if needed by a competant
VBA programmer (likely everyone reading this) if there were some unusual system date settings. It is really as easy as looking at
the original date value and the converted to text date value side by side to see if it works as expected.
When you look at the help description of how the Format function works it should be easy to see that the month, day, year values can
be recognized by this function when it receives a date value, and it is then very easy to arrange them in the manner desired such as
dd/mm/yyyy using this function. That seems to be the point of the Format function is that it offers user preferred output in the
form of a string of text. Viewing the results of this function affirm the correct transformation has been obtained.
Converting from a known format text string date such as dd/mm/yyyy to a date value is really quite easy and effective. I have never
had any date descrepancies between the original date value and the resultant date value after converting: date value -> text date
value -> date value.
Please note that when I refer to date values that I am not referencing the time values portion of a date value which I am not
including in my presentation of tranformation methods. If someone really wants to reference both date and time then they will need
a different approach.
I would like to say that your points have strong merit also and depending on what the programmer is trying to accomplish then
storing the date as a numeric date/time value may be better, especially if the time portion is needed. It really depends on the
circumstance and personal preference.
Thanks for your ideas and opinions, they are appreciated,
matt
