Convert a number to text and format it

[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

 

[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

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

 

[Dave]

Can the datatype be changed via a query?

I am trying to automate a process.

Thanks

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

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

 

[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!)

Can the datatype be changed via a query?

I am trying to automate a process.

Thanks

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

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

 

[Dave]

Thanks

It works like a charm

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!)

Can the datatype be changed via a query?

I am trying to automate a process.

Thanks

dave

On Fri, 3 Sep 2004 10:17:01 -0500, Dave wrote:

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

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