Problem converting currency value

[Stuart Green]

I am trying to create a fixed format datafile which contains currency amounts. The problem is that the format the values have to be in "drops" the decimal point and also has to be paddded out to 11 characters. Example

102.56
978.56
126.70

should be in the file as follows

00000010256
00000097856
00000012670

I can "sort of" get there using format, the problem is with the last example where the value is 126.70. This is treated as 126.7 and gives a result of

00000001267 (equates to £12.67) when it should be
00000012670 (£126.70)

Have tried splitting off the pence using RIGHT([value],2) with a view to then adding back to the pounds but to no avail as it ingores the zero amount and gives .7 rather than 70.

Have tried using CStr but same problem arises where the value is really only 1 decimal place

Am now at the end of my tether! Any suggestions short of sacrificing an animal at midnight under a full moon would be much appreciated (yes I am that desperate).

 

[Stuart Green]

Well, after 2 days banging my head just had a EUREKA moment!

It may not seem elegant but this works (where [grand] is the currency value)

The problem has been where the last digit was a zero, so I thought lets change this.

In my queery I add 0.001 to the currency value so every value ends with a 1 and is now 3 decimal places, then take the last 3 digits using the RIGHT command and then the first 2 of these using LEFT to finally give me a figure for the pence including those that end with a zero (the problem values). Now, add this to the rounded down pounds INT and then format to 11 places . Phew


Format(Int([grand]) & Left(Right(([grand]+0.001),3),2),"00000000000")

 

[neileg]

Why don't you just multiply the value by 100?

 

[Stuart Green]

Because that is far too obvious! switch on the obscenity filter. Time to retire!
Many thanks, I seem to have been immune to the bl**dy obvious

 

[neileg]

Of course I've never missed the bleedin' obvious before, oh no...