Formatting a numerical field

[MarionD]

Oh dear! I'm having problems formatting again!

In my export text file I need to pass a value of exactly 20 Characters.

eg. 240,25 should be 00000000000000240,25
-240,25 should have be -0000000000000249,25 (the string also only 20long including the minus sign)

I have tried Format(Bescheidsumme, "!@0000000000000000000") 'Anordnungsbetrag, num(20), Pflicht
but i cant get the thing to work correctly with a minus in front (If plus no sign is needed)

Can someone point me in the right direction?
Thanks a mil!

 

[gemma-the-husky]

hsve you tried generating an export spec in fixed width format

get your query, export it manually to text, and save the spec
then you can get the spec exactly as you want

 

[WayneRyan]

Marion,

If there are always two decimal places:

strNumber = IIf([YourField] < 0, _
                Format([YourField],  "0000000000000000.00"),
                Format([YourField], "00000000000000000.00"))

If you have variable #s of decimal places, you're gonna have to write
your own custom function, along with possible truncation rules.

Wayne

 

[MarionD]

Hi Wayne,

Thanks a lot.
I used a normal if the else statement. Do you think the iff is faster? better?

Thanks for the answer!

Marion

 

[WayneRyan]

Marion,

I thought it might have been in a query.

In VBA code, I'd use a regular If-Then-Else statement.

Wayne