Forcing digits into a text string version number

[matpj]

hi,

I have a database with version numbers in the form:

9.1.0
12.13.0

etc

I need to force digits into the field so that each version number is of the format xx.xx.xx

this would require forcing a '0' in FRONT of the first 'octet' (or whatever it is called for a three part number) and in each of the other 2 parts a '0' would have to be forced AFTER the existing number.

the script would, of course check for the presence of 2 digits beforehand.

is this possible? If so, can anyone show me how?

many thanks,

Matt

 

[JonFer]

You can write a VBA function to find each "." with INSTR and then use Format(Number,"00") to add leading zeroes. Use this in your query.

You can also write an involved expression in your query directly with:

InStr() - find "."
Right/Left/Mid - strip out certain parts
Format() - add leading zeroes

If you haven't done this before, do each number part in a separate field first and then concatenate the expressions with "&" when you're ready. Try it out and post back if you have questions.


John

http://www.interautomateconsulting.com

 

[matpj]

thanks John,

i'll try and find some time tomorrow to try it out.
you may be hearing from me!!!

Matt