Format Date in Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

When I type the following in my query:

Funding_Month: Format(DatePart("m",[Funding_Per_Begin]),"mmmm") in my query

where (For example) [Funding_Per_Begin] = 2/1/05 the result is JANUARY.

Am I doing something wrong?

Thanks
 
I'm not seeing that result, but you don't need the DatePart() bit. Just:
Funding_Month: Format([Funding_Per_Begin]),"mmmm")

You are probably aware that this calculated field is treated as text (e.g.
if you sort on this field, April comes before January.)
 
Hello,

When I type the following in my query:

Funding_Month: Format(DatePart("m",[Funding_Per_Begin]),"mmmm") in my query

where (For example) [Funding_Per_Begin] = 2/1/05 the result is JANUARY.

Am I doing something wrong?

Thanks
Click to expand...

Access stores dates as a Double number, counting the days since
12/30/1899.

The DatePart function returns the month number value of a date field,
which can only be 1 through 12.
So DatePart(2/1/2005) returns a value of 2 (February).

Formatting 2 through 12 as "mmmm" will always return January because
Access expects that the date in Format([DateField],"mmmm") will be
an actual full date. So a date value of 2 through 12 is going to be
Jan 1, 1900, Jan 2, 1900 etc...... Jan 12, 1900 (the number of days
from 12/30. 1899), all in the month of January. (A date of 1 will be
12/31/1899, which will return the Month of December).

All you need to get the name of the month dfrom a Date field is:

Format([Funding_Per_Begin],"mmmm")

No DatePart() needed.
 
Back
Top