Leading character and zeroes to pad field entries

[FirstVette52]

I have an ID number text field that uses the format 'L00000000'. My table
already has some 3000+ entries 2-7 characters in length (ie: 27340) that need
to be padded to 9 characters (ie: L00027340), the first character 'L' and the
rest '0's.

I used the following Input Mask !/L00000000 and the 'L' appears, but the
zeroes do not, just spaces.

I appreciate any help you may be able to provide.

 

[OssieMac]

This might not be the best method but I would run a Make Table query and use
an expression to create a new field with the formatting required. Initially I
thought you were using L in the mask as requiring an alpa character but on
re-reading it looks like you want to prefix all numbers with the letter L. Is
my assumption correct? If correct then create a query and then create an
expression (or virtual) field something like this:-

TestFormat: "L" & Format([OrigTable]![TestFormat],"00000000")

Include all fields from the original table in the query except the one that
has to be reformatted. The above expression is used in place of that field. I
have used TestFormat as the name of the field in the example.

In the Make Table query you will need a different table name to the original
table name. I would rename the old table before creating the query and then
in the make table query I would use the original name of your table. That way
you keep a backup of the old table.

Feel free to get back to me.

 

[John Spencer]

If you want all the characters - including the zeroes - change your input mask
to force it to save the formatted data instead of just what you have typed.
!/L00000000;0
The zero after the semi-colon specifies that Microsoft Access stores the
literal display characters in the table when you enter data. If you use 0 for
this section, all literal display characters (for example, the parentheses in
a phone number input mask) are stored with the value; if you enter 1 or leave
this section blank, only characters typed into the control are stored.

You can use an update query to fix the existing records.
STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect. The update query might look like the following.

UPDATE [YourTable]
SET [YourField} = "L" & Right("00000000" & Mid([YourField],2),8)
WHERE [YourField] Not LIKE "L########" AND [YourField] Like "L*"

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

 

[FirstVette52]

My original thought was to just impose a Mask to force an L and leading
zeroes to display, but that isn't working in my Office 07/Vista install. Is
this a glitch or is there a different format for the mask (!/L00000000;0
doesn't work; keeps changing to \!/L00000000;0). Thanks for your input.
--
FirstVette52

If you want all the characters - including the zeroes - change your input mask
to force it to save the formatted data instead of just what you have typed.
!/L00000000;0
The zero after the semi-colon specifies that Microsoft Access stores the
literal display characters in the table when you enter data. If you use 0 for
this section, all literal display characters (for example, the parentheses in
a phone number input mask) are stored with the value; if you enter 1 or leave
this section blank, only characters typed into the control are stored.

You can use an update query to fix the existing records.
STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect. The update query might look like the following.

UPDATE [YourTable]
SET [YourField} = "L" & Right("00000000" & Mid([YourField],2),8)
WHERE [YourField] Not LIKE "L########" AND [YourField] Like "L*"

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

I have an ID number text field that uses the format 'L00000000'. My table
already has some 3000+ entries 2-7 characters in length (ie: 27340) that need
to be padded to 9 characters (ie: L00027340), the first character 'L' and the
rest '0's.

I used the following Input Mask !/L00000000 and the 'L' appears, but the
zeroes do not, just spaces.

I appreciate any help you may be able to provide.

 

[John Spencer]

Try the following for the input mask.
\L00000000!;0
Note the ! has been moved to the end of the string.

I almost never use input masks as they don't give me the degree of
flexability I like to have.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[FirstVette52]

That put the 'L' in the field, but not a '0' in the empty places of the field
(for field that already contains '28903', I now have 'L 28903')

Thx!

 

[John Spencer]

The input mask will not change existing data.

If that is not the problem, try experimenting. You've exceeded my
degree of knowledge about input masks.

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

 

[John W. Vinson]

That put the 'L' in the field, but not a '0' in the empty places of the field
(for field that already contains '28903', I now have 'L 28903')

If you want to update your table to fill in actual zeroes in the Text type
field (permanently changing the contents of your table) you can run an Update
query updating fieldname to

Left([fieldname], 1) & Format(Val(Mid([fieldname], 2)), "0000000")

Back up your database first!!!