DatePart not returning name of month correctly

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

troddy - 17 Mar 2006 13:22 GMT

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?

Reply to this Message

Lyle Fairfield - 17 Mar 2006 13:41 GMT

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.

Reply to this Message

Rick Brandt - 17 Mar 2006 13:41 GMT

> 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
[quoted text clipped - 16 lines]
>
> 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")

Signature

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

Reply to this Message