Padding a value with preceding zeros

[Sveno]

Hi,

Is anyone aware of an Access equivilent of an ORACLE function called RPAD?

I need to pad a field with preceding zero to a static number of digits. I can always do this using if or case, but thought there may be a function already that I am not aware of and cannot find in help.

Cheers

Mark

 

[scriverb]

Yes, you can use the Format function for this purpose. Check out ACCESS help for this function and it will explain its parameters in detail.

Example:

Format([yourtable]![Value_Field],"000000")

This function returns a Variant(string) with the leading zeroes as indicated.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[KenReay]

Hi

How about Format(MyValue,"00000")

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

after posting notice you said the Oracle function RPad() which made me think you want to right pad not left pad

Format() may still do the job, if you are talking about after the decimal point eg Format(myValue,"0000.00"), but if not then consider

Left(MyValue & "00000",6) adjusting "00000" and 6 accrding to your needs

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Sveno]

Thank you all for replying.

I did this work yesterday and ended up using RIGHT(('00000' & fieldname),7)

I did have a hard time though, mainly my fault through storing the number values as currency. The actually give three decimal places, but shows two on reports, queries, etc. So $2.48 was stored as 2.475 and just taking the two characters from the decimal point was incorrect.

I ended up using Instr and adding 0.005 to the numbers.

Cheers

Mark