Hi,
I have stuck on this nested IIF case. Point is that I want to select records from 1 last working day skipping weekends and hollidays and starting calculating -2 days from entered [a] date.
I have one Bdaily table and Calendar table with marked days (Day=7 means it is non working day)

This is code that I set till now but I'm receiving an msg "At most one record can be returned by this subquery"

SELECT * INTO TEMP_3DAYS
FROM BDaily
WHERE DAY=(SELECT Date
FROM Calendar
WHERE Date=
IIf((Date=DateAdd('d',-2,[a]) And (Day<>7) ),Date,
(SELECT Date FROM Calendar as a where a.Date=IIf((a.Date=DateAdd('d',-3,[a]) And (a.Day<>7) ),a.Date,
(SELECT Date FROM Calendar as b where b.Date=IIf((b.Date=DateAdd('d',-4,[a]) And (b.Day<>7) ),b.Date,DateAdd('d',-1,[b.Date]))
)))));

I can't see why there is more dates results instead of one?

Instead of using subqueries, why not use multiple IIF statements, such as

SELECT * INTO TEMP_3DAYS
FROM BDaily
WHERE DAY=(SELECT Date
FROM Calendar
WHERE Date=
IIf((Date=DateAdd('d',-2,[a]) And (Day<>7) ),Date,
Iif((Date=DateAdd('d',-3,[a]) And (Day<>7) ),Date,
IIf((Date=DateAdd('d',-4,[a]) And (Day<>7) ),Date,DateAdd('d',-1,[Date])))

I started on this way but this was last version as lots of reasons went thru my mind...

Thank you on this.
Still main problem with msg "at most one record can be returned by this subquery" stay present.

Strange thing is that for one date value I get result back and for other (one day less) I get this msg?

I started on this way but this was last version as lots of reasons went thru my mind...

Thank you on this.
Still main problem with msg "at most one record can be returned by this subquery" stay present.

Strange thing is that for one date value I get result back and for other (one day less) I get this msg?


Try executing the SubQuery all by itself. When you see what is being returned, you might get a new perspective on the problem.

I did like this:
SELECT Date
FROM Calendar
WHERE Date=
IIf((Date=DateAdd('d',-2,[a]) And (CDay<>7)),Date,
IIf((Date=DateAdd('d',-3,[a]) And (CDay<>7)),Date,
IIf((Date=DateAdd('d',-4,[a]) And (CDay<>7)),Date,DateAdd('d',-1,[Date]))));

in Calendar situation is next:
Date CDay
13.1.2010 3
14.1.2010 4
15.1.2010 5
16.1.2010 7
17.1.2010 7
18.1.2010 1
19.1.2010 2
20.1.2010 3


and results were :
for 19.1.2010 result> 15.1.2010
for 18.1.2010 result> 15.1.2010, 14.1.2010
for 17.1.2010 result> 15.1.2010, 14.1.2010, 13.1.2010

result should be only 15.1.2010 in all three cases,
why query continue calculating for 17.1. when first date is 15.1<>7?

Anyone any idea?:(