Union Query not returning second select results

In the iif statements in both SELECT statements, you have not include the FALSE criteria,  ie. what is supposed to happen if the [startdte] is not between the two  test dates?

I am sorry for not replying earlier but had to leave the office for medical reasons.

Nothing needs to happen if it is false. I am using the "1"  to mark the records so that I can perform summary calculations later.

I do not believe the false condition of a IIF() function is optional, but then Jet, the db engine in Access is sometimes too lazy about raising flags .  If you do not want anything to happen, try inserting ,Null after each of the four ,1's in your query as in:

IIf([startdate] Between [startofweek] And [endofweek],1,Null)

Does that change anything?

I still get the same results. I also tried using 0 instead of Null.

The funny thing is that the select staments work by themselves. They just don't work as part of the union. Only the first select works regardless of which statement I put first.

I'm wondering if this has somnething to do with the way that I built the source tables.

That is good to know about the db engine not always raising errors.

SELECT 
b.ID, 
a.ID, 
a.StartofWeek, 
a.EndofWeek, 
b.startdate, 
IIf([startdate] Between [startofweek] And [endofweek],1) AS S1_IN,
b.s2StartDate, 
IIf([s2startdate] Between [startofweek] And [endofweek],1) AS S2_IN
FROM ref_dates_week a
LEFT JOIN que_rpt_Schedule_s1 b
ON a.ID = b.Startweek;
 
UNION ALL
 
SELECT 
d.ID, 
c.ID, 
c.StartofWeek, 
c.EndofWeek, 
d.startdate, 
IIf([startdate] Between [startofweek] And [endofweek],1),
 qd.s2StartDate, 
IIf([s2startdate] Between [startofweek] And [endofweek],1) 
FROM ref_dates_week c 
LEFT JOIN que_rpt_Schedule_s1 d
ON c.ID = d.S2_Startweek;
 
This is how I like to lay out the query when I'm having difficulty troubleshooting - using aliases a and b make it a lot easier to spot errors.  The first thing I noticed is that the fields in IIf() statements are not qualified with their table names if they are not parameters.  
 
Remember, a UNION query takes the field names and datatypes from the first SELECT query so there is not point in providing the AS <fldName> in the subsequent SELECT query(s).  Does the query as I have modified it in this snippet above run?

Select allOpen in new window

I should have added the nulls:

SELECT 
b.ID, 
a.ID, 
a.StartofWeek, 
a.EndofWeek, 
b.startdate, 
IIf([startdate] Between [startofweek] And [endofweek],1,Null) AS S1_IN,
b.s2StartDate, 
IIf([s2startdate] Between [startofweek] And [endofweek],1,Null) AS S2_IN
FROM ref_dates_week a
LEFT JOIN que_rpt_Schedule_s1 b
ON a.ID = b.Startweek;
 
UNION ALL
 
SELECT 
d.ID, 
c.ID, 
c.StartofWeek, 
c.EndofWeek, 
d.startdate, 
IIf([startdate] Between [startofweek] And [endofweek],1,Null),
 qd.s2StartDate, 
IIf([s2startdate] Between [startofweek] And [endofweek],1,Null) 
FROM ref_dates_week c 
LEFT JOIN que_rpt_Schedule_s1 d
ON c.ID = d.S2_Startweek;
 

Select allOpen in new window

I actually lay out my sql the same way but Acess seems to condense the code whenever I leave the query. I am not seing anything in your code different then mine. I did paste your code and I am still having the same issue.

Thanks for you help but I think that I will  try to create a work around. The functionality is not worth spending too much more time on.

OK, but I am going to try to get some 'fresh' eyes on this.

Did you try to save both queries and create the UNION like:

select * from qrySaved1
UNION ALL
select * from qrySaved2;

Nic;o)

Nico5038,
I had tried to execute the union the way you suggested and I got the same results. This is the strangest problem. I have used the union functionality many times and I have not had this problem. I am thinking that it must have something to do with the relationships between the tables.

Thanks for the suggestion

Hi Nico:  any comment about including/not including the False argument in an Access query using the IIF() function?

Thank you! I should have known it would be something simple.

Good catch.  I've been looking at this thing for over a week!

Hmm, my test query works flawlessly with the UNION ALL with and without the intermediate ";" , so I assume it's something in the data when the previous tip doesn't work.
Can you create a sample .accdb with (part of) the tables and the query and attach that to the comment so GRayL and I can have a look?

Nic;o)

As an old veteran expert you should know it's always in the details Ray <LOL>
Still puzzled why the ";" doesn't interfere for me in the accdb database, from the "old" .mdb databases I know you get a message when there's something behind the final ";".

OK, glad it's now working hragape, success with your application !

Nic;o)

I knew the devil was in the details, and I knew a 'fresh set of eyes' was the answer.  Thanks, Nico and good luck with the application hragape.