Formatting a Month to say December, etc.

[Guest]

This is the formula I am using in a query to extract just the month from a
date field. =Datepart("m",[DateReceived]). The result I receive is 1, 2, 3
etc. Works fine until I format the column in the query to mmm. Then it
returns Dec, and Jan; not January, February, and March.

What am I doing wrong?

 

[Gina Whipp]

Add an m to your mmm to make it mmmm

HTH

 

[Guest]

I tried that - it still doesn't work.

This is my data

I have the Date Received field formatted for Short Date

Service ID Date Received Type Of Service Expr1
1 1 /1 /2005 Warrant 1
1 1 /1 /2005 Warrant 1
2 2 /1 /2005 Warrant 2
3 3 /1 /2005 Ticket 3

The result comes back as this:

Service ID Date Received Type Of Service Expr1
1 1 /1 /2005 Warrant December
1 1 /1 /2005 Warrant December
2 2 /1 /2005 Warrant January
3 3 /1 /2005 Ticket January

Any ideas?


--
Deb H.

Add an m to your mmm to make it mmmm

HTH

This is the formula I am using in a query to extract just the month from a
date field. =Datepart("m",[DateReceived]). The result I receive is 1, 2,
3
etc. Works fine until I format the column in the query to mmm. Then it
returns Dec, and Jan; not January, February, and March.

What am I doing wrong?

 

[Gina Whipp]

Sorry I thought you wanted the Month to be spelled out? Not sure I
understand what you want...

I tried that - it still doesn't work.

This is my data

I have the Date Received field formatted for Short Date

Service ID Date Received Type Of Service Expr1
1 1 /1 /2005 Warrant 1
1 1 /1 /2005 Warrant 1
2 2 /1 /2005 Warrant 2
3 3 /1 /2005 Ticket 3

The result comes back as this:

Service ID Date Received Type Of Service Expr1
1 1 /1 /2005 Warrant December
1 1 /1 /2005 Warrant December
2 2 /1 /2005 Warrant January
3 3 /1 /2005 Ticket January

Any ideas?


--
Deb H.

Add an m to your mmm to make it mmmm

HTH

This is the formula I am using in a query to extract just the month
from a
date field. =Datepart("m",[DateReceived]). The result I receive is 1,
2,
3
etc. Works fine until I format the column in the query to mmm. Then
it
returns Dec, and Jan; not January, February, and March.

What am I doing wrong?

 

[Guest]

I do want the month spelled out, but I only have dates for January, February
and March. The result displays December and January, not the correct month.
--
Deb H.

Sorry I thought you wanted the Month to be spelled out? Not sure I
understand what you want...

I tried that - it still doesn't work.

This is my data

I have the Date Received field formatted for Short Date

Service ID Date Received Type Of Service Expr1
1 1 /1 /2005 Warrant 1
1 1 /1 /2005 Warrant 1
2 2 /1 /2005 Warrant 2
3 3 /1 /2005 Ticket 3

The result comes back as this:

Service ID Date Received Type Of Service Expr1
1 1 /1 /2005 Warrant December
1 1 /1 /2005 Warrant December
2 2 /1 /2005 Warrant January
3 3 /1 /2005 Ticket January

Any ideas?


--
Deb H.

Add an m to your mmm to make it mmmm

HTH

This is the formula I am using in a query to extract just the month
from a
date field. =Datepart("m",[DateReceived]). The result I receive is 1,
2,
3
etc. Works fine until I format the column in the query to mmm. Then
it
returns Dec, and Jan; not January, February, and March.

What am I doing wrong?

 

[Graham Mandeno]

Hi Deb

Formats such as "mmmm" are intended to work on dates, not numbers.

So, Format("06/06/2006", "mmmm") translates the string ("06/06/2006") into a
date (6 June 2006) and returns the full name of the month ("June").

If you ask for a number (e.g. 1) to be formatted as a date, Access must
first try to interpret the number as a date. The way it does this is to see
the number as a number of days after an arbitrary "zero date", which happens
to be 30-Dec-1899.

So, 1 is 31-Dec-1899
and 2 is 1-Jan-1900
etc

That is why 1 is giving you "December" and every other number up to 12 is
giving you "January".

The solution is simple: Get rid of the Format property on that column, and
instead of DatePart, use the expression:
Format("mmmm", [DateReceived])

 

[Guest]

It doesn't work. It returns four M's in the column. Any other ideas?
--
Deb H.

Hi Deb

Formats such as "mmmm" are intended to work on dates, not numbers.

So, Format("06/06/2006", "mmmm") translates the string ("06/06/2006") into a
date (6 June 2006) and returns the full name of the month ("June").

