MS Access Query date range when last date not found

user_6483724 0
Viewed 92

I have the following update query in MS Access 2013

UPDATE WXObs SET WXObs.SnowFlag = 1     
WHERE (((WXObs.StationID) ="451409") And(
(WxObs.ObsDate) Between #1/3/2003# AND #3/29/2003# OR
(WxObs.ObsDate) Between #11/16/2003# AND #5/7/2004# OR
(WxObs.ObsDate) Between #10/30/2004# AND #4/30/2005#));

This works until the end date in the range is not found. For instance, if 5/7/2004 is not in the data set, then the update continues to the next end date, in this case 4/30/2005. I would prefer it ended on the last date in the range. For instance, if the data ended on 4/21/2004, that would be last field updated between 11/16/ and 5/7/2004. The query would then continue to update again beginning on 10/30/2004.

I have tried < and <=

Thanks

ms-access-2013 ms-access sql
1 Answers

You're missing some parentheses that are affecting the evaluation order, causing the behavior that you're reporting.

What you want is for each of the BETWEEN portions to be evaluated completely before the OR option is evaluated, and you need to make sure that evaluation is done by surrounding the BETWEEN expressions in parentheses to guarantee the evaluation order.

This should correct it (untested, as you've not provided the test data necessary to create a test case).

UPDATE WXObs SET WXObs.SnowFlag = 1     
WHERE 
  (WXObs.StationID ="451409") 
And
  (
   (WxObs.ObsDate Between #1/3/2003# AND #3/29/2003#) OR
   (WxObs.ObsDate Between #11/16/2003# AND #5/7/2004#) OR
   (WxObs.ObsDate Between #10/30/2004# AND #4/30/2005#)
  );
Ken_White_625760
Related