One Query Two Date Ranges

[natep26]

I have a query that pulls up data from two different plants with inspection dates.

I'd like to place a conditional date range in the criteria.

I tried IIf([assetarea]="West Plant",Between #6/16/2008# And #12/15/2008#,Between #2/20/2008# And #8/19/2008#)

The query returns with no records. I have verified that there is data between these date ranges for both plants.

Any help would be appreciated.

Thanks

 

[redneckgeek]

I would use a UNION query for this:

SELECT ASSETAREA, SUM(FIELD2)
FROM (
SELECT ASSETAREA, FIELD2, ...
FROM TABLE1
WHERE ASSETAREA="WEST PLANT"
    AND INSPECTIONDATE BETWEEN #6/16/2008# And #12/15/2008#
UNION
SELECT ASSETAREA, FIELD2, ...
FROM TABLE1
WHERE ASSETAREA <>"WEST PLANT"
    AND INSPECTIONDATE BETWEEN #2/20/2008# And #8/19/2008#) RS
GROUP BY ASSETAREA

 

[Brianwarnock]

Your doing this in the query grid?
if so on one row of criteria under assetarea put "West Plant" and under the Datefield put Between #6/16/2008# And #12/15/2008#
on the next row just put
<>"West Plant" and Between #2/20/2008# And #8/19/2008#

This will AND the first 2 criteria then OR those with the last.

Brian

My system hung on the site as I realised I had made a mistake hope it doesn't cause a problem.

 

[redneckgeek]

heh...
Brian's solution is much easier than mine. Sometimes I like complexity too much.

 

[natep26]

Thanks Brian. I've used that method before fairly often. Not sure what made me forget it this time. Thanks for the friendly reminder.