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
Tidak ada komentar:
Posting Komentar