Short Date format conversion to Month name and two digit year

[Guest]

Is there a way to create a expression in a query to convert a date in short
date format to the abbreviated month name combined with the two digit year?

For example 17/01/06 to Jan-06

 

[Rick Brandt]

Is there a way to create a expression in a query to convert a date in
short date format to the abbreviated month name combined with the two
digit year?

For example 17/01/06 to Jan-06

MonthYear: Format(DateField,"mmm yy")

Or just set the format property of the column to "mmm yy".

The difference is that the Format() function is actually converting the
output to a string with the format desired. It will no longer be a date so
sorting, criteria etc., will follow string rules and behavior not those for
dates. If you were to later use this query in other queries, forms, and
reports you will still see the mmm yy format.

If you just set the format property you will still have a real date in your
output. Sorting and criteria rules will still be those for dates, rather
that strings. However; if you feed the output of the query into another
query, form, or report the formatting will likely not cary over and you will
again get the default display format for a date.

 

[John Spencer]

Assumption:
Your date field is a field of type Date

Use the format function.
Format([Your Date Field],"mmm\-yy")

If your date field is a text field then try
Format(CDate([Your Date Field]),"mmm\-yy")
Watch out for problems with this as it may not work correctly with dates
that are not in US format of mm/dd/yy.

I can't test, I am on of those US-oriented North Americans and my date
settings are correct. ;-)

 

[John Vinson]

Is there a way to create a expression in a query to convert a date in short
date format to the abbreviated month name combined with the two digit year?

For example 17/01/06 to Jan-06

The date format is NOT stored as part of the date. A Date/Time field
is actually stored as a number, a count of days and fractions of a day
since midnight, December 30, 1899. Your date is stored as 38734.00000.

You can use a Format expression to DISPLAY the date any way you wish;
setting the format of a form or report textbox to "mmm-yy" will
display it as Jan-06. Or, you can use the Format() function to convert
the date to a text string (for export for instance):

ExpDate: Format([datefield], "mmm-yy")

John W. Vinson[MVP]