469,922 Members | 2,157 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,922 developers. It's quick & easy.

Change DATE datatype to NUMBER datatype

How can I change a Date datatype to a Number datatype? For example, I
want a date 10/31/2006 to show 1031 as Number datatype. But I don't
want it becomes 39021. What formula should I use? Thanks for your
help!!

Nov 10 '06 #1
4 11709
On 10 Nov 2006 14:03:21 -0800, Orchid wrote:
How can I change a Date datatype to a Number datatype? For example, I
want a date 10/31/2006 to show 1031 as Number datatype. But I don't
want it becomes 39021. What formula should I use? Thanks for your
help!!
Without knowing more, my gut feeling is you are making a design
mistake.

As a number, how would you display the date of 01/24/2006?

As a Number datatype, Access does not store the leading zero, so a
date of 01/24/2006, as the number 124, would represent what?
1/24 or 12/4?

If for some reason, you don't wish to store the date as Date/Time, try
a Text datatype. As Text, Access stores leading zero's.

=Format([DateField],"mmdd") will return 0124 as text.

Now what do you intend to do with the value?
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Nov 10 '06 #2
Thanks for your reply!!
Any formula to change the date of 01/24/2006 as the number 124?
I need to format the data on MS Access then export as a Text file. Now
when I try by format as Text datatype, it works too. Just I want to
know the formula of how to change a Date as Number datatype. Again,
thanks for your help!

fredg wrote:
On 10 Nov 2006 14:03:21 -0800, Orchid wrote:
How can I change a Date datatype to a Number datatype? For example, I
want a date 10/31/2006 to show 1031 as Number datatype. But I don't
want it becomes 39021. What formula should I use? Thanks for your
help!!

Without knowing more, my gut feeling is you are making a design
mistake.

As a number, how would you display the date of 01/24/2006?

As a Number datatype, Access does not store the leading zero, so a
date of 01/24/2006, as the number 124, would represent what?
1/24 or 12/4?

If for some reason, you don't wish to store the date as Date/Time, try
a Text datatype. As Text, Access stores leading zero's.

=Format([DateField],"mmdd") will return 0124 as text.

Now what do you intend to do with the value?
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Nov 13 '06 #3
On 13 Nov 2006 07:06:17 -0800, Orchid wrote:
Thanks for your reply!!
Any formula to change the date of 01/24/2006 as the number 124?
I need to format the data on MS Access then export as a Text file. Now
when I try by format as Text datatype, it works too. Just I want to
know the formula of how to change a Date as Number datatype. Again,
thanks for your help!

fredg wrote:
>On 10 Nov 2006 14:03:21 -0800, Orchid wrote:
>>How can I change a Date datatype to a Number datatype? For example, I
want a date 10/31/2006 to show 1031 as Number datatype. But I don't
want it becomes 39021. What formula should I use? Thanks for your
help!!

Without knowing more, my gut feeling is you are making a design
mistake.

As a number, how would you display the date of 01/24/2006?

As a Number datatype, Access does not store the leading zero, so a
date of 01/24/2006, as the number 124, would represent what?
1/24 or 12/4?

If for some reason, you don't wish to store the date as Date/Time, try
a Text datatype. As Text, Access stores leading zero's.

=Format([DateField],"mmdd") will return 0124 as text.

Now what do you intend to do with the value?
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
If the Date is today's date (11/13/2006) ...
To change a Date datatype value to a number
CLng(Date())
39034

To change today's date formatted as "mmdd" to a number:
CInt(Format(Date(),"mmdd"))
1113
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Nov 13 '06 #4
Thanks so much for your help!

fredg wrote:
On 13 Nov 2006 07:06:17 -0800, Orchid wrote:
Thanks for your reply!!
Any formula to change the date of 01/24/2006 as the number 124?
I need to format the data on MS Access then export as a Text file. Now
when I try by format as Text datatype, it works too. Just I want to
know the formula of how to change a Date as Number datatype. Again,
thanks for your help!

fredg wrote:
On 10 Nov 2006 14:03:21 -0800, Orchid wrote:

How can I change a Date datatype to a Number datatype? For example, I
want a date 10/31/2006 to show 1031 as Number datatype. But I don't
want it becomes 39021. What formula should I use? Thanks for your
help!!

Without knowing more, my gut feeling is you are making a design
mistake.

As a number, how would you display the date of 01/24/2006?

As a Number datatype, Access does not store the leading zero, so a
date of 01/24/2006, as the number 124, would represent what?
1/24 or 12/4?

If for some reason, you don't wish to store the date as Date/Time, try
a Text datatype. As Text, Access stores leading zero's.

=Format([DateField],"mmdd") will return 0124 as text.

Now what do you intend to do with the value?
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

If the Date is today's date (11/13/2006) ...
To change a Date datatype value to a number
CLng(Date())
39034

To change today's date formatted as "mmdd" to a number:
CInt(Format(Date(),"mmdd"))
1113
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Nov 20 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Cristian Martinello | last post: by
4 posts views Thread by Tony | last post: by
5 posts views Thread by Macca | last post: by
7 posts views Thread by dotnetnoob | last post: by
10 posts views Thread by DontellTrevell via AccessMonster.com | last post: by
4 posts views Thread by Simon Gare | last post: by
reply views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.