Months as 2 digits rather than 1

  • Thread starter Thread starter Catcher via AccessMonster.com
  • Start date Start date
C

Catcher via AccessMonster.com

I am using an expression in a query field to retrieve the date part month as
follows:
Months: DatePart("M",[TouchDate],1,0)
This returns 1 - 12 but of course it sorts 1 11 12 2 3 4 5, etc.
Is there a simple solution to have the value return two digits?
01 02 03 04 05 06 07 08 09 10 11 12

I'm betting this is simple but I can't seem to ask "Help" the right way...
 
Not surprised you can't see how to do it. I've just looked up the Format
function in Access 2000 Index and all I can see are a load of Excel
Worksheet functions (huh??) rather than clear instructions on this handy
feature. Thank goodness I've kept my Access 97.
Bet Bill wants to sell us the Help file in a big expensive book.

Use
Format(DatePart("M",[TouchDate],1,0),"00")

but be aware that it's now a string and thus for show only so if you want to
do any arithmetic, refer back to your original formula

Evi
 
Thanks, Evi. Simple as I suspected. It works fine for my purpose, column
headers on a crosstab allowing for a Year / Month (2008 02) heading to be
dynamic and in order.
Not surprised you can't see how to do it. I've just looked up the Format
function in Access 2000 Index and all I can see are a load of Excel
Worksheet functions (huh??) rather than clear instructions on this handy
feature. Thank goodness I've kept my Access 97.
Bet Bill wants to sell us the Help file in a big expensive book.

Use
Format(DatePart("M",[TouchDate],1,0),"00")

but be aware that it's now a string and thus for show only so if you want to
do any arithmetic, refer back to your original formula

Evi
I am using an expression in a query field to retrieve the date part month as
follows:
Click to expand...
[quoted text clipped - 7 lines]
Click to expand...
 
I am using an expression in a query field to retrieve the date part month as
follows:
Months: DatePart("M",[TouchDate],1,0)
This returns 1 - 12 but of course it sorts 1 11 12 2 3 4 5, etc.
Is there a simple solution to have the value return two digits?
01 02 03 04 05 06 07 08 09 10 11 12

I'm betting this is simple but I can't seem to ask "Help" the right way...
Click to expand...

Hi
I created a function add it to a module then you can use it where ever
you want however it converts a date to a string
you can put the date back in what format you want
dd/mm//yyyy or dd-mm-yy etc.

Public Function eslDate(dt As Date) As String
'DATESUR: Day([Admin]![DATESUR]) & "/" & Month([Admin]![DATESUR]) &
"/" & Year([Admin]![DATESUR])
Dim ndy, nmo As String
ndy = Day(dt)
nmo = Month(dt)
If ndy < "10" Then
ndy = "0" & ndy
End If
If nmo < "10" Then
nmo = "0" & nmo
End If
eslDate = ndy & "/" & nmo & "/" & YEAR(dt)
End Function
 
Catcher via AccessMonster.com said:
I am using an expression in a query field to retrieve the date
part month as follows:
Months: DatePart("M",[TouchDate],1,0)
This returns 1 - 12 but of course it sorts 1 11 12 2 3 4 5, etc.
Is there a simple solution to have the value return two digits?
01 02 03 04 05 06 07 08 09 10 11 12

I'm betting this is simple but I can't seem to ask "Help" the right
Click to expand...
way...

In Access, numeric values do not return lead zeros. To get the leading
zeros (what you are calling "two digits") there would have to be a
conversion to text, as with a Format statement or property, or a CStr
function. And, from your description of the sort order, which is
appropriate for soring numeric characters in text, it seems you are already
performing such a conversion that you haven't told us about.

According to Help on DatePart, it returns a variant of type integer, and in
a quick, simple test where I used DatePart in a Query to extract the month
from dates randomly-ordered in a table, then set Sort to Ascending on that
Calculated Field, the months properly sorted in numeric order. The sort
order you cite is most certainly not "of course".

It _is_ quite simple... it is the default way that Access works, unless you
force it to do otherwise.

Larry Linson
Microsoft Office Access MVP
 
Back
Top