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

[gulaborani]

Hello Everyone,

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

I am trying to fill the 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

[Bulzie]

Not sure if I get what you are asking, what column are you looking to populate? All the Dute Dates values? Looks like you want to add time to a certain date? If so look at DateAdd function. So if you wanted to know the year after a date, it would be (changeYourdate to your date field):
DueDate2 = DateAdd("yyyy", 1, YourDate)

[aytee111]

The structure of the table is like a spreadsheet - columns going off into the future. Each date combination should be on a different record:
ID
Device Number
Cal Date
Due Date

Then in your query you take the max due date.

[gulaborani]

Thank You for your prompt reply.

Correct me if I am wrong, but the DateAdd function will only add to one specific date?

What I am looking for is not to add but to find from Column 3-Column 6 (1st Cal Date, 1st Due Date, 2nd Cal Date, 2nd Due Date) which one of these is the next date after today.

How can we look up these 4 dates (Date1, Date2, Date3, Date4) which one is the next date after today?

Does it make sense? I am so sorry for not being able to explain it better.

[gulaborani]

thank you aytee111 for replying back.

SO if my table already have these 4 different columns, I need to separate them into 2 columns and create new record of the next 2 columns?
I am not sure if I follow. Is there no way to just look up the date the way we see on a spreadsheet. Row2Col3:Row2Col6?

[aytee111]

In order to work in Access databases you have to think the opposite of spreadsheets! Spreadsheets are 2-dimensional, databases are 3-dimensional, a whole different concept. Access uses keys to get to records, so Row2Col3 will be primary key (when it is known) for the row number - record - and a real field name for the column.

[aytee111]

Further: you would have a query that used grouping (totals) and would group by device and take the max of the due dates - 1234/020317, etc. Another query would be a crosstab query to make it look like a spreadsheet and would include this first query which contains the field Next Due Date by device.