Date Format Change

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My date/time data starts out like this.

4/2/2006 9:00:00 AM

When I import, can Access reformat the date/time to Apr-06 or something like
this. Also. I need to be able to run queries on this field like: show me the
records with date/time greater then or equal to Apr-06 but less than or equal
to Jul-06.

Thank you in advance.
 
The format in your table is not important. The date and time is actually
stored as a number.

To display or print a date in a particular format, use the "format" option
when you add that field to your forms or reports.

To do your query, put something like the following in your criteria...

Between #04/01/06# and #07/31/06#
 
If the data is successfully imported into Access as date/time data types you
can format it however you wish in Access either using the Format property of
a control in a form, report etc. or using the Format function. To get the
MMM-YY format you want would be:

Format(YourDateField,"mmm-yy")

To return rows within the date range April to July 2006 you'd do this:

SELECT *
FROM YourTable
WHERE YourDate >= #04/01/2006#
AND Yourdate < #08/01/2006#;

Do not be tempted to use:

SELECT *
FROM YourTable
WHERE YourDate BETWEEN #04/01/2006#
AND #07/31/2006#;

As your data contains a non-zero time of day element this would not return
any rows on #07/31/2006# other than any with a zero time of day, i.e. at
midnight at the start of the day. There is no such thing in Access as a date
value without a time of day or vice versa. Entering a date without any time
is a value at midnight at the start of that day, entering a time without a
date is that time on 30 December 18999, which is day-zero in Access's
date/time implementation.

Ken Sheridan
Stafford, England
 
Back
Top