add leading zero

[nemmeh]

Hello everyone,

A guy made a post a few pages back about this, but I can't get the suggested procedure to work correctly for me. So I'd like some help if possible.


I have a number field that contained dates-of-birth, well these were just numbers like &quot;7121980&quot; or &quot;10101949&quot;. I have switched the field data type over to text and was playing around trying to get it to add a leading zero to anything < 8 characters, but I can't seem to get the syntax correct for this.

Can anyone help? just need the sql statement for it. Thanks

 

[SonOfEmidec1100]

Does this help

UPDATE MyTable SET DateField = '0' + Datefield WHERE LEN(Datefield) < 8

 

[scriverb]

There is a problem with SonOfEmidec1100's response. It only cancatenates one zero(0) in front of the text number in the field. What if the field is 123, then the result would be 0123. You have to put as many zeroes on the front as necessary depending upon the len of the string. The following SQL update query uses the Format function to do just that:

UPDATE tblMyTable as A SET A.txtDataField = Format(A![txtDataField],&quot;00000000&quot;

Post back with any questions. Just update with your table name the the text data field name.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[nemmeh]

Thanks for the help guys. 2nd query worked beautifully.

 

[scriverb]

Glad to be of assistance.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???