NZ function and curency formatting

[esposj]

Hello I really could use a hand with this as I want to get this project off of my desk

I am using an sql statement to populate a report.

Anyway, thanks to this board, I learned about the NZ function. This has made my reports work properly as far as the totals go. However, I can no longer seem to format the cells.

I found one thread that said to change nz(tablevalue,"0") to nz(tablevalue,0). I did that and I believe that the data is in the report as a number. I just can't seem to format it at all.


any ideas??
thanks A lot

Joseph E. Esposito

btw - if I should move this to the report forum please let me know

[This message has been edited by esposj (edited 08-01-2001).]

 

[Rich]

Set the format for the text boxes via the property sheet.

 

[esposj]

I'm a little confused,

obviously, I can't put the format command in the SQL syntax. Am I supposed to put the nz command somewhere else besides in the sql statement?

Here is the current statement
SELECT [Invoices].[Date], [Invoices].[Vendor], [Freight].[PCBX], [Invoices].[InvoiceNum], [Freight].[FgtCo], [Freight].[ProNum], [Freight].[BouquetFreight]*1 AS BouquetFreight2, [Invoices].[Bouquet], NZ([Freight].[PlantFreight]*1,0) AS PlantFreight2, [Invoices].[Plants], NZ([Freight].[FlowerFreight]*1,0) AS FlowerFreight2, [Invoices].[Flowers], [Invoices]![Bouquet]+[Invoices]![Plants]+[Invoices]![Flowers]+BouquetFreight2+PlantFreight2+FlowerFreight2 AS InvoiceTotal
FROM Invoices LEFT JOIN Freight ON [Invoices].[InvoiceNum]=[Freight].[InvoiceNum]
WHERE (((Month([Invoices].[Date]))=[monthToPrint]) AND ((Year([Invoices].[Date]))=[yearToPrint]))
ORDER BY [Invoices].[Vendor];

I had all the text box format properties set to currency already.

Still no luck..
any other ideas??