Access 2000 IIF statement

[GMC]

Hello all,

I am having problems trying to figure out how to write a statement that only
shows a column if there is something in the column. Right now I get a bunch
of blank records and then the one record with the stuff in it that I need.
I have a report that works off a query. The query works off a table and has
a field in it called "other_new_specify" and one with
"other_pending_specify". What I want to do is have the field show up on the
report only when there is something populating it those two fields.

The following statement gives me errors

Iif(IsNull(other_pending_specify]="" ""[other_pending_specify])

Any suggestions?

Thanks

 

[Guest]

Hi GMC,

Am I right in thinking that you need to set the criteria in a query to show
only records that are populated for the two fields you've mentioned? Have
you added the fields to your query grid? If so, then have you tried asking
for Not Null in the critera line?

The IIF statement you're writing appears to be incomplete. It is my
understanding that usually you would use the IIF in a query to generate data
in a new column, rather than to set criteria. If you are using the IIF in a
new column to generate data, and I have misinterpreted your example, please
forgive me. In this case, you will need to include your condition, true
value and false value, so, in a new column in your query grid, it looks like
this:

New column name:=IIF(your condition ie what you are comparing, what to do if
the condition is found to be true, what to do if the condition is found to be
false)

The separating commas are important as they show Access firstly what you are
looking for (often known as the condition or logical test), then after the
first comma, what to do if it finds it, and after the second column, what to
do if it doesn't. The colon between the new column name and the IIF just
tells Access where the new column name ends and the formula begins.

I hope this helps and hasn't confused you. Let me know how you get on.

Good luck,

Karen

 

[John W. Vinson]

Hello all,

I am having problems trying to figure out how to write a statement that only
shows a column if there is something in the column. Right now I get a bunch
of blank records and then the one record with the stuff in it that I need.
I have a report that works off a query. The query works off a table and has
a field in it called "other_new_specify" and one with
"other_pending_specify". What I want to do is have the field show up on the
report only when there is something populating it those two fields.

The following statement gives me errors

Iif(IsNull(other_pending_specify]="" ""[other_pending_specify])

Well, yes, that would error all over the place... IsNull has an open paren but
no close paren, and the fieldname has a right bracket but no left bracket.

If you just don't want to see those records where other_pending_specify is
NULL, use a query criterion of IS NOT NULL on the field. No IIF is needed.

John W. Vinson [MVP]

 

[GMC]

OK, will try tomorrow and I see that error.

Thanks.

 

[John W. Vinson]

Iif(IsNull(other_pending_specify]="" ""[other_pending_specify])

IIF takes three arguments. If the first one is True, the function returns the
second argument; if it's False, it returns the third argument.

IsNull takes one argument and returns True if that argument is NULL, and False
if that argument contains data.

I'm not sure what you wanted this function to return - it *looks* like you
want it to return an empty string if other_pending_specify is NULL and return
the value in other_pending_specify if it's not, but that is precisely what
you'ld see if you just referenced other_pending_specify directly.

John W. Vinson [MVP]