Remind me Friday, what I should see on the weekend

Question

I have the following query set up, in various incarcerations, to remind me of contract expirations 0, 7, 30, 60, and 90 days before the confirmed expiration date. My method of calculating the notification date doesn't give me any flexibility. For example, say I have 30 contracts expiring 12/5, a Saturday when my office isn't open. I will miss the notification that the contract expired that day, and 7 days out.

SELECT qryAllSold.SoldID, qryAllSold.CompanyName, qryAllSold.Product, qryAllSold.Description, qryAllSold.Quantity, qryAllSold.Price, qryAllSold.ConfirmedExpires, qryAllSold.Note
FROM qryAllSold
WHERE (((qryAllSold.ConfirmedExpires)=DateAdd("d",0,Int(Now()))) AND ((qryAllSold.RenewedToID) Is Null) AND ((qryAllSold.NonOpportunity)=No));

Each of these queries will be displayed on one "dashboard" form to serve as a daily task list. It would be great if I could have the form represent a date, and click an arrow to move forward or back a day at a time, but I don't know if Access has that capability.

If that won't work, what other ways can I make sure I don't miss notifications on these expirations?

Answer 1

[EDIT] If you are not a programmer here is an alternative solution

Why not use the Format function in your query, e.g.

Format([ConfirmedExpires],"ww",2,1)

"ww" - specifies weeks, 2 specifies Monday as first day of the week, 1 means week 1 contains 1 Jan

Now it is easy to do date calculations in weeks e.g. Contract expiring more than 4 weeks into the future:

Format([ConfirmedExpires],"ww",2,1)-Format(Now(),"ww",2,1))>4

Contracts expiring more than 4 weeks into the future and up to 5 weeks into the future

Format([ConfirmedExpires],"ww",2,1)-Format(Now(),"ww",2,1))>4 -
Format([ConfirmedExpires],"ww",2,1)-Format(Now(),"ww",2,1)),=5

Does that help?

---

Why don't you just use the weekday feature of functions like DateAdd?

Instead of calculating 30 calendar days ahead you could calculate 25, or any other number of weekdays ahead.

The code below displays a message box but you could equally use the function in a SQL query behind a message box.

Sub TestWeekDay()
    Dim FirstDate As Date    ' Declare variables.
    Dim IntervalType As String
    Dim Number As Integer
    Dim Msg

    IntervalType = "ww"    ' "ww" specifies weeks as interval.
    FirstDate = InputBox("Enter a date")
    Number = InputBox("Enter number of weekdays to add")
    Msg = "New date: " & DateAdd(IntervalType, Number, FirstDate)
    MsgBox Msg
End Sub

Answer 2

Perhaps a little complication:

IIf(Weekday(DateAdd("d", 0, Date())) = 7, DateAdd("d", 0, Date()) + 2, IIf(Weekday(DateAdd("d", 0, Date())) = 1, DateAdd("d", 0, Date()) + 1, DateAdd("d", 0, Date())))

This can be put in your where statement, like so:

SELECT q.SoldID, q.CompanyName, q.Product, 
       q.Description, q.Quantity, q.Price, 
       q.ConfirmedExpires, q.Note
FROM qryAllSold AS q
WHERE q.ConfirmedExpires=IIf(Weekday(DateAdd("d", 0, Date())) = 7, DateAdd("d", 0, Date()) + 2, IIf(Weekday(DateAdd("d", 0, Date())) = 1, DateAdd("d", 0, Date()) + 1, DateAdd("d", 0, Date())))
AND q.RenewedToID Is Null AND q.NonOpportunity=No

Note that I have used the alias q for qryAllSold as this makes it easier to read the query: FROM qryAllSold AS q

It would probably be best to use False instead of No.

If you wish, you should be able to use a parameter:

PARAMETERS NoDays Short;
SELECT q.SoldID, q.CompanyName, q.Product, 
       q.Description, q.Quantity, q.Price, 
       q.ConfirmedExpires, q.Note
FROM qryAllSold AS q
WHERE q.ConfirmedExpires=IIf(Weekday(DateAdd("d",[NoDays],Date()))=7,DateAdd("d",[NoDays],Date())+2,IIf(Weekday(DateAdd("d",[NoDays],Date()))=1,DateAdd("d",[NoDays],Date())+1,DateAdd("d",[NoDays],Date())))
AND q.RenewedToID Is Null AND q.NonOpportunity=No

The parameter allows you to pick a number of days. It would be even better if this referred to a field on a form or was set in VBA.