Numbers & text

[Fred]

I have data that has possible 3 characters & up to 10 numbers & up to 3
characters after the numbers. for example
A 012345678 BZ Displayed as A012345678BZ
A 012345679 BZ Displayed as A012345679BZ
I have a query that contains 100 columns that add 1-99 based on the number
entered, then combined with the letters
example of first field expression=
[character1]&[character2]&character3]&[numbers]&[character4]&[character5]&[character6]

example of second field expression=
[character1]&[character2]&character3]&[numbers]+1&[character4]&[character5]&[character6]

this gives me the all letters entered on the left of the numbers, the
numbers, and the letters on the right, then the next field adds +1

My problem is that if the number begins with a zero, the zero is dropped.
now my data that should have been A012345679BZ is now A12345679BZ.

I wan't to be able to calculate numbers then make them into text within one
database using queries and tables. I create 100 idnumbers in consecutive
order at a time. the position of the letters varies, and the numerical data
is from 3 to 10 digits.....please help someone. Thanks for your time!

 

[Marshall Barton]

I have data that has possible 3 characters & up to 10 numbers & up to 3
characters after the numbers. for example
A 012345678 BZ Displayed as A012345678BZ
A 012345679 BZ Displayed as A012345679BZ
I have a query that contains 100 columns that add 1-99 based on the number
entered, then combined with the letters
example of first field expression=
[character1]&[character2]&character3]&[numbers]&[character4]&[character5]&[character6]

example of second field expression=
[character1]&[character2]&character3]&[numbers]+1&[character4]&[character5]&[character6]

this gives me the all letters entered on the left of the numbers, the
numbers, and the letters on the right, then the next field adds +1

My problem is that if the number begins with a zero, the zero is dropped.
now my data that should have been A012345679BZ is now A12345679BZ.

I wan't to be able to calculate numbers then make them into text within one
database using queries and tables. I create 100 idnumbers in consecutive
order at a time. the position of the letters varies, and the numerical data
is from 3 to 10 digits.

I have no idea what you are trying to accomplish because
creating such records with so many fields makes no sense to
me.

To try to answer your question, you can use the Format
function to add the leading zeros. If you want 8 digits in
the numbers:

....&character3]&Format([numbers]+1,"00000000")&[character4]&...

 

[Fred]

Thank you Marsh for helping me. The format function does work to a certain
degree. If the exact number of numerical characters are know or fixed, then
this solution works. However, my data contains from 3-10 numerical
characters.
Rather than creating 5 different tables, queries, forms, and reports for
each different control number type. I was trying to allow the data to be
fluid. Enter the information for the first in the batch of 100 and 99 more
are created. For now I’m using your method. It was very helpful, but I’m
still searching for the perfect solution. Thanks much.

I have data that has possible 3 characters & up to 10 numbers & up to 3
characters after the numbers. for example
A 012345678 BZ Displayed as A012345678BZ
A 012345679 BZ Displayed as A012345679BZ
I have a query that contains 100 columns that add 1-99 based on the number
entered, then combined with the letters
example of first field expression=
[character1]&[character2]&character3]&[numbers]&[character4]&[character5]&[character6]

example of second field expression=
[character1]&[character2]&character3]&[numbers]+1&[character4]&[character5]&[character6]

this gives me the all letters entered on the left of the numbers, the
numbers, and the letters on the right, then the next field adds +1

My problem is that if the number begins with a zero, the zero is dropped.
now my data that should have been A012345679BZ is now A12345679BZ.

I wan't to be able to calculate numbers then make them into text within one
database using queries and tables. I create 100 idnumbers in consecutive
order at a time. the position of the letters varies, and the numerical data
is from 3 to 10 digits.

I have no idea what you are trying to accomplish because
creating such records with so many fields makes no sense to
me.

To try to answer your question, you can use the Format
function to add the leading zeros. If you want 8 digits in
the numbers:

....&character3]&Format([numbers]+1,"00000000")&[character4]&...

 

[Marshall Barton]

You need to explain where/how the number of digits is
specified and how it's supposed to get to the records.

Maybe you can use something like
Format(x, String([Number of digits], "0"))
--
Marsh
MVP [MS Access]

Thank you Marsh for helping me. The format function does work to a certain
degree. If the exact number of numerical characters are know or fixed, then
this solution works. However, my data contains from 3-10 numerical
characters.
Rather than creating 5 different tables, queries, forms, and reports for
each different control number type. I was trying to allow the data to be
fluid. Enter the information for the first in the batch of 100 and 99 more
are created. For now I’m using your method. It was very helpful, but I’m
still searching for the perfect solution. Thanks much.

I have data that has possible 3 characters & up to 10 numbers & up to 3
characters after the numbers. for example
A 012345678 BZ Displayed as A012345678BZ
A 012345679 BZ Displayed as A012345679BZ
I have a query that contains 100 columns that add 1-99 based on the number
entered, then combined with the letters
example of first field expression=
[character1]&[character2]&character3]&[numbers]&[character4]&[character5]&[character6]

example of second field expression=
[character1]&[character2]&character3]&[numbers]+1&[character4]&[character5]&[character6]

this gives me the all letters entered on the left of the numbers, the
numbers, and the letters on the right, then the next field adds +1

My problem is that if the number begins with a zero, the zero is dropped.
now my data that should have been A012345679BZ is now A12345679BZ.

I wan't to be able to calculate numbers then make them into text within one
database using queries and tables. I create 100 idnumbers in consecutive
order at a time. the position of the letters varies, and the numerical data
is from 3 to 10 digits.

I have no idea what you are trying to accomplish because
creating such records with so many fields makes no sense to
me.

To try to answer your question, you can use the Format
function to add the leading zeros. If you want 8 digits in
the numbers:

