Query with IIF Function

[MSdove1]

Hello all,

I am somewhat of an access novice. I am trying to create a calculated field in a query using the IIF function. I am looking at a column with costs in it. In the query, I want the column title to be "Sponsored?" and if the value in the field is $0.00 I want it to return "No", otherwise "Yes". The name of the column I am pulling from is Sponsor Cost

Here is what I attempted: Sponsored?: IIf([Sponsor Cost]="$0.00","No","Yes")



I keep getting #Error when I run the query. Thoughts on what I am doing wrong?

[aytee111]

If Sponsor cost is a number data type then it wants numbers from you - as in: If=0 then... They way you have it typed above is for text fields.

[MSdove1]

The sponsor cost is a currency. So forgive my ignorance, but how would I re-write the function correctly for a currency?

[Bulzie]

What is the error? In the table as aytee111 suggests, what is the data type of [Sponsor Cost]? Does the value in that field have the $ in it?

[aytee111]

Yes, currency is a format (what it looks like, not how Access stores it in the table), not a data type.

[MSdove1]

What is the error? In the table as aytee111 suggests, what is the data type of [Sponsor Cost]? Does the value in that field have the $ in it?

Yes, the value has the $ in it. Here is a snip of the column in question. So, I want to write a query such that any time the value in this column is $0.00, the result is "No" but if it is anything other than $0.00, the result is "Yes". Is that helpful in providing the information needed to help?

[pbaldy]

Yes, currency is a format (what it looks like, not how Access stores it in the table), not a data type.

Actually it is also a data type.

@MSDove, try:

Sponsored?: IIf([Sponsor Cost]=0,"No","Yes")