Currency-formatted textbox shows up in simple number format on report

[Ingeneeus]

That's it in a nutshell, but I'll clarify.

I've got a report which we use as a purchase order. There is a field for shipping and handling, which is entered on the PO creation form.

When we do not include shipping and handling on the PO (some vendors ship on our UPS account), the shipping and handling box renders in currency style ($0.00). However, when we do enter S&H in the PO creation form, the currency formatting goes away on the report ("$15.00" ==> "15", "$15.01" ==> "15.01")

I've checked the Properties of the textbox on the report and it is set to Currency, 2 decimal places.

I tried entering a custom format listed in Microsoft's support pages ($#,###.00) in the format property, but it doesn't appear to make a difference.

Any thoughts on what I'm missing? This is really just a petty annoyance, so I don't really want to spend a LOT of time fixing it (but it is an annoyance ). As always, any light that can be shed will be most appreciated.

Thanks!

 

[JHB]

If the other controls (textboxes) shows correct, then try by creating a new one.

 

[Ingeneeus]

If the other controls (textboxes) shows correct, then try by creating a new one.

Hi, JHB --
I tried your suggestion, but got the same results in my new textbox. I started thinking that maybe the textbox formatting was being overridden by the data source, so I took a look at that. The source is a field in the Purchase Orders query. It turns out that it was constructed with an Nz function:
ShippingAndHandling: Nz(PurchaseOrders.ShippingAndHandling,Format(0,"Currency"))
That explains the reason that if S&H is zero, it displays as $0.00.
I took a shot that if I added a format statement to the variable of that statement, it might just give me what I needed. To that end:
ShippingAndHandling: Nz(format(PurchaseOrders.ShippingAndHandling,"Currency"),Format(0,"Currency"))
and voila: "$15.00"

Feelin' pretty good right about now!