MonthName

[LCalaway]

I would like to substitute a month's name for its numeric equivalent in a
query. The date field ([ArrivalDate]) contains dates like 3/4/1855 and
12/31/1843. I want my results to look like: March 4, 1855 or December 31,
1843.

Is it possible to do this in a simple Access query, not VBA? I played with
the MonthName Function, and couldn't figure out how to use it.

Thank you.
LCalaway

 

[Allen Browne]

In query design view, right-click the date field, and choose Properties.
Set the Format property to:
Long Date

You can use the Format() function, e.g.:
Format([Arrival Date], "Long Date")
But I don't recommend this approach. Format() generates text, so the field
does not sort properly any more, and critiera don't work correctly.

 

[LCalaway]

I tried that, but I don't want the day of the week to display.
lc

In query design view, right-click the date field, and choose Properties.
Set the Format property to:
Long Date

You can use the Format() function, e.g.:
Format([Arrival Date], "Long Date")
But I don't recommend this approach. Format() generates text, so the field
does not sort properly any more, and critiera don't work correctly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I would like to substitute a month's name for its numeric equivalent in a
query. The date field ([ArrivalDate]) contains dates like 3/4/1855 and
12/31/1843. I want my results to look like: March 4, 1855 or December
31, 1843.

Is it possible to do this in a simple Access query, not VBA? I played
with the MonthName Function, and couldn't figure out how to use it.

Thank you.
LCalaway

 

[Allen Browne]

You can use any format you wish, such as:
mmmm d\, yyyy

Or you could edit the definition of Long Date in the Windows Control Panel,
under Regional Options.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I tried that, but I don't want the day of the week to display.
lc

In query design view, right-click the date field, and choose Properties.
Set the Format property to:
Long Date

You can use the Format() function, e.g.:
Format([Arrival Date], "Long Date")
But I don't recommend this approach. Format() generates text, so the
field does not sort properly any more, and critiera don't work correctly.

I would like to substitute a month's name for its numeric equivalent in a
query. The date field ([ArrivalDate]) contains dates like 3/4/1855 and
12/31/1843. I want my results to look like: March 4, 1855 or December
31, 1843.

Is it possible to do this in a simple Access query, not VBA? I played
with the MonthName Function, and couldn't figure out how to use it.

 

[LCalaway]

Thank you. I'll try that.
lc

You can use any format you wish, such as:
mmmm d\, yyyy

Or you could edit the definition of Long Date in the Windows Control
Panel, under Regional Options.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I tried that, but I don't want the day of the week to display.
lc

In query design view, right-click the date field, and choose Properties.
Set the Format property to:
Long Date

You can use the Format() function, e.g.:
Format([Arrival Date], "Long Date")
But I don't recommend this approach. Format() generates text, so the
field does not sort properly any more, and critiera don't work
correctly.

I would like to substitute a month's name for its numeric equivalent in
a query. The date field ([ArrivalDate]) contains dates like 3/4/1855
and 12/31/1843. I want my results to look like: March 4, 1855 or
December 31, 1843.

Is it possible to do this in a simple Access query, not VBA? I played
with the MonthName Function, and couldn't figure out how to use it.

 

[David F Cox]

Allen is right about the conversion to text, but you can add the date field
again to the query design window and do your criteria and sorting on that
u7nformatted column, but not display it.

In query design view, right-click the date field, and choose Properties.
Set the Format property to:
Long Date

You can use the Format() function, e.g.:
Format([Arrival Date], "Long Date")
But I don't recommend this approach. Format() generates text, so the field
does not sort properly any more, and critiera don't work correctly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I would like to substitute a month's name for its numeric equivalent in a
query. The date field ([ArrivalDate]) contains dates like 3/4/1855 and
12/31/1843. I want my results to look like: March 4, 1855 or December
31, 1843.

Is it possible to do this in a simple Access query, not VBA? I played
with the MonthName Function, and couldn't figure out how to use it.

Thank you.
LCalaway

 

[David F Cox]

in my tests I actually used:-

xx: Format([mydate],"mmmm dd yyyy")

You can use any format you wish, such as:
mmmm d\, yyyy

Or you could edit the definition of Long Date in the Windows Control
Panel, under Regional Options.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I tried that, but I don't want the day of the week to display.
lc

In query design view, right-click the date field, and choose Properties.
Set the Format property to:
Long Date

You can use the Format() function, e.g.:
Format([Arrival Date], "Long Date")
But I don't recommend this approach. Format() generates text, so the
field does not sort properly any more, and critiera don't work
correctly.

I would like to substitute a month's name for its numeric equivalent in
a query. The date field ([ArrivalDate]) contains dates like 3/4/1855
and 12/31/1843. I want my results to look like: March 4, 1855 or
December 31, 1843.

Is it possible to do this in a simple Access query, not VBA? I played
with the MonthName Function, and couldn't figure out how to use it.