Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Convert Number Field to Date in Table

Discussion in 'Information Technology' started by Janna, Jul 28, 2006.

  1. Janna

    Janna
    Expand Collapse
    Guest

    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
    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. Loading...

    Similar Threads Forum Date
    Sikhconvert Joins Sikh Philosophy Network! New SPN'ers Aug 28, 2016
    Jehovah's Witnesses Are Out And Converting! Blogs Jul 31, 2016
    Islam Ishna Has Converted To Islam Interfaith Dialogues Apr 1, 2016
    A Christian Missionary tried to convert me. Blogs Oct 22, 2015
    "Convert's Corner" - Ideas for Retitle? Convert's Corner Oct 21, 2015

  3. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    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
    > 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.
     
  4. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    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
    > 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. Janna

    Janna
    Expand Collapse
    Guest

    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
    >
    > 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
    > > 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.

    >
     
  6. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Sun, 2 Jul 2006 14:09:01 -0700, Janna
    <Janna@discussions.microsoft.com> 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]
     

Share This Page