[AccessD] NZ for Null

The Standard formatting worked for the code / fields that are fields in
the tables. But it does not even allow the choice of Format (standard)
from the dropdown when the field is from a query total. The report is
based on a query to get the Net Totals and another query to show the
Purchased units. Both queries and a table are brought into a query for
the report. So the report fields from the calculated query do not allow
formats.

 

This seemed to work:

Net Issued: Format(Nz(qry_nonpurchases.SumOfUnits,0),"#,###")

 
The NZ function will substitute 0 for Null for your parameters, but the
Format(Standard) and DecimalPlaces = 0 will control how the data in the
field on the report will look.  i.e. 7400 vs. 7,400.
 
Give it a try - let me know how it worked.
 
Dan
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
<http://databaseadvisors.com/mailman/listinfo/accessd> 
[mailto:accessd-bounces at databaseadvisors.com
<http://databaseadvisors.com/mailman/listinfo/accessd> ] On Behalf Of
Hollis, Virginia
Sent: Wednesday, June 20, 2007 2:28 PM
To: accessd at databaseadvisors.com
<http://databaseadvisors.com/mailman/listinfo/accessd> 
Subject: [AccessD] NZ for Null
 
Yes, but I want to include the format with the Nz too in the query for
the report. I used the Nz and want to add the format to all that to show
the results with the comma.
 
Format this to show it all with commas.
Nz(tbl_InventoryCurrent!Units,0) - Nz(qry_nonpurchase!SumOfUnits,0)
-Nz(qry_purchase!SumOfUnits,0)
 
In the property sheet for a text field on a Form or Report, it looks
like you would select Standard for the format, and then enter 0 for
Decimal Places.
 
Dan