Count # of quarters between two dates?

[dpav29]

I don't even know where to begin with this one!

The database deals with audits that are performed usually on a quaterly basis. Each time one is completed, a new due date is added to the table with the new date being calculated three months out. Right now, if an audit is past due, it simply says "audit is past due" on the report. I like it to print "Three audits are past due", etc. by counting quarters between the last one done and the current date.

For example, a quarterly audit was last done on 3/30/04. The next audit audit is in the table with a 6/30 due date, but it hasn't been completed. I want to be able to get a count of quarters between 3/30/04 and today. . .result is 2 (6/30 and 9/30).

Any thoughts that might point me in the right direction?

Thanks a lot!

 

[Trendsetter]

I can see 2 ways of doing this:

1) A count of the months since the last completion expressed as "Audit last completed x months ago"
This would be a straight subtraction of audit completed month and this month: this month 10 - last completed month 3.
"Audit last completed 7 months ago"

2)Build a query to split the month numbers into quarters and then use this query against your data to show a specific instance of the last completion quarter number, then you could print.
"Audit last completed quarter 1"
or
You could subtract that quarter no from this month's quarter number to show:
"Audit last completed 3quarters ago"



Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk