DatePart not returning name of month correctly

[troddy]

I am using the DatePart funtion in a query to extract the day, month
and year in separate fields in a query. The function works fine but I
am only getting a number for the month even if the field type is a long
date and the month is stored in the table. I tried the following to try
and get the name of the month to work.

DateTest: Format(DatePart ("m",[DateAffirmed]),"mmmm")

The DateAffirmed field had a date stored of 16/03/2006.

the result gave me January even though the month is number 3. I then
tried the following to test the format

MarchDate: Format(3,"mmmm" )

This also returned January and when I test all the numbers from 1 - 12
the only one that didn't come out as January was number 1 which came
out as December.

Any ideas?

[Lyle Fairfield]

Second part first:
0 is coerced to Dec 30 1899
1-> Dec 31 1899
2-> Jan 1 1900
etc

First part follows.
DatePart("m", Date) -> 3 -> Jan 2 1900
so you are asking for
Debug.Print Format(#1/3/1900#, "mmmm")
(in mm/dd/yyyy notation)
which is January

Possibly
Format(Date, "mmmm")
without extracting the month number first will work for you.

[Rick Brandt]

troddy wrote:

I am using the DatePart funtion in a query to extract the day, month
and year in separate fields in a query. The function works fine but I
am only getting a number for the month even if the field type is a
long date and the month is stored in the table. I tried the following
to try and get the name of the month to work.

DateTest: Format(DatePart ("m",[DateAffirmed]),"mmmm")

The DateAffirmed field had a date stored of 16/03/2006.

the result gave me January even though the month is number 3. I then
tried the following to test the format

MarchDate: Format(3,"mmmm" )

This also returned January and when I test all the numbers from 1 - 12
the only one that didn't come out as January was number 1 which came
out as December.

Any ideas?

Your logic is wrong. DatePart("m"... ) wil return an integer and then you are
attempting to apply a date format to that integer. In other words the output of
DatePart is not an appropriate input to the Format() function when trying to
apply date formats.

As far as the Format function is concerned you are passing in the date
represented by the number 3 which is the 2nd of January 1900. Just use...

Format([DateAffirmed],"mmmm")

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