Re: Date confusion
- From: "matt -`;'-" <mbox1@xxxxxxxxxx>
- Date: Sat, 10 Sep 2005 21:08:40 -0400
"Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote in message news:uZOLFdftFHA.3040@xxxxxxxxxxxxxxxxxxxxxxx
> Matt: Read my reply elsewhere in this string. Much of what you're suggesting
> is incorrect.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "matt -`;'-" <mbox1@xxxxxxxxxx> wrote in message
> news:1b2de$43228bbf$97d50759$18274@xxxxxxxxxxxxx
> >
> > "Ace9x" <Ace9x@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:AB5E3A45-94AD-4D46-8E23-9CA35AC56FED@xxxxxxxxxxxxxxxx
> >> I have created a function in the Main module in Access, which when called
> >> with a Financial Year parameter creates 52 weekly entries in a table with
> >> period no as 1 to 52, start and end date. This seems okay until I view
> >> the
> >> new entries in the table. Some of the dates are displayed in dd/mm/yyyy
> >> format whilst others appear in mm/dd/yyyy format. There are no format or
> >> input masks present on the table itself.
> >> For the sql insert statement of the 2 dates I used :-
> >>
> >> "#" & format(dteStart,"dd/mm/yyyy") ",#" & format(dteEnd,"dd/mm/yyyy") &
> >> "#"
> >>
> >> Obviously something is wrong either with the insert SQL or the display of
> >> the dates in the table, can someone please advise appropriately.
> >>
> >> Cheers,
> >> Steve
> >
> > The dates you are probably working with are a numeric value that the
> > program displays in a date format like m/d/y. The actual date
> > value is a 5 digit Long Integer.
>
> 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.
> > If you are working with a numeric date and you want the format DDMMYYYY,
> > then you will need to store the dates you use as String
> > representations of a date in the Table. (The data type in the table will
> > be text instead of date.) Visually this will look the
> > same to you, but to the program it will see a Text String and will keep it
> > properly formatted to dd/mm/yyyy. If you need to perform
> > math on the dates then you will need to convert them back to date values.
> > See below for both methods.
>
> 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.
> > '--Convert a numeric date value to a string date value
> > Format(CStr(dteStart),"dd/mm/yyyy")
> > '--Convert a string date value formatted as DD/MM/YYYY to a numeric date
> > value
> > DateValue(Mid(strDate,4,2) & "/" & Left(strDate,2) & "/" &
> > Right(strDate,4))
>
> 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
.
- References:
- Re: Date confusion
- From: matt -`;'-
- Re: Date confusion
- From: Douglas J. Steele
- Re: Date confusion
- Prev by Date: RE: Compile Error - Procedure too big
- Next by Date: Re: Subject: Should I use "IsNull" or something else? (Duh!)
- Previous by thread: Re: Date confusion
- Next by thread: How do I pass variables for 'Sorting and Grouping' in a report?
- Index(es):
Relevant Pages
|