If you ask for a number (e.g. 1) to be formatted as a date, Access must
first try to interpret the number as a date. The way it does this is to see
the number as a number of days after an arbitrary "zero date", which happens
to be 30-Dec-1899.

So, 1 is 31-Dec-1899
and 2 is 1-Jan-1900
etc

That is why 1 is giving you "December" and every other number up to 12 is
giving you "January".

The solution is simple: Get rid of the Format property on that column, and
instead of DatePart, use the expression:
Format("mmmm", [DateReceived])

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

This is the formula I am using in a query to extract just the month from a
date field. =Datepart("m",[DateReceived]). The result I receive is 1, 2,
3
etc. Works fine until I format the column in the query to mmm. Then it
returns Dec, and Jan; not January, February, and March.

What am I doing wrong?

 

[Graham Mandeno]

Doh! Sorry - I had a hard night last night :-/

What I meant was:
Format([DateReceived], "mmmm" )
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

It doesn't work. It returns four M's in the column. Any other ideas?
--
Deb H.

Hi Deb

Formats such as "mmmm" are intended to work on dates, not numbers.

So, Format("06/06/2006", "mmmm") translates the string ("06/06/2006")
into a
date (6 June 2006) and returns the full name of the month ("June").

If you ask for a number (e.g. 1) to be formatted as a date, Access must
first try to interpret the number as a date. The way it does this is to
see
the number as a number of days after an arbitrary "zero date", which
happens
to be 30-Dec-1899.

So, 1 is 31-Dec-1899
and 2 is 1-Jan-1900
etc

That is why 1 is giving you "December" and every other number up to 12 is
giving you "January".

The solution is simple: Get rid of the Format property on that column,
and
instead of DatePart, use the expression:
Format("mmmm", [DateReceived])

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

This is the formula I am using in a query to extract just the month
from a
date field. =Datepart("m",[DateReceived]). The result I receive is 1,
2,
3
etc. Works fine until I format the column in the query to mmm. Then
it
returns Dec, and Jan; not January, February, and March.

What am I doing wrong?

 

[Albert D.Kallal]

Sorry I thought you wanted the Month to be spelled out? Not sure I
understand what you want...

Your problem is that datepart never did support the use of "mmmm", or "mmm".

You are confused!!...it is format you need!

datepart ONLY supprots the use of "m"...not "mmm" etc....

you need to use in the query builder:

MyMonth: format("mmmm",[dateField])

 

[Albert D.Kallal]

See my other post.....

datepart never did work to produce a text month....

You need in the query builder to use:

MyMonth: format("mmmm",[YourDateFieldGoesHere])

 

[Graham Mandeno]

MyMonth: format("mmmm",[YourDateFieldGoesHere])

Et tu, Albert? What were *you* up to last night?? <vbg>

There seem to be a number of cases of MAD (MVP Argument Dyslexia) going
around today ;-)

 

[Van T. Dinh]

Yep, I have made a few errors like that lately. Must be a good batch of
wine the vineyards release this year ...

--
Cheers
Van T. Dinh
MVP (Access)

MyMonth: format("mmmm",[YourDateFieldGoesHere])

Et tu, Albert? What were *you* up to last night?? <vbg>

There seem to be a number of cases of MAD (MVP Argument Dyslexia) going
around today ;-)

 

[Guest]

Yes!!! It worked! Thank you very much!!!!
--
Deb H.

Doh! Sorry - I had a hard night last night :-/

What I meant was:
Format([DateReceived], "mmmm" )
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

It doesn't work. It returns four M's in the column. Any other ideas?
--
Deb H.

Hi Deb

Formats such as "mmmm" are intended to work on dates, not numbers.

So, Format("06/06/2006", "mmmm") translates the string ("06/06/2006")
into a
date (6 June 2006) and returns the full name of the month ("June").

If you ask for a number (e.g. 1) to be formatted as a date, Access must
first try to interpret the number as a date. The way it does this is to
see
the number as a number of days after an arbitrary "zero date", which
happens
to be 30-Dec-1899.

So, 1 is 31-Dec-1899
and 2 is 1-Jan-1900
etc

That is why 1 is giving you "December" and every other number up to 12 is
giving you "January".

The solution is simple: Get rid of the Format property on that column,
and
instead of DatePart, use the expression:
Format("mmmm", [DateReceived])

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


This is the formula I am using in a query to extract just the month
from a
date field. =Datepart("m",[DateReceived]). The result I receive is 1,
2,
3
etc. Works fine until I format the column in the query to mmm. Then
it
returns Dec, and Jan; not January, February, and March.

What am I doing wrong?