bfuchs
asked on
What logic to build in order to get a weekly reminder
Hi Experts,
I have a table with following columns
Openings
ID=AutoNum
Status=Varchar(50)
StatusDate=DateTime
Now current logic requires user to update Status/StatusDate in a daily bases.
So when user logs into the app first, it checked if there are any records with StatusDate null or StatusDate <Date() and it opens the form with those records and doesn't let them close until all records are updated with current date.
Now user is requesting following changes.
1- This restriction should be based weekly instead of daily. (guess StatusDate <Date()-6)
2- It should start on Tuesday's
3- If Tuesday was a holiday (or user didnt came in to work) then should be on the following day.
How should I change the logic of the program to accommodate this?
I have a table with following columns
Openings
ID=AutoNum
Status=Varchar(50)
StatusDate=DateTime
Now current logic requires user to update Status/StatusDate in a daily bases.
So when user logs into the app first, it checked if there are any records with StatusDate null or StatusDate <Date() and it opens the form with those records and doesn't let them close until all records are updated with current date.
Now user is requesting following changes.
1- This restriction should be based weekly instead of daily. (guess StatusDate <Date()-6)
2- It should start on Tuesday's
3- If Tuesday was a holiday (or user didnt came in to work) then should be on the following day.
How should I change the logic of the program to accommodate this?
ASKER
Hi Pawan,
As stated above, my main concern is how to base logic on Tue.
In addition, I left out this piece, although user may only have entered info on Wed or Thu, but next time to be prompted for info again is following Tue!
So it turns out that logic is not necessary based only on 7 days apart, its mainly each Tue.
Thanks,
Ben
As stated above, my main concern is how to base logic on Tue.
In addition, I left out this piece, although user may only have entered info on Wed or Thu, but next time to be prompted for info again is following Tue!
So it turns out that logic is not necessary based only on 7 days apart, its mainly each Tue.
Thanks,
Ben
Instead of checking if there are any records with StatusDate Null or StatusDate < Date() then use this expression:
If DateDiff("ww", Nz(StatusDate, DateAdd("ww", 1, Date)), Date, vbTuesday, vbFirstFourDays) > 0 Then
' Time for update.
Or, in a query:Where DateDiff("ww", Nz([StatusDate], DateAdd("ww", 1, Date())), Date(), 3, 2) > 0
/gustav
You may try Timer Event.
Timer Interval is set to a convenient interval.
At event time check if the day is Tue, or Wed by deciding an approach to check if it was checked on Tue or not.
Process records as required.
Timer Interval is set to a convenient interval.
At event time check if the day is Tue, or Wed by deciding an approach to check if it was checked on Tue or not.
Process records as required.
ASKER
Hi Gustav,
This is for ADP project that needs t-sql syntax, can you tell me how to convert it to t-sql?
PS. Excuse me for not specify this at the beginning.
Thanks,
Ben
This is for ADP project that needs t-sql syntax, can you tell me how to convert it to t-sql?
PS. Excuse me for not specify this at the beginning.
Thanks,
Ben
ASKER
@hnasr,
This is something that's supposed to be checking only once when user logs into the app.
Thanks,
Ben
You may try Timer EventNo, I dont want use timer event when not absolutely necessary.
This is something that's supposed to be checking only once when user logs into the app.
Thanks,
Ben
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This seems to work fine, Thank you!
You are welcome!
/gustav
/gustav
Please try like..
Open in new window
Hope it helps!