Format a date field in query result

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dylan Moran - 25 May 2005 13:24 GMT

Hi,

I want to format the output of a query that selects a date column so that it
outputs for example as 15 March 2005.

That is, in a query where I SELECT customer_name and date_ordered:

It currently outputs the data as follows

JG Enterprises 28/9/2005
ACME Inc 15/7/2004

However I want it to output as

JG Enterprises 28 September 2005
ACME Inc 15 July 2004

Is there somehing I can do in the QBE Grid or in sql view that can help me?
Assistance greatly appreciated.

Signature

I may not know VBA inside out, but from the outside I am looking in.
Dylan Moran - Melbourne Australia

Reply to this Message

Rick Brandt - 25 May 2005 13:35 GMT

> Hi,
>
[quoted text clipped - 15 lines]
> Is there somehing I can do in the QBE Grid or in sql view that can
> help me? Assistance greatly appreciated.

Format([date_ordered], "dd, mmmm yyyy")

The output from the above will actually be text rather than a date so it will
sort and react to comparisons as a string. You can also set the format property
of the column (and still get a date for output), but that setting will not be
inherited if you export the query or use it in a form/report.

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message

Van T. Dinh - 25 May 2005 13:41 GMT

Tiny adjustment per original question:

Format([date_ordered], "dd mmmm yyyy")

Signature

HTH
Van T. Dinh
MVP (Access)

> Format([date_ordered], "dd, mmmm yyyy")
>
> The output from the above will actually be text rather than a date so it will
> sort and react to comparisons as a string. You can also set the format property
> of the column (and still get a date for output), but that setting will not be
> inherited if you export the query or use it in a form/report.

Reply to this Message

Dylan Moran - 26 May 2005 01:08 GMT

Excellent!!! Rick and Van, Thanks very much for your responses.

> Tiny adjustment per original question:
>
[quoted text clipped - 9 lines]
> be
> > inherited if you export the query or use it in a form/report.

Reply to this Message