How to calculate the next due date using Calculated field or Query Criteria?

[nmedelec]

I am fairly new to this forum and also Access database, so this question might seem really lame, but I really need some help here.

Following is an example of table I am working with.

ID	Device Number	1st Cal Date	1st Due date	2nd Cal Date	2nd Due date	Next Due Date after today
1	1234	03/31/15	03/31/16	02/03/16	02/03/17
2	4567	04/14/15	04/14/16	01/26/16	01/26/17
3	7890	03/08/16	03/08/17

<tbody style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;">
</tbody>

I am trying to fill the last column such that the next due date after "Today" within that row is displayed.
For example in the first row it should be 02/03/17 and so on for the next rows.

In excel the formula I use is (MIN(IF(ROW2Col3:Row2Col6)>Today()),Row2Col3:Row2C ol6)
Pretty straight forward, but with access I am not entirely sure how to do the same.

So far I have tried using the Calculated field using Date/Time, but I get an #ERROR.

any help will be greatly appreciated.
Thank You

 

[Joe4]

Welcome to the Board!

The issue that you have is that you are working with a de-normalized database (meaning that the data design does not follow the best practice rule of relational database design - see: https://support.microsoft.com/en-us/kb/283878). Fields within a single record are really not supposed to be dependent on each other.

The MIN function in Access works against a single field across multiple records. You are trying to to perform a MIN against multiple fields in a single record. No such function exists.
So, if you want to do it, you have to write a formula to compare each field against the others. Since you have four fields, that is going to be a bit cumbersome. If I had no ability to change the structure of the data, I would probably create my own User Defined Function in VBA to do it, where you feed in the four fields, and then program it to do all the checks.

 

[mistrellis]

Are you trying to add 1 year to the previous field? If so, you can use a query with:
DateAdd("y", 1, [2nd Due date]) as a field