Question posted 2010 · +2 upvotes
I have an access table with a text field. It has alpha numeric values. But i want all values to be minimum 3 digit. so i want to add zeroes in front of all single or two digit values
.
5 must become 005
89 must become 089
how do i write a query to update all values in the table.
thanks tksy
Accepted answer +9 upvotes
The key is to add as many zeros as needed to take the length up to 3.
UPDATE yourTable
SET YourField = LEFT("00", 3-LEN(YourField)) + YourField
WHERE LEN(YourField)<3 AND Len(YourField)>0
Top ms-access Q&A (6)
- How can I modify a saved Microsoft Access 2007 or 2010 Import Specification? +31 (2008)
- OleDbCommand parameters order and priority +28 (2009)
- Is there an equivalent to the SUBSTRING function in MS Access SQL? +26 (2009)
- What do I need to read Microsoft Access databases using Python? +25 (2009)
- MS Access library for python +24 (2009)
- is there any replacement of Access? +21 (2009)
ms-access solutions on this site
.