Convert a number to text and format it

  • Thread starter Thread starter Dave
  • Start date Start date
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
 
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");
 
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...
 
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...
 
Thanks

It works like a charm


Douglas J. Steele said:
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:
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");
Click to expand...
Click to expand...
Click to expand...
 
Back
Top