Nz Function

[Octet32]

I am using the Nz Function it work fine but my Currency is now gone how can i
get the currency format to return?

SELECT qrySubCost.Division, qrySubCost.Carrier,
Nz([Cumulative_Impact],"N/A") AS CumulativeImpact
FROM qrySubCost;

Joe

 

[Ken Snell \(MVP\)]

Are you using this query as the record source for a form or a report? If
yes, you can set the Format property of the control that is bound to the
CumulativeImpact field -- however, note that you're putting a text string
("N/A") into a format for currency .....

 

[Octet32]

I am using it as a record sourse for a report in the report footer But when i
click the dropdown on format there is nothing There.

Are you using this query as the record source for a form or a report? If
yes, you can set the Format property of the control that is bound to the
CumulativeImpact field -- however, note that you're putting a text string
("N/A") into a format for currency .....
--

Ken Snell
<MS ACCESS MVP>

I am using the Nz Function it work fine but my Currency is now gone how can
i
get the currency format to return?

SELECT qrySubCost.Division, qrySubCost.Carrier,
Nz([Cumulative_Impact],"N/A") AS CumulativeImpact
FROM qrySubCost;

Joe

 

[Ken Snell \(MVP\)]

Put this in the Format property:

$#,##0.00;($#,##0.00)

--

Ken Snell
<MS ACCESS MVP>

I am using it as a record sourse for a report in the report footer But when
i
click the dropdown on format there is nothing There.

Are you using this query as the record source for a form or a report? If
yes, you can set the Format property of the control that is bound to the
CumulativeImpact field -- however, note that you're putting a text string
("N/A") into a format for currency .....
--

Ken Snell
<MS ACCESS MVP>

I am using the Nz Function it work fine but my Currency is now gone how
can
i
get the currency format to return?

SELECT qrySubCost.Division, qrySubCost.Carrier,
Nz([Cumulative_Impact],"N/A") AS CumulativeImpact
FROM qrySubCost;

Joe

 

[fredg]

I am using the Nz Function it work fine but my Currency is now gone how can i
get the currency format to return?

SELECT qrySubCost.Division, qrySubCost.Carrier,
Nz([Cumulative_Impact],"N/A") AS CumulativeImpact
FROM qrySubCost;

Joe

Does this help?
SELECT qrySubCost.Division, qrySubCost.Carrier,
Nz(Format([Cumulative_Impact],"Currency"),"N/A") AS CumulativeImpact
FROM qrySubCost;

 

[Octet32]

Yes that worked thanks

I am using the Nz Function it work fine but my Currency is now gone how can i
get the currency format to return?

SELECT qrySubCost.Division, qrySubCost.Carrier,
Nz([Cumulative_Impact],"N/A") AS CumulativeImpact
FROM qrySubCost;

Joe

Does this help?
SELECT qrySubCost.Division, qrySubCost.Carrier,
Nz(Format([Cumulative_Impact],"Currency"),"N/A") AS CumulativeImpact
FROM qrySubCost;

 

[Octet32]

I jump the gun there ithe currency came back but the N/A is gone now.

I am using the Nz Function it work fine but my Currency is now gone how can i
get the currency format to return?

SELECT qrySubCost.Division, qrySubCost.Carrier,
Nz([Cumulative_Impact],"N/A") AS CumulativeImpact
FROM qrySubCost;

Joe

Does this help?
SELECT qrySubCost.Division, qrySubCost.Carrier,
Nz(Format([Cumulative_Impact],"Currency"),"N/A") AS CumulativeImpact
FROM qrySubCost;

 

[Klatuu]

You can't have it both ways. It is either a numeric field of currency type or
it is a text type.
--
Dave Hargis, Microsoft Access MVP

I jump the gun there ithe currency came back but the N/A is gone now.

I am using the Nz Function it work fine but my Currency is now gone how can i
get the currency format to return?

SELECT qrySubCost.Division, qrySubCost.Carrier,
Nz([Cumulative_Impact],"N/A") AS CumulativeImpact
FROM qrySubCost;

Joe

Does this help?
SELECT qrySubCost.Division, qrySubCost.Carrier,
Nz(Format([Cumulative_Impact],"Currency"),"N/A") AS CumulativeImpact
FROM qrySubCost;

 

[fredg]

I jump the gun there ithe currency came back but the N/A is gone now.

I am using the Nz Function it work fine but my Currency is now gone how can i
get the currency format to return?

SELECT qrySubCost.Division, qrySubCost.Carrier,
Nz([Cumulative_Impact],"N/A") AS CumulativeImpact
FROM qrySubCost;

Joe

Does this help?
SELECT qrySubCost.Division, qrySubCost.Carrier,
Nz(Format([Cumulative_Impact],"Currency"),"N/A") AS CumulativeImpact
FROM qrySubCost;

Try it this way.

SELECT qrySubCost.Division, qrySubCost.Carrier,
qrySubCost.[Cumulative_Impact]
FROM qrySubCost;


Then on the Design grid, right click below the criteria rows.
Select Properties.
Set the [Cumulative_Impact] field's Format property to:
$#,###.00;-$#,###.00;$0.00;"N/A"

Save the changes.

This is what I get. The first row (N/A) is null
N/A
$0.00
$5.00
-$10.00

In Access help, look up
Format property + Number and Currency datatypes
to see why this works.