I have a form which has a textbox which allows the user to search for a record in a table. The field and table they are searching on is padded by zeros. For example:

00000001
00001234
0000000000789

How can I set my field up so that the user can search by typing in 1 or 1234 or 789 and the correct record will still be found. The field length of the field in the table is 10. So I would need to somehow pad the number they type in so it is 10 characters. Ideally though when the user types in 1234 I want the field to auto update in the form to 0000001234 before they search.

How can I do this?

Many thanks