Formatting a field

[Nigel]

Hi guys

I am using Acc97 and trying to create a field that
automatically formats a previous currency field without
the decimal place and with padded zeros.

For example :
First field value = £123.45
required value of 2nd field = 00000012345

The following works OK where the 2nd dec place is not 0

=Left([first],8)-Right([first],3) & Right([first],2)

However, the result for £123.40 would be 120.4 obviously
due to the way Access stores decimals.

Does anyone have any suggestions? TIA

Nigel

 

[Kelvin]

Why not just multiply the number by 100 to get rid of the decimal than use
format().

format("00000000000",[first]*100)

Or if you want this stored as text

Right("00000000000"&Cstr([first])*100,11)

Kelvin

Hi guys

I am using Acc97 and trying to create a field that
automatically formats a previous currency field without
the decimal place and with padded zeros.

For example :
First field value = £123.45
required value of 2nd field = 00000012345

The following works OK where the 2nd dec place is not 0

=Left([first],8)-Right([first],3) & Right([first],2)

However, the result for £123.40 would be 120.4 obviously
due to the way Access stores decimals.

Does anyone have any suggestions? TIA

Nigel