Solved Query help on Between # and #

[wjm821]

Need a little help. I have a query based off a table.
SELECT tblLocker.FieldDate, DateAdd("d",180,[FieldDate]) AS ExpireDateLocker, ([ExpireDateLocker])-Date()
AS LockerExpire, tblLocker.L6m, tblLocker.LockerNumber, tblLocker.LockerAssignTo
FROM tblLocker
WHERE (((tblLocker.L6m)=True));

This works, pulls everything L6m true, FieldDate (7/20/2023), Expiredate (1/16/2024), LockerExpire (6)days till expired.

What I'm trying to do is create a popup message when the LockerExpire is between 1 and 14.

Thank you in advanced

 

[theDBguy]

Hi. Congratulations on your first post. Try modifying your WHERE clause to something like:

WHERE tblLocker.L6m=True AND Date() Between tblLocker.FieldDate+166 AND tblLocker.FieldDate+179

(untested)
Hope that helps...

 

[wjm821]

WHERE tblLocker.L6m=True AND Date() Between tblLocker.FieldDate+166 AND tblLocker.FieldDate+179

That did the trick!!!! Question what is +166 and +179 for?

 

[theDBguy]

That did the trick!!!! Question what is +166 and +179 for?

Basically, that's 14 days and 1 day before the due date. Cheers!

 

[Galaxiom]

Hi. Congratulations on your first post. Try modifying your WHERE clause to something like:

WHERE tblLocker.L6m=True AND Date() Between tblLocker.FieldDate+166 AND tblLocker.FieldDate+179

(untested)
Hope that helps...

That is not a good query because it isn't SARGable. The criteria should involve operations on the current date that are calculated once, rather than being applied to FieldDate which requires every record to be processed and cannot use the index on FieldDate.

The difference in performance won't be evident with small numbers of records but certainly will as numbers of records increase.

 

[theDBguy]

That is not a good query because it isn't SARGable. The criteria should involve operations on the current date that are calculated once, rather than being applied to FieldDate which requires every record to be processed and cannot use the index on FieldDate.

The difference in performance won't be evident with small numbers of records but certainly will as numbers of records increase.

I don't disagree. Can you show us an alternate query that fits the requirements and also sargable? I can't picture it right now.

Sent from phone...

 

[theDBguy]

I don't disagree. Can you show us an alternate query that fits the requirements and also sargable? I can't picture it right now.

Sent from phone...

This won't be sargable either, right?

WHERE DateAdd("d", 180, [DateField]) Between Date+1 And Date+14

Sent from phone...

 

[CJ_London]

Would have thought this is sargable (assumes I’ve got the maths right

WHERE [DateField] Between Date-179 And Date-166

 

[theDBguy]

Would have thought this is sargable (assumes I’ve got the maths right

WHERE [DateField] Between Date-179 And Date-166

I see. Thanks!