Loss of Preceding Zeros

[Bernie]

I am attemping to create a value based on two existing
fields. One is a letter (i.e. [Type] is "S", the other is
a numeric field formatted to display preceding zeroes
(i.e. 0000001).

When I use the ControlSource of the new value to do this
(i.e. [Type]&[Number]), the result is S2, not S0000002 as
I want.

How can I maintain the preceding zeroes?
THANKS.
Bernie

 

[fredg]

I am attemping to create a value based on two existing
fields. One is a letter (i.e. [Type] is "S", the other is
a numeric field formatted to display preceding zeroes
(i.e. 0000001).

When I use the ControlSource of the new value to do this
(i.e. [Type]&[Number]), the result is S2, not S0000002 as
I want.

How can I maintain the preceding zeroes?
THANKS.
Bernie

=[Type] & Format([Number],"0000000")

Better yet, change the Datatype of that number field to text. Number
datatypes do not store preceding zero's.
Text datatypes do.

 

[Jim Allensworth]

I am attemping to create a value based on two existing
fields. One is a letter (i.e. [Type] is "S", the other is
a numeric field formatted to display preceding zeroes
(i.e. 0000001).

When I use the ControlSource of the new value to do this
(i.e. [Type]&[Number]), the result is S2, not S0000002 as
I want.

How can I maintain the preceding zeroes?
THANKS.
Bernie

Assuming that the number of digits is constant ...

[Type] & Format([Number], "0000000")


(Also, note that Number is a reserved word in Access. You ought not
use it for user defined objects)

- Jim

 

[Bernie]

Thank you! That worked perfectly. I used "Number" as an
example, actual field name is different. Thanks again!
Bernie

-----Original Message-----

I am attemping to create a value based on two existing
fields. One is a letter (i.e. [Type] is "S", the other is
a numeric field formatted to display preceding zeroes
(i.e. 0000001).

When I use the ControlSource of the new value to do this
(i.e. [Type]&[Number]), the result is S2, not S0000002 as
I want.

How can I maintain the preceding zeroes?
THANKS.
Bernie

Assuming that the number of digits is constant ...

[Type] & Format([Number], "0000000")


(Also, note that Number is a reserved word in Access. You ought not
use it for user defined objects)

- Jim
.