IIF Statement in report - Textbox control source

[AnitaPita]

I have been trying to figure this out all day. Any help would be greatly appreciated!

I have the following IIF statement in the control source of a textbox on a report:

=IIF([StartDate]=#8/15/2011#,[EmpName],"")

I simply want it to look at a table that has Employee information, and if the start date = 8/15/2011, then I want it to return the employee name.
I have the record source of the report set to that same employee information table. When I view the report, it doesn't give me an error or anything, it's just blank.

Any ideas why this isn't working? Could the formatting of the dates have anything to do with it? Although, I tried to change the date formatting and it still didn't pull. The text box is in the detail section of the report. There are no spelling errors etc. in the table or field names. I have done statements like this is the past and had no problem. Ack!

Thanks!

 

[pbaldy]

Formatting shouldn't matter, but if the value in the table has a time element it would likely never match, so you'd get the ZLS.

 

[AnitaPita]

I ended up building this on my home computer with the same tables, reports, everything and the IIF statement worked, although it only pulled 1 record with that date (the first one in the table). Any idea why it wouldn't pull the others?

I have Access 2007 on both my home and work computers. Only difference is my home computer has XP and work one is running
Windows 7....

 

[pbaldy]

Does the date value in the table include a time? Can you post the db?

 

[AnitaPita]

Hi again, no the date doesn't have a time...I originally had it formatted as "short date" then I even reformatted it to a mmddyy and it still wouldn't pull it. On my home computer it pulls but only gets the first record. The table I have is small, has only 20-30 records right now...
I can create this on my home computer but problem is, It is going to live on a shared drive here at work with everyone in the department using it, I'm sure if I put it out there the IIF isnt going to work :/

 

[pbaldy]

My gut is that it's not the IIf() and it's not what computer it's built on. I just tested and that function worked fine. I believe it's data related. It doesn't matter how the field is formatted, it could still have a time in it. How is it populated? I take it you can't post the db?

 

[AnitaPita]

Hi how do I post the db? Sorry, I've never done that before. Thanks!

 

[pbaldy]

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[AnitaPita]

My table is linked to a form that is being used for data entry...so we can track out of the office/vacation time. So an employee can have more than one record with their name. So one entry could be John Doe for Vacation on a certain date then John Doe again for Floating holiday on a different date. I intentionally set the table to allow duplicate records....

 

[rxm01l]

I have a user Prod Table that contained many records in my table. One of the field in the record I have Over Time Field "Y or N "check box". I'd like to have a IIF statement put in to my control box on the form to calculate User Prodcutivity if there is OT exist in record? See attach screen.. Will this be possible..?Thanks in advance..

 

[pbaldy]

=IIf(OT = True, Something, Whatever)