Client ID format alphanumeric

[Hanhar]

I use the client ID in alphanumeric format for my database purposes. The length of the ID should be 9 characters long including 8 numeric and 1 letter at the end.
Some ID's are short like 1230L, others are longer 02153M .etc...
How do I add leading zeros in the Clint ID so all ID's are 9 characters long with 8 numeric and 1 different letter at the end?
I am using access 2007.


Is this doable for excel as well?
Thanks

[ranman256]

In your form , fill out the boxes needed for your alphas, have an extra box for the ID to be calculated...txtID
when they press the SAVE button , fill the ID and have an append query add the record.

the formula for txtID is
'oops sorry
i = len(txtLetters)
txtID = string(9-i,"0") & txtLetters

then run the query.

[June7]

If this value is generated during data entry to a bound form, should not need an append query, just populate the field of record.

[ssanfu]

How do I add leading zeros in the Clint ID so all ID's are 9 characters long with 8 numeric and 1 different letter at the end?

This is unclear about WHEN you want to add the leading zeros.

When you are entering the ID in a form? -> Set an input mask. Or use the before update event of the control to add the leading zeros.

In a query for a form or report? -> MyClientID:Right("00000000" & ClientID,9)

Data already entered in a table. -> Use an update query (or VBA code)

[Hanhar]

I would like to add leading zeros for client ids already entered in table. Most of the id's are shorter then 9 characters. I will also be entering the client IDs for new clients in the table.
I tried going to design view of table and change format and mask for Client ID field property. What do I really enter in the properties and where? Format or input mask?

[John_G]

To update all existing fields, try this:

string(9-len([ID]),"0") & [ID]

For the form properties, you would us an input mask. A mask governs how data is entered, whereas a format governs how it is displayed.

A suitable input mask might be ">00000000L"

The first 8 characters are digits, required, and the last character is a letter, required. The ">" makes everything upper case (affecting only the letter).