....&character3]&Format([numbers]+1,"00000000")&[character4]&...

 

[John Spencer]

If you post what you are currently using to identify the number and add
1 to it, perhaps we can help you. For instance, if you know the number
portion of your number, then you could try:

Format(Val(NumberPortionOfYourField) + 1,"0000000000")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Bob Barrows]

I have data that has possible 3 characters & up to 10 numbers & up to
3
characters after the numbers. for example
A 012345678 BZ Displayed as A012345678BZ
A 012345679 BZ Displayed as A012345679BZ
I have a query that contains 100 columns that add 1-99 based on the
number
entered, then combined with the letters
example of first field expression=
[character1]&[character2]&character3]&[numbers]&[character4]&[character5]&[character6]

example of second field expression=
[character1]&[character2]&character3]&[numbers]+1&[character4]&[character5]&[character6]

this gives me the all letters entered on the left of the numbers, the
numbers, and the letters on the right, then the next field adds +1

My problem is that if the number begins with a zero, the zero is
dropped.
now my data that should have been A012345679BZ is now A12345679BZ.

I wan't to be able to calculate numbers then make them into text
within one
database using queries and tables. I create 100 idnumbers in
consecutive
order at a time. the position of the letters varies, and the
numerical data
is from 3 to 10 digits.....please help someone. Thanks for your time!

This should work:
Right("0000000000" & ([numbers] + 1), 10)

 

[John W. Vinson]

Thank you Marsh for helping me. The format function does work to a certain
degree. If the exact number of numerical characters are know or fixed, then
this solution works. However, my data contains from 3-10 numerical
characters.
Rather than creating 5 different tables, queries, forms, and reports for
each different control number type. I was trying to allow the data to be
fluid. Enter the information for the first in the batch of 100 and 99 more
are created. For now I’m using your method. It was very helpful, but I’m
still searching for the perfect solution. Thanks much.

The perfect solution will use a normalized table - which will NOT have 100
mutually dependent fields!!!!

As is often the case, your programmatic difficulties appear to be due to a
shaky foundation - a very non-normalized table structure.

Could you step back and describe the real-world situation you're modeling, and
why each record has 100 incremented fields?

 

[Fred]

First I want to say thank you for your interest in my situation. It’s
amazing that anyone actually responded.
My real life situation example is this. My office receives secure documents
bundled in packs of 100, each individual document with a control number. The
control number varies between different document types. Some of these secure
documents are high volume (issuing roughly 2000-3000 per week) we have
roughly 40 employees with access to these documents, therefore need to track
who issued which documents so that they are accounted for. We have a book
that we keep sign-out sheets but we must have the sheets with the control
numbers already typed in. I have only 5 or 6 that are so high volume that I
need an easier solution than doing this in excel.
One of the regularly used documents has a 7 character control number example
X0001BK. The X,B,K characters are consistent throughout the series of 100.
However the numbers count up (0001, 0002,0003…etc) So I created a table that
has [Character 1], [numerical characters], [character 2]. I then created a
query that contains [Character 1]&[numerical characters]&[character 2] ,
expression1: [character 1] & Format([numbers]+1,"0000") & [character2]…(99
expressions in the query) and this works fine for that particular secure
document I’m working with. However I have several other documents that have
completely different styles of control numbers. Like A01092001k, A01092002k
etc. Just like the first example I gave (X0001BK), the control number is
consistent for that particular control numbered document…but I have many
different documents I’m working with.
I hope this clarifies what I’m doing here. Thank you for your interest and
help.

 

[John W. Vinson]

First I want to say thank you for your interest in my situation. It’s
amazing that anyone actually responded.
My real life situation example is this. My office receives secure documents
bundled in packs of 100, each individual document with a control number.

Then you appear to have a One (pack) to Many (document) relationship. A
normalized structure would have a Packs table in a one to many relationship,
with one record per document.

"Fields are expensive. Records are cheap".

The
control number varies between different document types. Some of these secure
documents are high volume (issuing roughly 2000-3000 per week) we have
roughly 40 employees with access to these documents, therefore need to track
who issued which documents so that they are accounted for.

If you need to track individual documents the case becomes *much stronger* for
having a table of documents with one record per document. Trying to track down
the document ID by searching 100 fields in a table is a nightmare.

We have a book
that we keep sign-out sheets but we must have the sheets with the control
numbers already typed in. I have only 5 or 6 that are so high volume that I
need an easier solution than doing this in excel.

I'd try to use one solution (Access, but then I'm biased) for all your
document tracking. With a well designed system it's just as easy to track 500
documents as it is to track 5.

One of the regularly used documents has a 7 character control number example
X0001BK. The X,B,K characters are consistent throughout the series of 100.
However the numbers count up (0001, 0002,0003…etc) So I created a table that
has [Character 1], [numerical characters], [character 2]. I then created a
query that contains [Character 1]&[numerical characters]&[character 2] ,
expression1: [character 1] & Format([numbers]+1,"0000") & [character2]…(99
expressions in the query) and this works fine for that particular secure
document I’m working with. However I have several other documents that have
completely different styles of control numbers. Like A01092001k, A01092002k
etc. Just like the first example I gave (X0001BK), the control number is
consistent for that particular control numbered document…but I have many
different documents I’m working with.
I hope this clarifies what I’m doing here. Thank you for your interest and
help.

YOu would use the same technique to generate control numbers, but would add
*one new record* - going "down" a tall thin table - rather than a new *field*
for each document. If the control number is unique (no two documents will ever
have the same control number) and stable (once a control number is assigned it
will never change) you could use that value as the Primary Key of your table.
The inconsistant format is possibly a problem, though; you might instead want
this table to have an Autonumber DocumentID primary key, and a unique Index on
the control number, ensuring that different documents can't be assigned the
same control number.