Update data in field to fixed length

  • Thread starter Thread starter Lori
  • Start date Start date
L

Lori

In Access 97, I have a form using a single table as a data
source. I have a field called Policy Nbr. The data in
this field must be 7 characters long. Is there a way that
if fewer than 7 characters is entered, I can have Access
append zeros to the beginning of the number to make the
field be 7 characters long?

For example, if 9345 is entered, can Access convert this
to 0009345 and store it as such?

Thanks! Lori
 
In Access 97, I have a form using a single table as a data
source. I have a field called Policy Nbr. The data in
this field must be 7 characters long. Is there a way that
if fewer than 7 characters is entered, I can have Access
append zeros to the beginning of the number to make the
field be 7 characters long?

For example, if 9345 is entered, can Access convert this
to 0009345 and store it as such?

Thanks! Lori
Click to expand...

If the [Policy Nbr] field's datatype is Number, Access will drop
zero's to the left of the value.
Simply leave the data as is, but set the format property of the
control to
0000000

If it is a field that will never be used for calculations, the field
datatype should be Text, not Number.
In the Control's AfterUpdate event:
[ControlName] = string(7-Len([FieldName]),"0") & [FieldName]
 
Back
Top