Data mismatch problem

[RobertM]

Here's the IIf statement that's giving me trouble:

CCur(IIf([QryBSum-SSum]![ShareVariance]=0,[VarWComm],"N/A"))

If I take the CCur out as shown below I get the N/A where I want it, but
it's not formatted as currency:

IIf([QryBSum-SSum]![ShareVariance]=0,[VarWComm],"N/A")

I put CCur in to format it as currency, but now I get #Error where I want to
get N/A.

I just want the query to show that values as either currency of N/A.

Any help is greatly appreciated.

 

[Van T. Dinh]

Have you tried:

IIf([QryBSum-SSum]![ShareVariance]=0,Format([VarWComm], "Currency"),"N/A")

 

[RobertM]

Van You're the best. Thank you very much.

Robert

Have you tried:

IIf([QryBSum-SSum]![ShareVariance]=0,Format([VarWComm], "Currency"),"N/A")


--
HTH
Van T. Dinh
MVP (Access)

Here's the IIf statement that's giving me trouble:

CCur(IIf([QryBSum-SSum]![ShareVariance]=0,[VarWComm],"N/A"))

If I take the CCur out as shown below I get the N/A where I want it, but
it's not formatted as currency:

IIf([QryBSum-SSum]![ShareVariance]=0,[VarWComm],"N/A")

I put CCur in to format it as currency, but now I get #Error where I want
to
get N/A.

I just want the query to show that values as either currency of N/A.

Any help is greatly appreciated.

 

[John Vinson]

Here's the IIf statement that's giving me trouble:

CCur(IIf([QryBSum-SSum]![ShareVariance]=0,[VarWComm],"N/A"))

If I take the CCur out as shown below I get the N/A where I want it, but
it's not formatted as currency:

IIf([QryBSum-SSum]![ShareVariance]=0,[VarWComm],"N/A")

I put CCur in to format it as currency, but now I get #Error where I want to
get N/A.

How many dollars does "N/A" equal?

It can't be converted to Currency using CCur() beacuse it's not a
valid value for the Currency datatype.

I just want the query to show that values as either currency of N/A.

Move the CCur() inside the IIF:

IIF([QryBSum-SSum]![ShareVariance]=0,CCur([VarWComm]),"N/A")

or, probably better, if VawWComm is a numeric field type,

IIF([QryBSum-SSum]![ShareVariance]=0,Format$([VarWComm],"Currency"),"N/A")

John W. Vinson[MVP]