Convert Number Field to Date in Table

#1 (**permalink**) 28-Jul-2006, 08:29 AM

Another Department created an Access Database. Rather than using a Date
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12449-convert-number-field-to-date-table.html
field in their table to hold date information for records, they set the data
type of the field to number. They have over a 1000 records in the table
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12449
already and now want to run queries against the data based on dates--which
are stored as numbers. Is there an easy way to convert this number field to
a date field without losing data. I tried just changing the data type on a
copy of the table, but then the date information gets fouled up. Any help is
appreciated.

*

#2 (**permalink**) 28-Jul-2006, 08:29 AM

Janna,

This will probably be possible, but can't advise specifically without
knowing the numbering system being used. Can you give some example of
the numbers that are in the table, and the dates they represent?

--
Steve Schapel, Microsoft Access MVP

Janna wrote:
> Another Department created an Access Database. Rather than using a Date
> field in their table to hold date information for records, they set the data
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12449
> type of the field to number. They have over a 1000 records in the table
> already and now want to run queries against the data based on dates--which
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12449
> are stored as numbers. Is there an easy way to convert this number field to
> a date field without losing data. I tried just changing the data type on a
> copy of the table, but then the date information gets fouled up. Any help is
> appreciated.

#3 (**permalink**) 28-Jul-2006, 08:29 AM

What do you mean by number?

Store
1/1/2006
As
112006

Or, something else?

--
Good Luck
BS"D

"Janna" wrote:

> Another Department created an Access Database. Rather than using a Date
> field in their table to hold date information for records, they set the data
> type of the field to number. They have over a 1000 records in the table
> already and now want to run queries against the data based on dates--which
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12449
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12449
> are stored as numbers. Is there an easy way to convert this number field to
> a date field without losing data. I tried just changing the data type on a
> copy of the table, but then the date information gets fouled up. Any help is
> appreciated.

#4 (**permalink**) 28-Jul-2006, 08:29 AM

Example: June 26, 2006 is currently being stored in a number data type field
as 62606

"Steve Schapel" wrote:

> Janna,
>
> This will probably be possible, but can't advise specifically without
> knowing the numbering system being used. Can you give some example of
> the numbers that are in the table, and the dates they represent?
>
> --
> Steve Schapel, Microsoft Access MVP
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12449
>
> Janna wrote:
> > Another Department created an Access Database. Rather than using a Date
> > field in their table to hold date information for records, they set the data
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12449
> > type of the field to number. They have over a 1000 records in the table
> > already and now want to run queries against the data based on dates--which
> > are stored as numbers. Is there an easy way to convert this number field to
> > a date field without losing data. I tried just changing the data type on a
> > copy of the table, but then the date information gets fouled up. Any help is
> > appreciated.
>

#5 (**permalink**) 28-Jul-2006, 08:29 AM

On Sun, 2 Jul 2006 14

01 -0700, Janna
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12449
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12449
wrote:

>Example: June 26, 2006 is currently being stored in a number data type field
>as 62606

Assuming that July 1 is stored as 70106 (rather than... eep... as
7106!) try

CDate(Format([numberdate], "#/00/00"))

John W. Vinson[MVP]