I am developing an Access application using Access as the front end and SQL Server as the back end database. If you have an Access table with a field defined as nvarchar 6 and the table is populated with some fields that are missing the leading zero, how would you perform a SQL update to insert a leading zero into the values that only show the trailing 5 positions.

For ex:
a value appears as "23456" and should appear as "023456"
Who is Participating?
Patrick MatthewsCommented:
SQL Server:

Right('000000' + TheColumn, 6)


Right("000000" & TheColumn, 6)
Guy Hengel [angelIII / a3]Billing EngineerCommented:
leading 0 is a display issue, not a data issue.
depending on where exactly the sql runs on (sql server or access), you can put a formula.
That will actually modify and then take the right six characters of that field in every record.  Would this be any faster, or would the system actually replace every instance with itself for lengths <> 5?

myFld=IIf(len(myFld)=5, "0" & myFld),"myFld")
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.