Need code to pop up a message based on data crieria

[avayaman]

Here is the situation, I have a database of records. One field is a date field containing the date the record was last changed or established.

There is another field of drop down choices.

What I want to do is run a query on the database such that if the date is 120 days past the date entered in the date field, and the value in the other field is "pending" a pop up message will appear. I know how to create the message. It the sql query i do not know how to program.

The table name is "maintable" the date field is "date" & the field which I am looking for pending inside is "status"

This is a club membership database. the purpose of this is to create a pop up alert when the database is opened if it is 120 days past when someone signed up or renewed and said they were going to send a check. People in that position are tagged as "pending". There is already a button to list them, this is just a reminder to go look at it if anyone in there is over 120 days in arrears.

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com

 

[djj55]

SELECT * FROM maintable WHERE DATEDIFF(date, GETDATE()) > 120 AND status = 'pending'

not tested.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[markros]

I suggest

IF EXISTS (select 1 from MainTable where [Date] < dateadd(day,-120,CURRENT_TIMESTAMP) and Status = 'Pending')
   RAISERROR('Old records with status still Pending!',16,1)

PluralSight Learning Library

 

[avayaman]

OK I think I led everyone astray. by posting in the wrong section. This is a MS Access 2003 database, not a mysql database.

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com

 

[tcsbiz]

It would still be the wrong place if it were a MySQL database.

 

[avayaman]

Good news is the problem was solved. Thanks

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com