Thank you Graham, the where clause that you recommended gave me the results
that I was looking for, and I appreciate you explaining why code would not
work. Your suggestion at the end of your post was partly my step two issue
that I had to solve, and that is generating a date that the reminder is/was
sent. That field is "RemindSentDate" which I suspect will serve the same
purpose as the "ReminderDue field that you are recommending. I figured that
I could create an update query that places today's date in the
ReminderSentDate. That way, I have a record of when the reminder was sent.
You are right, there is no logical connection between ReminderDate and
RepairDateOut. I only used RepairDateOut as the starting line for
establishing how long it will be before the reminder is generated.
This is the first step to what will ultimately be a multistep process to go
from.
1. generating the reminder;
2. Creating a report that shows who is going to have an email reminder
sent to them;
3. Generating an email reminder through Outlook; and
4. Populate ReminderSentDate with Date() on the day the email goes
out.
Twenty-five percent of this is now in the bag thanks to you and Duane
Thanks again,
David
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Graham Mandeno
Sent: Thursday, November 03, 2011 4:48 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] How to set a reminder
Hi David
<quote>
WHERE (((tblScopeOfJob.Recommended)=-1) AND
((tblScopeOfJob.RemindSentDate)="isNull") AND
(("ServiceReminder")>=Date()-5 And ("ServiceReminder")<Date()+5))
</quote>
There are two major problems with this WHERE clause:
First, you are not comparing your date range with the calculated reminder
date, but with the string "ServiceReminder". I think what you mean is
[ServiceReminder], but [ServiceReminder] is the alias for a calculated field
and cannot be used in a WHERE clause, so you need to use the original
calculation in the WHERE expression (note you can use simple addition
instead of DateAdd):
([RepairDateOut]+[RemindInDays] between Date()-5 and Date()+4)
Secondly, you are comparing [RemindSentDate] with the string "isNull". This
does NOT check for a Null value. What you need is:
([ReminderSentDate] is Null)
I would also recommend you use <>0 instead of =-1 to test for a True value
in a Boolean field. This is because some database back-ends use 1, not -1
for True, but all use 0 for False.
So, the replacement WHERE clause becomes:
WHERE (tblScopeOfJob.Recommended<>0) AND
(tblScopeOfJob.RemindSentDate is Null) AND
([RepairDateOut]+[RemindInDays] between Date()-5 and Date()+4)
Incidently, why not have a ReminderDue date field that is calculated and
populated at the time the reminder is generated, instead of a RemindInDays
field? It seems to me the ReminderDate is not logically connected with the
RepairDateOut, and should therefore be independent.
Best wishes,
Graham
> From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
djsdaddy531
> Sent: Friday, 4 November 2011 08:28
> To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: [MS_AccessPros] How to set a reminder
>
>
> Great Day, I am working on an application for a small vehicle repair
> shop. It keeps track of customers' estimates and repair orders. It also
> keeps track of a repair item that the technician may discover while
> working on a repair order: For instance, the customer may bring the car
> in for an oil change, but the technician sees that a wheel bearing needs
> to be replaced. What I would like to do is identify the wheel bearing as
> something that needs to be done down the road, but it could be anything
> from something minor to something major. I have a field that permits the
> user to identify the replacement of the wheel bearing as something that
> needs to be done. I also have a field where I place the number of days
> from the repair when I would like to send an email reminder of this
> particular service item. The reminder can be set for 10 days or 20 days
> or 30 days, etc.
>
> I have created a query that tests for this reminder date and here is
> the SQL:
>
> SELECT tblJobs.JobID, tblJobs.CustomerID, tblTitles.CustomerTitle,
> tblMasterCustomer.FirstName, tblMasterCustomer.LastName,
> tblMasterCustomer.EmailAddress, tblScopeOfJob.Recommended,
> tblScopeOfJob.WorkRequested, tblScopeOfJob.RemindInDays,
> tblScopeOfJob.RemindSentDate, tblJobs.RepairDateOut,
> DateAdd("d",[RepairDateOut],[RemindInDays]) AS ServiceReminder
> FROM ((tblMasterCustomer INNER JOIN tblTitles ON
> tblMasterCustomer.CustomerTitleID = tblTitles.CustomerTitleID) INNER
> JOIN tblJobs ON tblMasterCustomer.CustomerID = tblJobs.CustomerID) INNER
> JOIN tblScopeOfJob ON tblJobs.JobID = tblScopeOfJob.JobId
> WHERE (((tblScopeOfJob.Recommended)=-1) AND
> ((tblScopeOfJob.RemindSentDate)="isNull") AND
> (("ServiceReminder")>=Date()-5 And ("ServiceReminder")<Date()+5));
>
> If I am following my own logic; if a reminder date falls between five
> days ago and five days from now, that record should show in the query. I
> have 1 record where RepairDateOut = #10/24/2011# and the reminder is to
> be sent out in 10 days, which would be today; consequently, this
> reminder should show up in the query since the date range would October
> 29, 2011 through November 8, 2011, but there are no records in the
> query.
>
> Thanks in advance for the assistance.
>
> r/David
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar