adding zeros to the front of a field

[Guest]

I have a field that has vendor numbers, (115, 3245, 10987, 111,234, etc.).
Is there a way to runa query that will add zeros to the front of each vendor
number until it is 10 digits long.

I want to see the following:

0000000115
0000003245
0000010987
0000111234

Is it possible to do this with a query?
Thanks for the help...

 

[Steve]

Your field needs to be text to display leading numbers. You don't need a
query to do this; you just need to format the display.
Format([VendorNumber],"0000000000")
Note, the 0s at the right end only tell Access that those characters are
digits.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[George Nicholson]

In the query designer:
VendorNum: Format([VendorID],"0000000000")

In SQL view:
SELECT.....Format([VendorID],"0000000000") As VendorNum,.....

HTH,

 

[Guest]

Can you give me a little more info on how to do this in query designer? Thanks
--
JT

In the query designer:
VendorNum: Format([VendorID],"0000000000")

In SQL view:
SELECT.....Format([VendorID],"0000000000") As VendorNum,.....

HTH,

I have a field that has vendor numbers, (115, 3245, 10987, 111,234, etc.).
Is there a way to runa query that will add zeros to the front of each
vendor
number until it is 10 digits long.

I want to see the following:

0000000115
0000003245
0000010987
0000111234

Is it possible to do this with a query?
Thanks for the help...

 

[Guest]

One important question is what do you intend to do with the query?
If it is for a form or report, you will get better performance if you do the
formatting on the report or form. If you are wanting to export it to Excel.
It will not work. Formats are not maintained when importing or exporting to
or from Excel.

 

[Guest]

I am trying to update the field on all records in a table

 

[Guest]

If the data type of the field is text, okay, but not a good idea.
If the data type of the field is numeric, it wont work. numeric fields
don't store leading zeros. Leading zeros are only for we humans. If this
field is text, it really should be changed to numeric. Then use the
formatting as previously shown for the humans. Computers don't care about
leading zeros.

 

[John Spencer]

IF you are planning to do this as a PERMANENT change then you can use an
update query, as long as your field is a TEXT field. Number fields do not
store leading (or trialing) zeroes.

Open a new query
-- Select your table
-- Select the field with the value (field must be of type text)
-- Set the criteria to Is Not Null
-- SELECT Query: Update from the menu
-- Set Update to
Right("0000000000" & [Vendor Number],10)
-- SELECT Query: Run from the menu

BY the way BACKUP your database BEFORE you do this. Undo will not work to
restore the data if this is not what you want.

If you just want to display the information with leading zeroes, you can use


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks....I understand what you are saying but the end user wants to see the
leading zeros in the table. I have made the field as text but I need to
change the records so the leading zeros are displayed in the table.

Any help in doing this would be appreciated... Thanks

 

[Guest]

Users should NEVER be allowed to see a raw table. We can guarantee they will
screw up your data sooner or later.
All data presented to users should be in a form or a report. period.

Sorry, JT, I don't mean to be critical of your, My intent is to help you
protect yourself from your users.

 

[Guest]

No problem....no offense taken.....I agree 100 % and my users usually do not
see the raw data....this is a special project with a few "special" users; who
are not the ordinary users. I appreciate the advice....Thanks again

 

[Guest]

John.....Thanks.......that is what I did and got us to were they want to be.
Thanks again for the help.....
--
JT

IF you are planning to do this as a PERMANENT change then you can use an
update query, as long as your field is a TEXT field. Number fields do not
store leading (or trialing) zeroes.

Open a new query
-- Select your table
-- Select the field with the value (field must be of type text)
-- Set the criteria to Is Not Null
-- SELECT Query: Update from the menu
-- Set Update to
Right("0000000000" & [Vendor Number],10)
-- SELECT Query: Run from the menu

BY the way BACKUP your database BEFORE you do this. Undo will not work to
restore the data if this is not what you want.

If you just want to display the information with leading zeroes, you can use


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..