Complex date calculations

[Ellen07886]

Hello

Newbie here looking for help.

I have a database which I need to add a calculated field in, I currently do this in Excel and copy the data across but there are lots of records and I'd like to streamline the process if I can, so I want to add a calculated field which will provide me with a deletion date based on the following criteria:

deletion date =
DateofReport + 22 years if Age is blank or
DateofReport + 4 if Age >=18 or
DateofReport + number of years between Age and 18 eg Age is 6 so the number of years between 6 and 18 is 12.

This is the formula used in Excel =IF(Age=" ",DateofReport,IF(Age>=18,DateofReport+4*365,Dateo fReport+(22-Age)*365))

Is it possible to do this in Access? I really don't know where to start.

[Bulzie]

Research DateAdd Function for Access. you can add or subtract years, days, months, etc. to calculations. And yes you can create same type of formulas in Access.

https://www.techonthenet.com/access/...te/dateadd.php

[Micron]

I need to add a calculated field in

Hopefully you don't mean in a table, nor do you intend to store the calculation. Things like this can be done with nested IIF statements or you can use a function and set the name of the function to be the form control's control source property or as a calculated query field. The thing about DateAdd is that you'll have to incorporate it into nested IIF's if I'm not mistaken. I imagine that will look real ugly.

[CJ_London]

your excel formula does not match your requirement.

your requirement says

DateofReport + 22 years if Age is blank

your formula says

IF(Age=" ",DateofReport,

so if blank, it is not adding 22 years

Age is generally not a good value to use - it changes every year, better to use date or year of birth

but you can use a formula along these lines

deletiondate=dateadd("yyyy",switch(isnull(age),22,age>=18,4,true,18-age),dateofreport)

[Micron]

Dang! I keep forgetting about the switch function as opposed to nested IIF's.

[Ellen07886]

Research DateAdd Function for Access. you can add or subtract years, days, months, etc. to calculations. And yes you can create same type of formulas in Access.

https://www.techonthenet.com/access/...te/dateadd.php

Thanks. it looks a bit complicated but I'll have a go.

[pbaldy]

Post 6 was moderated, I'm posting to trigger email notifications.