Storing different date formats in same field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sal - 30 Oct 2004 23:23 GMT

I am using Access 97. For sorting purposes, I would like to sort the
following date types in the same field:
YYYY/MM and YYYY/MM/DD
I've tried appending a table that has dates in the first format in to a
temporary table, then appending the table that has dates in the second format
into the same temporary table. I have tested by setting the receiving field
in the temporary table to text type and to date type. Each time the
temporary table interpretes the dates in mm/dd/yyyy format. If possible I
would like to use queries to manipulate the data. If that is not possible I
would appreciate a code snippet.
Many thanks.

Signature

Sally

Reply to this Message

Steve Schapel - 31 Oct 2004 09:23 GMT

Sally,

The format only affects the way the data is displayed, and has no
relevance to the actual data in the field. The sorting applies to the
actual data, so any change in format will not affect the sorting.

Ok, here's the confusing part, based on the fact that the word "format"
has two meanings. What I have mentioned so far relates to the Format
property of the field or control. If you use the Format() function, it
will return a string, and sorting on this string will give you the type
of control you seek. In your query, make a calculated field like this...
DateForSorting: Format([YourDateField],"yyyymmdd")

However, I am puzzled as to your purpose in doing this, because the
result of this sort will be identical to sorting directly on your date
field itself.

Signature

Steve Schapel, Microsoft Access MVP

> I am using Access 97. For sorting purposes, I would like to sort the
> following date types in the same field:
[quoted text clipped - 7 lines]
> would appreciate a code snippet.
> Many thanks.

Reply to this Message