Format Function - Access 2000

[paulmitchell507]

Dear All,
I am running a simple query on a table called [Opportunity] that has a
field called [ClosedDate] containing dates with a data type of date
\time.

If I sort the [ClosedDate] field using any of the named formats i.e
short date, long date etc. the dates are sorted as your would expect.
However, I would like to use a custom format of "mm/yy".

So the query looks as follows,

Field: ClosedDate: Format([Opportunity]![CloseDate],"mm/yy")
Table:
Sort: Descending

Which outputs the dates as 03/08, 02/09 etc. I assume that the format
function is returning the data as a string, so when I sort the dates
03/08 is above 02/09. I would like use my custom format but keep
original data type of date\time. Can anybody offer any guidance?

 

[Albert D. Kallal]

Just add back in the original date column, and sort on that in the query (or
repot but use your custom field for the display purposes....

And, the same goes for a report, use the [closedDate] for your sort, but use
a text box with your custom format (or perhaps as you done, use the
additional column you defined in the query for display).

 

[Brendan Reynolds]

Dear All,
I am running a simple query on a table called [Opportunity] that has a
field called [ClosedDate] containing dates with a data type of date
\time.

If I sort the [ClosedDate] field using any of the named formats i.e
short date, long date etc. the dates are sorted as your would expect.
However, I would like to use a custom format of "mm/yy".

So the query looks as follows,

Field: ClosedDate: Format([Opportunity]![CloseDate],"mm/yy")
Table:
Sort: Descending

Which outputs the dates as 03/08, 02/09 etc. I assume that the format
function is returning the data as a string, so when I sort the dates
03/08 is above 02/09. I would like use my custom format but keep
original data type of date\time. Can anybody offer any guidance?

How about adding the original date/time field to the query, unchecking the
"Show" check box so that it does not appear in the result, and sorting by
that column while continuing to display the other column with your custom
format? This should sort the records in the correct date order, and may also
be more efficient, as the query will then be able to make use of any index
that might exist on the date/time field.