leading zeros

G

Guest

I need to change a field in my database from number to text in order to allow
alphanumeric entries. Changing the data type was easy, but I need to update
the current entries to be able to link with other databases.

Currently the data are numbers (3-6 digits long) stored as text. The new
entries will be 10 digit numbers with or without a 4-letter code attached. If
there is no code, then the numbers have to have leading zeros to pad it out
to 10 digits in order to link with the other databases.

i.e. either: 0123456789 or ABCD9876543210

1. How can I update the existing data to 10-digit numbers with leading
zeros? i.e. 1234 to 0000001234 I've been playing with both FORMAT and INPUT
MASK but can't get it to store the whole number, only display it.

2. Is there a way to make this a default for new entries that don't need a
letter code, i.e. can I enter 123456 and have it store as 0000123456?

Thanks!
 
G

Guest

In VBA on afterupdate event for field something like..
field=right(string(10,"0") & field,10)

Can do same in update sql to alter current data.

Madhouse
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top