How to make a 6 digit # into a 10 digit #

[Guest]

Having a hard time doing this in a query. What I want to do is this:

before: 123456
456789

after: 0000123456
0000456789

I know I can do this in a macro, but I was wondering if it is possible to do
this in query form.

Also, I was wondering how to remove any records that have more than 10 digits

Thanks for any help.

 

[Allen Browne]

Just set the Format property of the field in the query (or the text box on
your form or report) to:
0000000000

This does not store leading zeros (which would imply you are dealing with
text and not numbers.)

To find a number field with more than 10 digits (assuming positive numbers),
use criteria of:

9999999999

For a text field, you would type this into the Field row in the query:
Len([Field1])
and in the Criteria row:

10

Substitute your field name for Field1.

 

[John Vinson]

Having a hard time doing this in a query. What I want to do is this:

before: 123456
456789

after: 0000123456
0000456789

I know I can do this in a macro, but I was wondering if it is possible to do
this in query form.

If - as it really should be, it seems - this is a Text field you can
run an Update query updating the field to

Right("0000000000" & [fieldname], 10)

This will append ten zeros before the current contents of the field,
and then return the rightmost ten digits.

Also, I was wondering how to remove any records that have more than 10 digits

The query above will silently trim off any digits past ten. You may
want to run a query first to identify - or, if you wish, permanently
and irrevokably delete - any such records. Allen's suggestions will do
that for you.

John W. Vinson[MVP]