Date problem 2

[medic133]

I have a query which returns a date in the format of mm/dd/yyy. I would like to return the name of the month associated with this returned value. In other words, if the returned value is 01/13/2004, I would like another field to return "January". I've been able to create a field with the expression Format((datefieldname),"mm&quot with the proper returned values, but from here, I'm stuck. I need this for a field in a group header on a report so I can group information according to month rather than each day. Any suggestions? Thanks in advance!!

 

[BNPMike]

I'm not saying this is the best way but one option is to get the month number - Month(yourdatefield) - and then join that to a table Months like:

1 January
2 February
3 March

etc

 

[MakeItSo]

You don't need an extra lookup table:

Format(Month([datefield]),"mmmm&quot should provide the same.

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[BNPMike]

Andreas

Didn't spot that. You just declare another field set to the date field and then put mmmm in the format box of the field property window. That tip is worth a star.

 

[MakeItSo]

Thanks BNPMike. ;-)

 

[medic133]

Thank you all! The formatting works exactly how I needed it to. This is a helpful reminder of the custom formatting that is available in Access. Thanks again!

 

[kmkland]

MakeItSo,
I used this query, but I get "January" for all my dates, despite the month!
Any thoughts??
Kim

 

[MakeItSo]

Hi kmkland,

sorry, but I think I don't understand.
What do you mean by "all dates despite the month"?
What does your datefield look like (string, short date, long date)?
What's your respective SQL string?

Cheerio,
Andy

[blue]An eye for an eye only leaves the whole world blind. - "Mahatma" Mohandas K. Gandhi[/blue]

 

[PaulBricker]

The expression should look like this
Format([datefield],"mmmm")

Adding the Month() function inside the format function causes an error.


Paul

 

[Golom]

The format statement should be

Format([DateField], "MMMM")

You are getting January because Format assumes that its first argument must be a date since the format is a date-related format. It helps you out by converting "Month([DateField]) to a Date. Unfortunately

Cdate(1) = 12/31/1899
cDate(2) = 01/01/1900
cDate(3) = 01/02/1900
:
cDate(12) = 01/12/1900

So For A January date

Format ( Month([DateField]), "MMMM" ) = "December"

and for any other month

Format ( Month([DateField]), "MMMM" ) = "January"

 

[kmkland]

Paul...Golom,
Brilliant!!
Thank you!!!!!
Kim

 

[PaulBricker]

Nice explaination Golom. Have a star.

Paul