Calculated Query - property sheet not displaying option for Currency

[debswebs]

I am trying to create a calculated query using an "IIF" statement in the "FIELD":

Natl Assess: (IIf([Let Date]>#12/31/2010#,([Bid Price]*0.55)/1000,"0"))

This returns the correct value, but I want it to display as currency. My property sheet for this field does not have a "currency" option.

Should I put the "Iif" statement in "criteria"?

 

[spikepl]

1. Showing queries directly is not a good practice - if shown in a form, you can format any field to your heart's desire and also protect the data.
2. Mixing datatypes in a column works in Excel, but doesn't work well in Access - you IIF delivers a number or a string - not good. Decide on one and stick with it - Currency, I presume. You can always cast the output to curency, just in case : CCur(IIF....)

 

[debswebs]

OK, I'm am new to Access, so I don't understand your reply??

 

[spikepl]

([Bid Price]*0.55)/1000 is a number
"0" is a string
0 is a number

 

[debswebs]

I understand number and text string . . .

Are you saying I can't get the results I want in a query?

(I am trying to calculate a fee based on the Project bid price if the project was done prior to 2011)

 

[boblarson]

If you are using the query output directly and not in a form or report (which is what spikepl was referring to using the controls on them to format instead of directly in the query), then you can use:

Natl Assess: FormatCurrency(IIf([Let Date]>#12/31/2010#,([Bid Price]*0.55)/1000,0), 2)

 

[spikepl]

If you understand number and stirng then why have you made a formula that outputs one or the other from time to time? I think Access discoivered that, and makes it a string in both cases, whioch is not a Currency or Number that can be formatted simply. Change your IIF so it always delivers a Currency result.

I am not saying that you cannot get the results you want in a query. I am saying that formatting of output should in general be done in forms (or reports) because you should not show query output to the user directly. Make a form using the query as record source and with Datasheet view, and in that form you can format fields as desired.

 

[debswebs]

Thank you, BobLarsen!! That is exactly what I needed. It is working now.

 

[debswebs]

Thank you, Spikepl, for that explanation. I am still trying to learn how and where to use calculated fields, so that helps!!

I appreciate your help!