Change status during query

[dhborchardt]

I have a query that will look at the PM table and see when the last PM was done, compare it with the PM recurance number, and if it is greater than the PM occurance number, the datediff will be set to yes and will then appear in the result.

This is what I would like to do.
If DateDiff is set to yes because it meets the criteria explained above, I then need the
tblPM.PMLastscheduled set to todays date so the next time the query is run I don't get the same results until the recurance number has been reached again.

I call this query from an ASP page and it will take these results and put them in another table for current PMs that need to be completed. I have found that I can't use user defined functions because I will get errors in the ASP page, so it needs to be done as an expression in the query itself.

I included the SQL from the query.

SELECT tblPM.PMID, tblPM.MEIDKey, tblPM.PMDesc, tblPM.PMRecur, tblPM.PMLastCompleted, tblPM.PMLastScheduled, IIf(DateDiff('d',PMLastCompleted,Now())>PMRecur,'Yes','No') AS IsDue
FROM tblPM
WHERE (((IIf(DateDiff('d',[PMLastCompleted],Now())>[PMRecur],'Yes','No'))="Yes"));

 

[PHV]

If DateDiff is set to yes because it meets the criteria explained above, I then need the tblPM.PMLastscheduled set to todays date
Something like this ?
UPDATE tblPM SET PMLastscheduled=Date()
WHERE PK_of_tblPM IN
(SELECT PK_of_tblPM FROM tblPM
WHERE DateDiff('d',PMLastCompleted,Now())>PMRecur);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhborchardt]

Thank you for you reply. I have two follow up questions.

In your solution, did you intend this to be a query on its own, or do I add this SQL to the existing.

I don't understand what PK_of_tblPM is

Thanks again
Dale

 

[PHV]

PK_of_tblPM is a place holder for the fieldname of the PrimaryKey.
My suggestion is an action query on its own to do the update of PMLastscheduled when the criteria is meet, as you asked.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

If you are filtering to only show records that are due, you can use these field expressions in the Select clause instead:

Now() as PMLastScheduled,
"Yes" as IsDue

And this in the Where:

DateDiff('d',[PMLastCompleted],Now())>[PMRecur]