By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,753 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

iif statement

P: 78
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:
Expand|Select|Wrap|Line Numbers
  1. =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?
Nov 18 '11 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 15k+
P: 31,709
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).

Expand|Select|Wrap|Line Numbers
  1. =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.
Nov 18 '11 #2

P: 78
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!

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?
Nov 23 '11 #3

Expert Mod 15k+
P: 31,709
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1. =Format(Nz(DLookup("[Car1]", "[GARAGE]"), 0), "Yes;Yes;No")
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.
Nov 23 '11 #4

Post your reply

Sign in to post your reply or Sign up for a free account.