update with preceeding zero's

[doylie]

If I wanted to update a value in an update query from 1000101 to 000101 how would I get access to display the preceeding zero's.

 

[antomack]

Set the format on the field to '000000' this will display all numbers as 6 digits with leading zeros to make up the 6 digits where required. Set the number of zeros to whatever you require the display length to be. The number will still be seen by Access as it was but will display with the leading zeros.

 

[doylie]

Next problem

Excellent!! The update works superbly.
Now the next thing I need help on is when I try and change the field type in the design table view from long integer to a date time format, ie yymmdd. Instead of simply updating it how you would expect. It changes the values to eg. 751456. Why does this happen? If anyone knows please feel free to let me know. Thankyou

 

[Pat Hartman]

Sounds like the current contents of the column are dates in the format mmddyy. If so, Access will not recognize these strings as valid dates and so you can't convert them by simply changing the data type of the column.

You can add a new column to the table to hold the converted dates, run the conversion, delete the original column, and rename the converted column. Use an update query with something like the following in the Update To cell:

CDate(Left(YourFld,2) & "/" & Mid(YourFld,3,2) & "/" & Right(YourFld,2))