Convert a number to text and format it

D

Dave

I have employee numbers that are supposed to be 6 digit. So I am converting
them to text, but when I do a number like 12345 is not 6 digit. I want to
convert this to text and give all numbers a 6 digit format like 0123456

Is this possible in a query?

Thanks
 
F

fredg

I have employee numbers that are supposed to be 6 digit. So I am converting
them to text, but when I do a number like 12345 is not 6 digit. I want to
convert this to text and give all numbers a 6 digit format like 0123456

Is this possible in a query?

Thanks
Click to expand...

Change the field Datatype to text, then run an update query:
Update YourTable Set YourTable.FieldName] =
Format([FieldName],"000000");
 
D

Dave

Can the datatype be changed via a query?

I am trying to automate a process.

Thanks

dave
fredg said:
I have employee numbers that are supposed to be 6 digit. So I am
converting
them to text, but when I do a number like 12345 is not 6 digit. I want
to
convert this to text and give all numbers a 6 digit format like 0123456

Is this possible in a query?

Thanks
Click to expand...

Change the field Datatype to text, then run an update query:
Update YourTable Set YourTable.FieldName] =
Format([FieldName],"000000");
Click to expand...
 
D

Douglas J. Steele

Permanently, or temporarily?

If you use the Format function like Fred suggested, the field in the query
will be text, not a number. That means that if you only want it temporarily
a string, you can simply use the query, rather than the table.

If you want to do it permanently, you'll have to change the data type in the
table, then run an Update Query to set the values correctly.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Dave said:
Can the datatype be changed via a query?

I am trying to automate a process.

Thanks

dave
fredg said:
I have employee numbers that are supposed to be 6 digit. So I am
converting
them to text, but when I do a number like 12345 is not 6 digit. I want
to
convert this to text and give all numbers a 6 digit format like 0123456

Is this possible in a query?

Thanks
Click to expand...

Change the field Datatype to text, then run an update query:
Update YourTable Set YourTable.FieldName] =
Format([FieldName],"000000");
Click to expand...
Click to expand...
 
Top