Test Due date (1 Viewer)

[PeteB]

Although I have been working with Access for a while, am still finding it challenging. Not sure what happened to my last post, but am still struggling with the following:

I need to calculate a 'Test Due' date which is one year later than a 'Date Tested' date but as there are multiple 'Date Tested' dates for each item being tested (successive past annual tests) I first need to find the most recent date in the 'Date Tested' field, and for each item tested. The query I've created returns all of the tests until I add DateAdd("d",365,[Date Tested]) into the design grid under 'Date Tested', then the query returns zero records.

My ultimate aim is to create a form which alerts if a test is due and which opens on 'Database open'.

I would really appreciate help with this.

 

[Minty]

By putting that in the criteria, you are testing for [Date Tested] a year after the [Date Tested] - which is why no records are returned. You need a calculated field.

In the top line of the query designer type TestDue: DateAdd("d",365,[Date Tested])
Run your query.

Now under that calculated field put in criteria of <Date() to return all tests that are now overdue.

 

[isladogs]

1. Do an aggregate query to get the MAX 'Date tested' values
In SQL That will be shown as MaxOfDate Tested unless you give it an alias e.g. MaxDateTested

2. Create a 2nd query based on the above & add the DateAdd field to your query.
However, your current formula will be wrong in a leap year. Change it to

DateAdd("y",1,[MaxOfDate Tested])

or

DateAdd("y",1,[MaxDateTested])

if using the alias

NOTE:
Advise you don't have spaces in field names to simplify code - [] brackets no longer needed

 

[PeteB]

Hi Minty
Just tried your solution to the 'Test Due Date' and it worked fine - thanks a million.
PeteB