formatting number without trailing zero's

[iankerry]

Hi

I have a ticket printing report. On some events tickets are £4.50, but on others it is £4.00.

I want the latter one to read £4 - losing the trailing zeros.

I assume it is an OnFormat event, but is there a command to lose trailing spaces? It is a currency field.

Thanks

ian

 

[namliam]

No need to change the format, it is probably set as a currency default which has...
#0.00 as default format, forcing 0 even when not there.
#0.## will only show zero's if there...

Potentially though should also show 4.5 instead of 4.50

 

[Galaxiom]

To get around the potential problem namliam's post mentions, this produces a string:

"£" & Iif([fieldname]-Int([fieldname])=0, Format([fieldname],"0"), Format([fieldname],"Fixed"))

I actually put "0.00" where it says "Fixed" but Access 2007 changed it.
I am curious about this change. How does Access decide that two decimals is the meaning of "Fixed".
Does Fixed mean to keep it as original? The original field is formatted currency as $0.00

No. It still says "Fixed" for "0.00" when the original field is $0.000

Here is the go:
http://www.webcheatsheet.com/SQL/access_functions/format.php

 

[iankerry]

Hi

sorry if i am dense, i didnt understand your reply Galax. Is that the solution or did you find another problem?!

ian

 

[Galaxiom]

#0.## will only show zero's if there...
Potentially though should also show 4.5 instead of 4.50

namliam pointed out a potential problem with his own solution.
If you are happy for £4.50 to show as £4.5 then it isn't really a problem.

My solution checks after the decimal point and prints both places for anything other than .00

Note however my result is no longer a number but a text string so it should not be used anywhere you want to add the figures.

I did ramble on about not understanding why Access substituted "Fixed" for "0.00"
Just ignore that part.

 

[iankerry]

OK Thanks, I think I get it.

So the line

"£" & Iif([fieldname]-Int([fieldname])=0, Format([fieldname],"0"), Format([fieldname],"Fixed"))

doesn't look like a normal line of code, i can just about see what it is trying to do, but i cant put this is an event procedure can I? I have tried putting in the format column on the property sheet, which has some interesting results. Also tried to make adultTP (the field) = the string but no joy...

sorry and thanks for your input.

 

[namliam]

Using that code as the input of your report field or as a query column should result in what you need.

 

[iankerry]

Hey, I got it and it worked!!! Thanks so much. Really appreciate it.

Ian

artsalive.co.uk

 

[iankerry]

one problem has arisen though. previously if the ticket price was £0, it didn't dispaly at all, which is what i wanted. however now we have converted it to a text field is does appear as £0.

Is there a tweak that could be performed to make the field blank when there is a £0 ticket price?

thanks
ian

 

[namliam]

Tweak:
iif([fieldname]= 0, Null,
"£" & Iif([fieldname]-Int([fieldname])=0, Format([fieldname],"0"), Format([fieldname],"Fixed"))
)

 

[iankerry]

You are a tweak God.

I am honoured to add to your reputation!

Ian

 

[April15Hater]

I have a similar problem, but the above solution didn't seem to work for me. I have an instance on a report where the price column can go past 3 decimal places ($0.003). However many of our prices are listed in the standard 2 decimal place convention ($5.00). I'd like for it to show that 3rd decimal only when that third decimal is present in the table and the standard convention all other times.
Thanks
Joe

 

[Galaxiom]

Tweak4Variant:
iif([fieldname]= 0, Null,
"$" & Iif(Right([fieldname], 1)="0", Format([fieldname],"Fixed"), Format([fieldname],"0.000"))
)

 

[April15Hater]

You certainly led me in the right direction! Here's what I ended up with

=IIf([price]=0,Null,"$" & IIf(Right(Format([price],"0.000"),1)=0,Format([price],"Fixed"),Format([price],"0.000")))

Thanks guys!

Joe

 

[Galaxiom]

Excellent. A moment after I pressed Post I realised I had left out exactly what you posted. The site is communicating with me so slowly that I stopped waiting for a chance to edit.

Get the feeling the site is getting a lot of traffic.

 

[April15Hater]

I'm having the same speed problem... Once I saw what the expression was actually doing, I had a bit of an aha moment, lol! Thanks again!