Date comparions

[MKH2001]

I have a program of works that are done as part of various regimes

ISF1 = Monthly
ISC1 = Monthly
ISF3 = Quarterly
ISC3 = Quarterly
ISF6 = Six Monthly
ISFA = Annually

I query another database against my programs

SELECT IMSV7_INLAI.STARTDTTM, IMSV7_INLAI.COMPKEY, WholeNetwork_Regime_3.COMPKEY, WholeNetwork_Regime_3.UNITID, IMSV7_INLAI.INSPKEY, IMSV7_INLAI.INSPNO, IMSV7_INLAI.ACTKEY, IMSV7_ACTDEFN_1.ACTDESC, IMSV7_ACTDEFN_1.ACTKEY, IMSV7_ACTDEFN_1.ACTCODE, WholeNetwork_Regime_3.FILENAME, WholeNetwork_Regime_3.REGIME, IMSV7_INLAI.COMPBY, WholeNetwork_Regime_3.INITIATION, WholeNetwork_Regime.INITIATION INTO mktbl_InspectPerformanceFigures_stp1_1
FROM (((tbl_InspectorChsn2 LEFT JOIN ((IMSV7_ACTDEFN AS IMSV7_ACTDEFN_1 RIGHT JOIN IMSV7_INLAI ON IMSV7_ACTDEFN_1.ACTKEY = IMSV7_INLAI.ACTKEY) LEFT JOIN WholeNetwork_Regime AS WholeNetwork_Regime_3 ON IMSV7_ACTDEFN_1.ACTCODE = WholeNetwork_Regime_3.INITIATION) ON tbl_InspectorChsn2.DBName = IMSV7_INLAI.COMPBY) LEFT JOIN WholeNetwork_Regime ON IMSV7_INLAI.COMPKEY = WholeNetwork_Regime.COMPKEY) LEFT JOIN tbl_RegimeChsn2 AS tbl_RegimeChsn2_1 ON WholeNetwork_Regime.REGIME = tbl_RegimeChsn2_1.REGIME) LEFT JOIN tbl_FilenameChsn2 AS tbl_FilenameChsn2_1 ON WholeNetwork_Regime.FILENAME = tbl_FilenameChsn2_1.FILENAME
WHERE (((tbl_FilenameChsn2_1.FILENAME) Is Not Null) AND ((tbl_RegimeChsn2_1.REGIME) Is Not Null))
GROUP BY IMSV7_INLAI.STARTDTTM, IMSV7_INLAI.COMPKEY, WholeNetwork_Regime_3.COMPKEY, WholeNetwork_Regime_3.UNITID, IMSV7_INLAI.INSPKEY, IMSV7_INLAI.INSPNO, IMSV7_INLAI.ACTKEY, IMSV7_ACTDEFN_1.ACTDESC, IMSV7_ACTDEFN_1.ACTKEY, IMSV7_ACTDEFN_1.ACTCODE, WholeNetwork_Regime_3.FILENAME, WholeNetwork_Regime_3.REGIME, IMSV7_INLAI.COMPBY, WholeNetwork_Regime_3.INITIATION, WholeNetwork_Regime.INITIATION, WholeNetwork_Regime.COMPKEY, WholeNetwork_Regime.FILENAME, WholeNetwork_Regime.REGIME
HAVING (((IMSV7_INLAI.STARTDTTM) Between [Forms]![frm_InspectionPerformanceFigures]![datefrom] & " 00:00:00" And [Forms]![frm_InspectionPerformanceFigures]![dateto] & " 23:59:59") AND ((IMSV7_INLAI.COMPBY) Is Not Null) AND ((WholeNetwork_Regime_3.INITIATION) Is Not Null) AND ((WholeNetwork_Regime.INITIATION)=[WholeNetwork_Regime_3]![Initiation]) AND ((WholeNetwork_Regime.COMPKEY)=[WholeNetwork_Regime_3]![COMPKEY]) AND ((WholeNetwork_Regime.FILENAME)=[WholeNetwork_Regime_3]![FILENAME]) AND ((WholeNetwork_Regime.REGIME)=[WholeNetwork_Regime_3]![REGIME]));

Which lets me know that a programmed file has been done within a particular period.

The next step I need to achieve is to compare the returned records "STARTDTTM" with the original database to see if they have been done in time.

ISF1 = Monthly plus or minus 1 working days (not including weekends)
ISC1 = Monthly Monthly plus or minus 1 working days(not including weekends)
ISF3 = Quarterly Monthly plus or minus 3 working days(not including weekends)
ISC3 = Quarterly Monthly plus or minus 3 working days(not including weekends)
ISF6 = Six Monthly Monthly plus or minus 3 working days(not including weekends)
ISFA = Annually plus or minus 5 working days(not including weekends)

Can anyone think of where I would get started on this one?

Thanks for you assistance in advance.

Lidias

 

[MKH2001]

Bump.

Still having no joy at this so anything anyone can think of would be worth a try.

 

[MKH2001]

Dispite a disappointing lack of response from anyone in the forum so far I am so close.

I use

From:DateAdd("m",-tbl_InspectionProgramDurationandResource!FrequencyMonths,qry_InspectPerformanceFigures_stp1_3!STARTDTTM)-tbl_InspectionProgramDurationandResource!ToleranceDays

and

To: DateAdd("m",-tbl_InspectionProgramDurationandResource!FrequencyMonths,qry_InspectPerformanceFigures_stp1_3!STARTDTTM)+tbl_InspectionProgramDurationandResource!ToleranceDays

to give me my "BETWEEN" dates to query on.
Which works brilliantly.
EXCEPT where my From date equals a Saturday or Sunday (needs to also exclude public holidays somehow =/) I need the query to return the previous working day.
Similarly where my To date equals a Saturday or Sunday (needs to also exclude public holidays somehow =/) I need the query to return the Following working day.

Any ideas?

Think there should be some coding for weekends, although with public holidays I guess it is likely I will need another Table where I manually add what constitutes as these so any query could make exception to them?

Anyway... stuck on this bit now.
But very proud for making it this far =P

Lidias

 

[lespaul]

FAQ181-261 has some code that you may be able to modify to find the date that you REALLY need.

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins