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.

 

[Pat Hartman]

You can nest functions:

Format(nz(tablevalue,0),"currency") or maybe
Format(nz(tablevalue,0),currency) - it's late and I'm a littly fuzzy at the moment.

 

[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??

 

[Pat Hartman]

Why is it obvious that you cannot put the Format() function into the SQL statement? You put the Nz() function there.

You can use almost all VBA functions in Access SQL and you can even use user-defined functions (those you write yourself)! Of course if you are writing pass-through queries, you are not using Access SQL so you cannot use Access functions or user-defined functions. You would only be able to use the integral SQL functions supported by the back-end RDBMS. You also need to be a little careful where you use non-standard SQL functions when your tables are linked to ODBC data sources or you could cause the ODBC driver to request too much data from the back-end database.

The loss of format seems to be caused by the Nz() function. Probably because it returns a varient. So, reguardless of what the original data type was, the data type returned by the function is a varient. Using the Format() function to supply a specific format will solve the problem. And yes, the function qoes into the SQL.

...
Format(NZ([Freight].[PlantFreight],0),"currency") AS PlantFreight2

I just noticed as I posted the above example that you have "*1" following each column name within the Nz() function. You are defeating the purpose of the function because the multiply operation (which doesn't seem to accomplish anything) will occur prior to the null test. Therefore, if the column is in fact null, you would cause the error that you were trying to prevent with the Nz() function in the first place.
....