How can I create an Integer Number field with Leading Zeroes?

[Guest]

Hi,

I could use some help. I need an integer or a long integer field that keeps
it's leading zeroes. Is there a way to do this?

Thanks so much.

 

[strive4peace]

you can use the format code to show a specified number of digits

for example:

format code --> 000000

1 --> 000001

9999 --> 009999

internally, the number will be stored the same, format just affects the
display...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Hi! Thnanks for responding so quickly. How and where do I use the Format
code? Is it set up in the table itself? What's the syntax for this?

Thanks so much,
Kathy

 

[strive4peace]

Hi Kathy,

you're welcome

yes, you can define a format for the table field -- from the design view
of your table, click on the field and fill the format in the lower pane.
For more help on Format codes, press the F1 key in the Format property.

If you have already set up forms and reports, you will also need to
define the Format for the respective controls on those objects (since
they get the Format from the table when they are created)

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[enginerd477]

Crystal,
thanks for the help, i found this thread and it helped me out greatly,
but i have a new problem. i had to change my Data type to integer to
get the zeros to show, but what i need is for the field to be able to
accomodate a letter at the beginning of the Number. example: C00123
or B12345.
I was wondering if there was anyway to do this while still keeping the
leading zeros, the only letters i need to use are A, B, C, D, and E;
if that helps.
The reason im doing this is that we have parts that are assigned a
number and then depending on what size they are we give them a certain
letter, and then the numbers follow.
Thanks for any help.

 

[Guest]

Crystal,

Thanks so much! Works like a charm!

Have a great day,
Kathy

 

[strive4peace]

you're welcome, Kathy happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

Hi engineer (assuming that is what you are from your username )

you're welcome, glad you found us!

If you will be wanting a letter at the beginning, the field needs to be
set up as text

if you give an example of what your values should look like, we can help
you with the Format code if you have trouble -- click in the Format
property and press the F1 key to look at format codes


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[enginerd477]

My values will look something like the following:

A02345
B12345
C00012
D02034
E34567

It's just one of those letters with a 5 digit number. i was hoping to
make people's lives easier so that when they only have like 3 didgits
ot add the zero's come up automatically. I was also planning on adding
a combo bos with the letter's in it and when the user picked the letter
for the right size part it would add this directly to the part number.

Thanks again for any help, Peace.

 

[strive4peace]

Hi engineer,

combobox:

Name --> FLetter
RowSourceType --> Value List
RowSource --> A;B;C;D;E

AfterUpdate --> =MakeField()

testboxes:

Name -- FNumber
Format --> 00000
AfterUpdate --> =MakeField()

Name --> Fieldname
ControlSource --> Fieldname

code behind the form:

'~~~~~~~~~~~~~~~~
private function MakeField()
if isnull(FLetter) _
or isnull(FNumber) then
me.fieldname = null
exit sub
end if

me.fieldname = FLetter _
& format(FNumber, "00000")
end function
'~~~~~~~~~~~~~~~~

and, then each time a record changes, use the form Current event to fill
out the 2 unbound controls for collecting data:

'~~~~~~~~~~~~~~~~
if isnull(me.fieldname) then
me.FLetter = null
me.FNumber = null
exit sub
end if

me.FLetter = left(me.fieldname,1)

me.FNumber = cLng(mid( me.fieldname,2, _
len(me.fieldname)-1 ))

'~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*