Re: Format Function - Access 2000

Don't use the Format() function in a query.
Its output is always interpreted as Text, so the sort will be nonsense.

Instead, leave the field as a date, and use the Format property of the field (in the properties box.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"paulmitchell507" <paulmitchell507@xxxxxxxxxxxxxx> wrote in message
news:6332c8b8-4575-4f16-b826-23be339a509b@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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?

.

Relevant Pages

  • Re: Time Field: Sorts incorrectly
    ... Are you using the Format() ... > Once I sort these times in a query or report they sort as: ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Completely baffled on what should be simple
    ... the same thing I previously did, changing format to custom ... sort, it didn't work. ... if a cell contains the text ...
    (microsoft.public.excel.misc)
  • Re: Make Table Query-Issue with Date
    ... to instructions contained in a format expression." ... > When I run the query and it creates the table the Data Type is set to Text ... > selected and then opens charts based on the Make Table but since the Data ...
    (microsoft.public.access.queries)
  • Re: Completely baffled on what should be simple
    ... And remember just changing the format of the cell won't change the underlying ... sort, it didn't work. ... for the extensions that have either one or two 0's as the first numbers, ...
    (microsoft.public.excel.misc)
  • Re: Date Format problem in parameter query
    ... it sounds like Access is confused about the data type of the field. ... Are you applying this on a calculated field? ... In query design view, choose Parameters on the Query menu. ... > its Format property...nothing fancy ...
    (microsoft.public.access.queries)