Date Alerts

[SusanC]

Hi,
I have looked through past threads but can't find the help I need!

I have a table with the following :

ID
RequestNo
Startdate
Finishdate

Can I put this into a query and use an IIF statement to tell me when each request has finished. Something like
IIF([Finishdate] = Now(), "Yes","No")

Ideally I'd like the database to alert me when each request has finished, either by email or just a pop up! Is this possible?
thanks for any help in advance,
I'm quite new to this so please be gentle!
cheers
Sue

 

[ghudson]

IIF([Finishdate] = Now(), "Yes","No")

IIF([Finishdate] = Date(), "Yes","No")

Since the Now() function includes the exact time down to the seconds yours would not work. Use the Date() function.

 

[SusanC]

Thanks you that's much better. However, how can I make it so that it remains saying 'Yes' as once the date passes they will no longer match and so even though it is finished the result will say 'No'.

I was trying to put int something like
IIf([Finishdate] = > Date(), "Yes", "No")

but this doesn't work. I've also tried
IIf([Finishdate] = Date(), "Yes", "No" Or IIf([Finishdate] > Date(), "Yes", "No")

but that didn't work either?
Any ideas!
thanks
Sue

 

[Mile-O]

IIf([Finishdate] = Date() Or [Finishdate] > Date(),"Yes", "No")

 

[SusanC]

I was nearly there!! Thanks you make it look so easy!!

Is there anyway that if the result is 'Yes' I can either get an alert box to say so or possible even send an email i.e.

RequestNo
RequestStart
RequestFinish
Finished?
Email Address

I have very little experince in coding by the way!
thanks for your help so far though, saves me hours!!
Sue

 

[Mile-O]

It's possible but more information is needed. How does the database get updated with requests being finished?
Also, you can remove the Finished? field. Two reasons: i) it contains a special character (?) which is not advised in the name of any object (table, field, etc.; and ii) if the DateFinished field has a date then you know its finished - the Finished? field becomes redundant.

 

[SusanC]

yes I think I see what you mean. Let me see if I can explain what I want.

When a new request is made the following information is put into the table (yet to make a form).
Request Number
When the request starts
When the request finishes.
Any other relevant info.
The email address of the person who 'owns' that request.

So when the request has finished I'd like the database to email the person and tell them.

Is that clearer?

 

[Mile-O]

You'll need to make a form to bind to the query.

When you save the information on the form (after entering the finish date) you would use the DoCmd.SendObject method.

Try converting a SendObject macro to VB to get an idea of the code needed - it's not much, one line if I recall...

 

[SusanC]

? How do you 'bind' the form to the query?

 

[Mile-O]

1. Open Form in Design View
2. Open the form's Properties
3. Set its ControlSource to the name of the query

 

[SusanC]

okay. I've managed to get an email sent when I open this form up.
How can I get it to only send when the finish = yes?