Adding 6 Mo to a date

[idcowboy]

I have a DB I am building that is being used to keep track of clients machines In the DB I have such fields as date work in & clean N test (check box) What I want to be able to do is run an report that shows me all of the machines that are fit this Have been cleaned in the last 6 mo or later now every machine will have many cleanings on it and I need it to be able to pick out the last cleaning, by the date in date and calculate 6 mo or greater and if it is show up on the report I hope this makes sense thanks any help would be great

 

[scriverb]

Here is some sample SQL to select the records that have their last clean within the last 6 months:

Select tblYourTableName.MachineID, Max(tblYourTableName.CleanDate)
FROM tblYourTableName
WHERE tblYourTableName.CleanDate >= DateAdd("m", -6,Date())
Group by tblYourTableName.MachineID;

You will have to expand upon this code to include other fields as well as substitute your table name and fields accordintly.

Let me know if you need further assistance. Bob Scriver

 

[hartbros]

It wouks kind of I need the report to pull 6mo and plus but the report pulls 6mo an under

 

[scriverb]

I jdust change the > sign to <:
Select tblYourTableName.MachineID, Max(tblYourTableName.CleanDate)
FROM tblYourTableName
WHERE tblYourTableName.CleanDate <= DateAdd(&quot;m&quot;, -6,Date())
Group by tblYourTableName.MachineID;

You are looking for machines that have their last clean date longer than 6months ago correct? Well this will do it. Their clean date will be older than 6 months prior to todays date.
Bob Scriver

 

[hartbros]

I Thought it was working however, let me explain, I have a client that has had service work done on a machine in October, witch would make it due in march, well the machine was serviced in march and it is still showing the October date. Any help you have would be great thanks again

 

[scriverb]

Try this:

Select tblYourTableName.MachineID, Max(tblYourTableName.CleanDate) as Last_Clean_Date
FROM tblYourTableName
WHERE Max(tblYourTableName.CleanDate) <= DateAdd(&quot;m&quot;, -6,Date())
Group by tblYourTableName.MachineID;

Let me know how this one looks.


Bob Scriver

 

[hartbros]

Thanks Bob,
Here is my syntax:

SELECT wo.IDnum, Max(wo.DateIn) AS Datein
FROM wo, machine
WHERE Max (wo.DateIn)<=DateAdd(&quot;m&quot;,-6,Date())
GROUP BY wo.IDnum

I Get an error:
Cannont have aggregatefuction in WHERE clause (Max (wo.DateIn)<=DateAdd(&quot;m&quot;,-6,Date())


Any help you would have would be great thanks again

 

[CosmoKramer]

Try this small modification to Bob's last syntax:

SELECT wo.IDnum, Max(wo.DateIn) AS Datein
FROM wo, machine
GROUP BY wo.IDnum
HAVING Max(wo.DateIn)<=DateAdd(&quot;m&quot;,-6,Date())

 

[hartbros]

that works great thanks