By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,425 Members | 2,549 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,425 IT Pros & Developers. It's quick & easy.

Left, Right Mid?

P: n/a
I have been trying to extract the digits between the two / symbols in a
date. For example the date (in Australian format, not US) is 23/12/2004. I
want to be able to extract the 12 (Month) from that. I have tried various
left, right and mid functions, but I only seem to be able to get the 23, the
04 or the full date. What is the correct function to use?

dixie
Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
"dixie" <di***@dogmail.com> wrote in message
news:41********@duster.adelaide.on.net...
I have been trying to extract the digits between the two / symbols in a
date. For example the date (in Australian format, not US) is 23/12/2004. I want to be able to extract the 12 (Month) from that. I have tried various
left, right and mid functions, but I only seem to be able to get the 23, the 04 or the full date. What is the correct function to use?

dixie


Dixie, if the date is stored as a Date field, you can use the DatePart
function to extract the month.

Dim d As Date
d = "12/22/2004" ' Americanized form
Debug.Print DatePart("m", d)

prints:
12

HTH,
Randy
Nov 13 '05 #2

P: n/a
On Thu, 23 Dec 2004 07:12:20 +1100, dixie wrote:
I have been trying to extract the digits between the two / symbols in a
date. For example the date (in Australian format, not US) is 23/12/2004. I
want to be able to extract the 12 (Month) from that. I have tried various
left, right and mid functions, but I only seem to be able to get the 23, the
04 or the full date. What is the correct function to use?

dixie


If the field is a valid date datatype (not text datatype):
=Month([DateField])
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #3

P: n/a
?month(#23/12/2004#)
12

?day(#23/12/2004#)
23

?year(#23/12/2004#)
2004

You must put the Hash# sign on anything to do with dates
Phil
"dixie" <di***@dogmail.com> wrote in message
news:41********@duster.adelaide.on.net...
I have been trying to extract the digits between the two / symbols in a
date. For example the date (in Australian format, not US) is 23/12/2004.
I want to be able to extract the 12 (Month) from that. I have tried
various left, right and mid functions, but I only seem to be able to get
the 23, the 04 or the full date. What is the correct function to use?

dixie

Nov 13 '05 #4

P: n/a
Of course, ?month(#11/12/2004#) is going to return 11, regardless of what
the short date format is set to!

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Phil Stanton" <di********@stantonfamily.co.uk> wrote in message
news:41*********************@mercury.nildram.net.. .
?month(#23/12/2004#)
12

?day(#23/12/2004#)
23

?year(#23/12/2004#)
2004

You must put the Hash# sign on anything to do with dates
Phil
"dixie" <di***@dogmail.com> wrote in message
news:41********@duster.adelaide.on.net...
I have been trying to extract the digits between the two / symbols in a
date. For example the date (in Australian format, not US) is 23/12/2004.
I want to be able to extract the 12 (Month) from that. I have tried
various left, right and mid functions, but I only seem to be able to get
the 23, the 04 or the full date. What is the correct function to use?

dixie


Nov 13 '05 #5

P: n/a
It is. Is there an way I can make it return 01, 02, etc for the months
previous to October?

"fredg" <fg******@example.invalid> wrote in message
news:dl*****************************@40tude.net...
On Thu, 23 Dec 2004 07:12:20 +1100, dixie wrote:
I have been trying to extract the digits between the two / symbols in a
date. For example the date (in Australian format, not US) is 23/12/2004.
I
want to be able to extract the 12 (Month) from that. I have tried
various
left, right and mid functions, but I only seem to be able to get the 23,
the
04 or the full date. What is the correct function to use?

dixie


If the field is a valid date datatype (not text datatype):
=Month([DateField])
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Nov 13 '05 #6

P: n/a
I meant there that it returns a 1 digit number and I would like a 2 digit
number.

dixie

"dixie" <di***@dogmail.com> wrote in message
news:41******@duster.adelaide.on.net...
It is. Is there an way I can make it return 01, 02, etc for the months
previous to October?

"fredg" <fg******@example.invalid> wrote in message
news:dl*****************************@40tude.net...
On Thu, 23 Dec 2004 07:12:20 +1100, dixie wrote:
I have been trying to extract the digits between the two / symbols in a
date. For example the date (in Australian format, not US) is
23/12/2004. I
want to be able to extract the 12 (Month) from that. I have tried
various
left, right and mid functions, but I only seem to be able to get the 23,
the
04 or the full date. What is the correct function to use?

dixie


If the field is a valid date datatype (not text datatype):
=Month([DateField])
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.


Nov 13 '05 #7

P: n/a
On Thu, 23 Dec 2004 09:02:16 +1100, dixie wrote:
I meant there that it returns a 1 digit number and I would like a 2 digit
number.

dixie

"dixie" <di***@dogmail.com> wrote in message
news:41******@duster.adelaide.on.net...
It is. Is there an way I can make it return 01, 02, etc for the months
previous to October?

"fredg" <fg******@example.invalid> wrote in message
news:dl*****************************@40tude.net...
On Thu, 23 Dec 2004 07:12:20 +1100, dixie wrote:

I have been trying to extract the digits between the two / symbols in a
date. For example the date (in Australian format, not US) is
23/12/2004. I
want to be able to extract the 12 (Month) from that. I have tried
various
left, right and mid functions, but I only seem to be able to get the 23,
the
04 or the full date. What is the correct function to use?

dixie

If the field is a valid date datatype (not text datatype):
=Month([DateField])
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.



=Format(Month([DateField]),"00")
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #8

P: n/a
That fixed it. Tah.

dixie

"fredg" <fg******@example.invalid> wrote in message
news:1x*******************************@40tude.net. ..
On Thu, 23 Dec 2004 09:02:16 +1100, dixie wrote:
I meant there that it returns a 1 digit number and I would like a 2 digit
number.

dixie

"dixie" <di***@dogmail.com> wrote in message
news:41******@duster.adelaide.on.net...
It is. Is there an way I can make it return 01, 02, etc for the months
previous to October?

"fredg" <fg******@example.invalid> wrote in message
news:dl*****************************@40tude.net...
On Thu, 23 Dec 2004 07:12:20 +1100, dixie wrote:

> I have been trying to extract the digits between the two / symbols in
> a
> date. For example the date (in Australian format, not US) is
> 23/12/2004. I
> want to be able to extract the 12 (Month) from that. I have tried
> various
> left, right and mid functions, but I only seem to be able to get the
> 23,
> the
> 04 or the full date. What is the correct function to use?
>
> dixie

If the field is a valid date datatype (not text datatype):
=Month([DateField])
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.


=Format(Month([DateField]),"00")
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Nov 13 '05 #9

P: n/a
On Thu, 23 Dec 2004 09:02:16 +1100, dixie wrote:
I meant there that it returns a 1 digit number and I would like a 2 digit
number.

right("00" & month([datefield]) , 2)

Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.