Welcome to SPN

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

Sign Up Now!

Problem with Storing Data

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

  1. Sarah Schreffler

    Sarah Schreffler
    Expand Collapse
    Guest

    I have a database which, in this one table, is tracking projects.
    Projects in our company is a 15-digit number [due to legacy projects
    that were "numbered" as (client number)(project number done for that
    client)(task in that project) ]

    At some point in the near past, they switched to a sequential numbering
    system company-wide, and current projects are numbered around 40000.
    (42317, 39457, etc.) I set up a Format on my ProjectNum column of 15
    zeroes (00000 00000 00000) so that even when the easy-to-type project
    number is typed in, the entire project number shows up in the column.

    Everything works as I expect with the new projects, but we still have
    legacy projects with the old style number (9006006006, 10047001002,
    etc)

    Both in the form based on the table, and in the table itself, these
    legacy numbers don't show up properly. The two examples in particular
    show up as 9006006000 and 10047000000. I have tried retyping the
    entire number in, and changing just the 0s that are wrong to the number
    they are supposed to be, and it stubbornly changes back to the above
    numbers.

    I can change text in other fields (like Project Name) and it stays
    changed. But I can't change this one field.

    I did a test. As long as the number I am working with is 7 digits or
    less, it shows up fine. After that, it starts working wonky

    (i.e. 1 is 00000 00000 00001 and 111 is 00000 00000 00011 all the way
    to 1111111 is 00000 00011 11111 but if I put one more number in there:
    11111111 becomes 00000 00111 11110 and 111111111111111 becomes 11111
    11000 00000)

    Any idea what is going on? These numbers uniquely identify projects.
    900600600 is a completely different project from 9006006006! With a
    different Project Manager, maybe even a different department entirely!

    I am using Microsoft Access 2003 SP1

    Thank you for your help.

    --Sarah Schreffler
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikhs Face Problems While Carrying Kirpans In Khyber-Pakhtunkhwa - SikhSiyasat.Net Breaking News Jul 29, 2016
    "Dasam" Granth - A Look At The Core Problems Dasam Granth Oct 21, 2015
    Problems In Life Sikh Sikhi Sikhism Apr 13, 2015
    How can I make decent and useful friends and from where ?? most people have a problem with me Sikh Youth Oct 23, 2013
    The Problem with Taking Too Many Vitamins Health & Nutrition Oct 18, 2013

  3. dbahooker@hotmail.com

    dbahooker@hotmail.com
    Expand Collapse
    Guest

    Sarah;

    Don't try to put information into your primary key and foreign key;
    they need to be a surrogate-- completely independent single column
    integer.

    if you used Access Data Projects then you could have a big-old nuimber
    like that without a problem using the BIGINT datatype

    -Aaron



    Sarah Schreffler wrote:
    > I have a database which, in this one table, is tracking projects.
    > Projects in our company is a 15-digit number [due to legacy projects
    > that were "numbered" as (client number)(project number done for that
    > client)(task in that project) ]
    >
    > At some point in the near past, they switched to a sequential numbering
    > system company-wide, and current projects are numbered around 40000.
    > (42317, 39457, etc.) I set up a Format on my ProjectNum column of 15
    > zeroes (00000 00000 00000) so that even when the easy-to-type project
    > number is typed in, the entire project number shows up in the column.
    >
    > Everything works as I expect with the new projects, but we still have
    > legacy projects with the old style number (9006006006, 10047001002,
    > etc)
    >
    > Both in the form based on the table, and in the table itself, these
    > legacy numbers don't show up properly. The two examples in particular
    > show up as 9006006000 and 10047000000. I have tried retyping the
    > entire number in, and changing just the 0s that are wrong to the number
    > they are supposed to be, and it stubbornly changes back to the above
    > numbers.
    >
    > I can change text in other fields (like Project Name) and it stays
    > changed. But I can't change this one field.
    >
    > I did a test. As long as the number I am working with is 7 digits or
    > less, it shows up fine. After that, it starts working wonky
    >
    > (i.e. 1 is 00000 00000 00001 and 111 is 00000 00000 00011 all the way
    > to 1111111 is 00000 00011 11111 but if I put one more number in there:
    > 11111111 becomes 00000 00111 11110 and 111111111111111 becomes 11111
    > 11000 00000)
    >
    > Any idea what is going on? These numbers uniquely identify projects.
    > 900600600 is a completely different project from 9006006006! With a
    > different Project Manager, maybe even a different department entirely!
    >
    > I am using Microsoft Access 2003 SP1
    >
    > Thank you for your help.
    >
    > --Sarah Schreffler
     
  4. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Hi Sarah ,

    Since your numbers are stored as text, you can pad the
    beginning of the number with the appropriate number of zeros...

    UPDATE Tablename SET ProjectNumber
    = left("000000000000000",15-len(nz(ProjectNumber,""))
    & ProjectNumber


    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    Sarah Schreffler wrote:
    > I have a database which, in this one table, is tracking projects.
    > Projects in our company is a 15-digit number [due to legacy projects
    > that were "numbered" as (client number)(project number done for that
    > client)(task in that project) ]
    >
    > At some point in the near past, they switched to a sequential numbering
    > system company-wide, and current projects are numbered around 40000.
    > (42317, 39457, etc.) I set up a Format on my ProjectNum column of 15
    > zeroes (00000 00000 00000) so that even when the easy-to-type project
    > number is typed in, the entire project number shows up in the column.
    >
    > Everything works as I expect with the new projects, but we still have
    > legacy projects with the old style number (9006006006, 10047001002,
    > etc)
    >
    > Both in the form based on the table, and in the table itself, these
    > legacy numbers don't show up properly. The two examples in particular
    > show up as 9006006000 and 10047000000. I have tried retyping the
    > entire number in, and changing just the 0s that are wrong to the number
    > they are supposed to be, and it stubbornly changes back to the above
    > numbers.
    >
    > I can change text in other fields (like Project Name) and it stays
    > changed. But I can't change this one field.
    >
    > I did a test. As long as the number I am working with is 7 digits or
    > less, it shows up fine. After that, it starts working wonky
    >
    > (i.e. 1 is 00000 00000 00001 and 111 is 00000 00000 00011 all the way
    > to 1111111 is 00000 00011 11111 but if I put one more number in there:
    > 11111111 becomes 00000 00111 11110 and 111111111111111 becomes 11111
    > 11000 00000)
    >
    > Any idea what is going on? These numbers uniquely identify projects.
    > 900600600 is a completely different project from 9006006006! With a
    > different Project Manager, maybe even a different department entirely!
    >
    > I am using Microsoft Access 2003 SP1
    >
    > Thank you for your help.
    >
    > --Sarah Schreffler
    >
     
  5. Sarah Schreffler

    Sarah Schreffler
    Expand Collapse
    Guest

    strive4peace wrote:
    > Hi Sarah ,
    >
    > Since your numbers are stored as text, you can pad the
    > beginning of the number with the appropriate number of zeros...
    >
    > UPDATE Tablename SET ProjectNumber
    > = left("000000000000000",15-len(nz(ProjectNumber,""))
    > & ProjectNumber
    >
    >
    > Warm Regards,
    > Crystal
    > Microsoft Access MVP 2006


    Actually, the project number is a "Number/Single" NOT text -- one way
    we often sort by is to put them in order by project number. And Long
    Integer won't hold enough characters.

    dbahooker@hotmail.com wrote:
    > Sarah;
    >
    > Don't try to put information into your primary key and foreign key;
    > they need to be a surrogate-- completely independent single column
    > integer.
    >
    > if you used Access Data Projects then you could have a big-old nuimber
    > like that without a problem using the BIGINT datatype


    The Project Number is not the primary key of the project. (and good
    thing too! Because even if it was, having it be the wrong number would
    be a problem.) I've got an autonumber ID as the primary key of the
    table. I will look into Access Data Projects though.

    --Sarah Schreffler
     
  6. Sarah Schreffler

    Sarah Schreffler
    Expand Collapse
    Guest

    They were not being stored as text. But they are now :)
    (I was storing as a Single)
    Thanks for the help

    Sarah Schreffler

    strive4peace wrote:
    > Hi Sarah ,
    >
    > Since your numbers are stored as text, you can pad the
    > beginning of the number with the appropriate number of zeros...
    >
    > UPDATE Tablename SET ProjectNumber
    > = left("000000000000000",15-len(nz(ProjectNumber,""))
    > & ProjectNumber
    >
    >
    > Warm Regards,
    > Crystal
    > Microsoft Access MVP 2006
    >
    > *
    > Have an awesome day ;)
    >
    > remote programming and training
    > strive4peace2006 at yahoo.com
    >
    > *
    >
    > Sarah Schreffler wrote:
    > > I have a database which, in this one table, is tracking projects.
    > > Projects in our company is a 15-digit number [due to legacy projects
    > > that were "numbered" as (client number)(project number done for that
    > > client)(task in that project) ]
    > >
    > > At some point in the near past, they switched to a sequential numbering
    > > system company-wide, and current projects are numbered around 40000.
    > > (42317, 39457, etc.) I set up a Format on my ProjectNum column of 15
    > > zeroes (00000 00000 00000) so that even when the easy-to-type project
    > > number is typed in, the entire project number shows up in the column.
    > >
    > > Everything works as I expect with the new projects, but we still have
    > > legacy projects with the old style number (9006006006, 10047001002,
    > > etc)
    > >
    > > Both in the form based on the table, and in the table itself, these
    > > legacy numbers don't show up properly. The two examples in particular
    > > show up as 9006006000 and 10047000000. I have tried retyping the
    > > entire number in, and changing just the 0s that are wrong to the number
    > > they are supposed to be, and it stubbornly changes back to the above
    > > numbers.
    > >
    > > I can change text in other fields (like Project Name) and it stays
    > > changed. But I can't change this one field.
    > >
    > > I did a test. As long as the number I am working with is 7 digits or
    > > less, it shows up fine. After that, it starts working wonky
    > >
    > > (i.e. 1 is 00000 00000 00001 and 111 is 00000 00000 00011 all the way
    > > to 1111111 is 00000 00011 11111 but if I put one more number in there:
    > > 11111111 becomes 00000 00111 11110 and 111111111111111 becomes 11111
    > > 11000 00000)
    > >
    > > Any idea what is going on? These numbers uniquely identify projects.
    > > 900600600 is a completely different project from 9006006006! With a
    > > different Project Manager, maybe even a different department entirely!
    > >
    > > I am using Microsoft Access 2003 SP1
    > >
    > > Thank you for your help.
    > >
    > > --Sarah Schreffler
    > >
     
  7. strive4peace

    strive4peace
    Expand Collapse
    Guest

    you're welcome, Sarah :) happy to help

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    Sarah Schreffler wrote:
    > They were not being stored as text. But they are now :)
    > (I was storing as a Single)
    > Thanks for the help
    >
    > Sarah Schreffler
    >
    > strive4peace wrote:
    >
    >>Hi Sarah ,
    >>
    >>Since your numbers are stored as text, you can pad the
    >>beginning of the number with the appropriate number of zeros...
    >>
    >>UPDATE Tablename SET ProjectNumber
    >>= left("000000000000000",15-len(nz(ProjectNumber,""))
    >>& ProjectNumber
    >>
    >>
    >>Warm Regards,
    >>Crystal
    >>Microsoft Access MVP 2006
    >>
    >> *
    >> Have an awesome day ;)
    >>
    >> remote programming and training
    >> strive4peace2006 at yahoo.com
    >>
    >> *
    >>
    >>Sarah Schreffler wrote:
    >>
    >>>I have a database which, in this one table, is tracking projects.
    >>>Projects in our company is a 15-digit number [due to legacy projects
    >>>that were "numbered" as (client number)(project number done for that
    >>>client)(task in that project) ]
    >>>
    >>>At some point in the near past, they switched to a sequential numbering
    >>>system company-wide, and current projects are numbered around 40000.
    >>>(42317, 39457, etc.) I set up a Format on my ProjectNum column of 15
    >>>zeroes (00000 00000 00000) so that even when the easy-to-type project
    >>>number is typed in, the entire project number shows up in the column.
    >>>
    >>>Everything works as I expect with the new projects, but we still have
    >>>legacy projects with the old style number (9006006006, 10047001002,
    >>>etc)
    >>>
    >>>Both in the form based on the table, and in the table itself, these
    >>>legacy numbers don't show up properly. The two examples in particular
    >>>show up as 9006006000 and 10047000000. I have tried retyping the
    >>>entire number in, and changing just the 0s that are wrong to the number
    >>>they are supposed to be, and it stubbornly changes back to the above
    >>>numbers.
    >>>
    >>>I can change text in other fields (like Project Name) and it stays
    >>>changed. But I can't change this one field.
    >>>
    >>>I did a test. As long as the number I am working with is 7 digits or
    >>>less, it shows up fine. After that, it starts working wonky
    >>>
    >>>(i.e. 1 is 00000 00000 00001 and 111 is 00000 00000 00011 all the way
    >>>to 1111111 is 00000 00011 11111 but if I put one more number in there:
    >>>11111111 becomes 00000 00111 11110 and 111111111111111 becomes 11111
    >>>11000 00000)
    >>>
    >>>Any idea what is going on? These numbers uniquely identify projects.
    >>>900600600 is a completely different project from 9006006006! With a
    >>>different Project Manager, maybe even a different department entirely!
    >>>
    >>>I am using Microsoft Access 2003 SP1
    >>>
    >>>Thank you for your help.
    >>>
    >>>--Sarah Schreffler
    >>>

    >
    >
     

Share This Page