Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

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?
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi,

Please try like..

	IF EXISTS (
	SELECT * FROM 
	(
		SELECT MAX(StatusDate) StatusDate
		From yourtable 
	
	)k WHERE StatusDate < DATEADD( d , - 6 , GETDATE()) )


		SELECT 1

	ELSE

		SELECT 0 

Open in new window


Hope it helps!
Avatar of bfuchs

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
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.

Open in new window

Or, in a query:

Where DateDiff("ww", Nz([StatusDate], DateAdd("ww", 1, Date())), Date(), 3, 2) > 0 

Open in new window

/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.
Avatar of bfuchs

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
Avatar of bfuchs

ASKER

@hnasr,

You may try Timer Event
No, 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
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs

ASKER

This seems to work fine, Thank you!
You are welcome!

/gustav