CODEWITHSUNDEEP
sqlms-accessms-access-2013
pcLoadLetter
pcLoadLetter

Reputation: 27

MS Access Query date range when last date not found

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

Upvotes: 2

Views: 151

Answers (1)

Ken White
Ken White

Reputation: 125669

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#)
  );

Upvotes: 1

Related Questions