IIF is driving me nuts...

[Randix]

I have a report that has multiple memo fields. In one of the memo fields called "lic", my "goal" is to not have it print if it's empty. No matter what I do, Access reports an error which seems non-existent to me...for example, this is what i put in the report...

IIF(IsNull([lic]),"","Update")

In response, Access reports an error and says:

Extra ) in query expression '[IIF(IsNull([lic]),"","Update")]'

I just don't see what the problem is...can anyone explain what's happening here?

 

[Sorrells]

Randix,

Yes, the IIF is tricky and so I might not help you at all but here are some thoghts.

1) the error may be ambigious, that is not the real problem.

2) memo fields are trcky to work with, there may be a problem using this data type in the IIF statement. I don't know, just a possibility.

3) You have 'lic' delimited by brackets []. This indicates a field to me in a table. If this is so, you may need to include the entire reference - [table name]![field]

4) Why do you have "Update" for the false part. If the control/field is not null, don't you want to see the contents?

5) You might reverse the logic to see what happens, e.g. IIF NOT ISNULL(.....

6) If this is in fact a control on the report (lic) then you should be sure it's caption is different from the name and use the caption in the IIF statement.

7) Did you try this: IIF Isnull(txtlic,,txtLic) that is, just leave the true action empty to see what response you get.

Good luck,

Kaniksu

 

[Rich]

Sorry I don't understand your logic if the memo field is empty how can it print?

 

[Rich@ITTC]

Hi Randix

I think I get what you want to achieve - if there is some data in the memo field "lic" then the report should display "Update" in that field. When null then nothing is displayed.

I think the error may be in the name of the field from your query being "lic" and your text box/memo box on your report also being called "lic". Access very kindly names text boxes by the same name as the fields they draw upon, and then it finds it tricky to intrepet which is which!

I would delete the text box and create a new unbound text box. Name this text box "txtlic". Then in the control source put:

=IIf(IsNull([lic]),"","Update")

HTH

Rich Gorvin

[This message has been edited by Rich@ITTC (edited 08-03-2001).]

 

[Randix]

Rich...your solution was right on the mark. By using an unbound text box, it works fine.

Naturally I have one more related question...not sure if this is obvious or not, but if I have 4 unbound text boxes, with about 1/2" space between each one, one after the other, vertically, and there's no text in the third one, although the third box does not appear (as it should using the IIF statement), there nevertheless remains extra space between the 2nd box and the 4th box. Any suggestions? The grow/shrink settings don't do it.