Problem with Storing Data

[Sarah Schreffler]

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

 

[dbahooker]

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

 

[strive4peace]

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]

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.

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

 

[Sarah Schreffler]

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

Sarah Schreffler

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

*

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

 

[strive4peace]

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

*

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

Sarah Schreffler

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

*

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