iif statement

[wisni1rr]

Here is my situation:

I have a text box control. I want to check a field in my database and compare it to 0. The field is Car1 in the GARAGE table. If the value of Car1 is "0" the the text box control should display "NO" if not then "YES" The statement I have is as such:

=IIf([GARAGE]![Car1]=0,"NO","YES")

The control is on a report and when I run the report it prompts a paramater query on GARAGE. Leaving it blank and hitting OK generates the report but the textbox control displays #Type!

Am I messing up the syntax?

[NeoPa]

Controls on a form or report don't have direct access to the whole database. Only to the Record Source of the form/report.

What you can do though, is use a DLookup() call if you need to (Better would be to arrange the Record Source such that the required value is included if it makes sense to).

=Format(DLookup("[Car1]", "[GARAGE]"), "Yes;Yes;No")

NB. Your example only referenced the field in the table. It didn't indicate which record to use.

[wisni1rr]

Thank you for your help.

The records to use will be based on a query or filter. (I haven't decided exactly how I'm going to develop this.) However, as the report stands, it includes all records.

I tried the DLookup() function and had this as a result on my report:

Car1: 364e0

The field in question is included in the Record Source.

[Car1] is a number data type field. It is used to store how many cars the first garage can hold. It may be left blank or contain a 0 in addition to the format of '##.#'.
I want to return a text string of "YES" if the value is greater than zero on that particular report I am working on. The report is a summary style report where I have dataset totals (Mean, Median, ect.)in the header section and the records used to calculate these totals in the detail section.

Hope that helps to identify the issue.

As always, thank you for all the help you provide to me and others!

Also,
If it would be a better choice I can add a field to the table. I know that would be easier. Would it be better on performance?

[NeoPa]

Wisni1rr:

Car1: 364e0

Interesting. You also say the field is numeric, yet this indicates otherwise. The DLookup() code I provided (or technically the Format() code) would have converted any non-zero numeric value into the string "Yes" for you. It wouldn't have handled a Null though. For that you need :

=Format(Nz(DLookup("[Car1]", "[GARAGE]"), 0), "Yes;Yes;No")

Wisni1rr:
If it would be a better choice I can add a field to the table. I know that would be easier. Would it be better on performance?

There are various reasons why this would be a bad idea. None of them is related to performance though. For a better understanding of why see Database Normalisation and Table structures.