Combine IIF

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Combine IIF

Post by D Willett »

The below formula works great in a holiday planner and shows "Booked" on a report if UnitID is booked as a holiday.

Code: Select all

=IIf(DCount("*","tblAttPeriod","UnitID=" & [Code] & " AND [Forms]![frmAttendance]![txtDateFrom] Between [FromDate] And [ThruDate]")>0,"Booked","")
I need to combine it with the below:

Code: Select all

=IIf(DCount("*","tblHoliday","HolidayDate" AND [Forms]![frmAttendance]![txtDateFrom] Between [FromDate] And [ThruDate]")>0,"Bank Holiday","")
I have a separate table than tblAttPeriod called tblHoliday, this has two columns 'HolidayName' and 'HolidayDate'.
The combinated code should check:

Has UnitID booked a holiday between [FromDate] and [ThruDate], if true then show "Booked" on the report, also if there is a date in tblHoliday then show "Bank Holiday"

Is this doable?

Cheers
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Combine IIF

Post by HansV »

Try

=IIf(IsNull(DLookup("HolidayDate","tblHoliday","HolidayDate=Forms]![frmAttendance]![txtDateFrom]")),IIf(DCount("*","tblAttPeriod","UnitID=" & [Code] & " AND [Forms]![frmAttendance]![txtDateFrom] Between [FromDate] And [ThruDate]")>0,"Booked",""),"Public Holiday")

It might not do exactly what you want - if so. let me know.
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Combine IIF

Post by D Willett »

Unfortunately not Hans. The screen shot attached shows Dawn had booked Friday 4th June so that is correct. However that week the Monday 31st May was a public holiday which hasn't shown up as "Public Holiday".
You do not have the required permissions to view the files attached to this post.
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Combine IIF

Post by HansV »

What is the name of the control on the form that displays Monday-31-May-2021?
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Combine IIF

Post by D Willett »

It's a report Hans.
ScreenHunter_224.jpg
You do not have the required permissions to view the files attached to this post.
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Combine IIF

Post by HansV »

Sorry, I cannot see the complete control source of the text boxes on the left hat display the dates.
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Combine IIF

Post by D Willett »

=[Forms]![frmAttendance]![txtDateFRom]
=[Forms]![frmAttendance]![txtDateFRom]+1
=[Forms]![frmAttendance]![txtDateFRom]+2
etc

If this is what you meant Hans.

Applying your code brings #ERROR throughout the report.
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Combine IIF

Post by HansV »

I guess I'd have to see a copy of the database...
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Combine IIF

Post by D Willett »

Thanks Hans, always appreciate the help.
Cheers ...

Dave.

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Re: Combine IIF

Post by burrina »

Just curious, is your code allowing for Nulls? i.e. Nz ?
Are you missing any references?
Generally this error means no records.
Just my 2 cents.
Hth