Why can I not format a calculated field in a query to Currency?

[Guest]

For some reason once I use an IIf statement for a calculated field in a
Query, the dropdown menu for format in the Field Properties menu no longer
gives me any options for the data type. I want it to be in currency but
instead the query results give me long decimals that are not what I want to
see. At the very least they need to be rounded off to two decimal places.

Also I would like to sum the results of the IIf statement in a report and I
think since I am not able to specify a data type I keep getting the error
message of a data type mismatch or the expression being too complicated (when
it is a simple expression of =sum([INS FEE]).

Any help would be greatly appreciated.

 

[Allen Browne]

Typecast the calculation, e.g.:
CCur(Nz(... ,0))
where the ... represents the calculation you already have.

More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

 

[Guest]

Thank you Allen,

Your solution almost worked. the problem is, however, like you said at your
webpage, "These conversion functions cannot handle Null." You see, my IIf
statement involves performing calculations on only certain records in the
Query results, thus leaving all of the other results Null. When I use your
typecast it gives me Error messages for any record that the calculation isn't
being performed on....

Typecast the calculation, e.g.:
CCur(Nz(... ,0))
where the ... represents the calculation you already have.

More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

For some reason once I use an IIf statement for a calculated field in a
Query, the dropdown menu for format in the Field Properties menu no longer
gives me any options for the data type. I want it to be in currency but
instead the query results give me long decimals that are not what I want
to
see. At the very least they need to be rounded off to two decimal places.

Also I would like to sum the results of the IIf statement in a report and
I
think since I am not able to specify a data type I keep getting the error
message of a data type mismatch or the expression being too complicated
(when
it is a simple expression of =sum([INS FEE]).

Any help would be greatly appreciated.

 

[John Spencer]

If I recall correctly, you can drop the typecasting (CCUR) once you have set
up the formatting you want. Again IF I recall correctly, Access will not
remove the formatting you have set.

Thank you Allen,

Your solution almost worked. the problem is, however, like you said at
your
webpage, "These conversion functions cannot handle Null." You see, my IIf
statement involves performing calculations on only certain records in the
Query results, thus leaving all of the other results Null. When I use your
typecast it gives me Error messages for any record that the calculation
isn't
being performed on....

Typecast the calculation, e.g.:
CCur(Nz(... ,0))
where the ... represents the calculation you already have.

More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

For some reason once I use an IIf statement for a calculated field in a
Query, the dropdown menu for format in the Field Properties menu no
longer
gives me any options for the data type. I want it to be in currency but
instead the query results give me long decimals that are not what I
want
to
see. At the very least they need to be rounded off to two decimal
places.

Also I would like to sum the results of the IIf statement in a report
and
I
think since I am not able to specify a data type I keep getting the
error
message of a data type mismatch or the expression being too complicated
(when
it is a simple expression of =sum([INS FEE]).

Any help would be greatly appreciated.