IIF Statement in Text box Control Source

[AnitaPita]

Hi,

I am having problems with this IIF statement...

=IIf([OutOfOffice]![StartDate]=#8/1/2011#,[OutOfOffice]![EmpName])

The text box is in a report and I am simply trying to look at the date field in a table and if its 8/1/2011 I want it to pull the employee's name. When I use this I get the #Name? error. Any help would be appreciated...thank you!

 

[revans]

You need an else part of the iif. Try

=IIf([OutOfOffice]![StartDate]=#8/1/2011#,[OutOfOffice]![EmpName],"")

If that doesn't work check your spelling and/or your data.

hth,

Rich

 

[AnitaPita]

Thanks Rich,
I entered the else part, and when I try to view the report it gives me the "enter parameters value" box. I went back and double checked all my field names, spelling and data in the table and everything seems right....I am wondering if this has something to do with the formatting of the date? I have the date in the statement though exactly as it appears in the table.....

 

[boblarson]

In order to use those fields like that, they do need to exist in the report. Also, the name of the text box that uses this as its control source can't be named the same as any of the fields in the IIF function. And last, but not least, it is going to be dependent upon being in the right location. Where do you have this text box (Report Header/Footer, Page Header/Footer, Detail Section, Group Header/Footer?

 

[AnitaPita]

Hi Bob,

The text box is in the Detail section. Should I have it in another location? I added the fields to the report, do those need to be put in a certain place? The name of the textbox isn't the same as anything else so I think I'm OK there....

 

[boblarson]

Hi Bob,

The text box is in the Detail section. Should I have it in another location?

No, it should be fine there.

I added the fields to the report, do those need to be put in a certain place?

They have to be in the query, or select statement, that is used for the report's RECORD SOURCE. You can't just add them onto the report. They need to be part of the record source. So, if they are from another table and you have a different table set as the form's record source, you can't just drag and drop them onto the report from the list of related tables and fields (one of the confusing things about the way Access 2007 and 2010 show the field list now).

 

[AnitaPita]

I see, yes I have the control source set to a table where all those fields are located...I've double checked all the names of the fields etc. to make sure everything is correct/no misspellings etc. I don't get an Error where the text box is, it's just blank when I try to view the report....