I am relatively new to this sort of thing and I'm trying to figure out the best approach to returning calculated results in a query. I have been using nested IIf statements that include DateAdd but I think that I am at a point where there must be a better way.
I want my query to calculate a date based on:
1) A Type field from my table
2) A calculated date based on other query fields.
My query has the following fields:
[Type] [Action 1] [Projected Action 1] [Projected Action 1 Revised] [Action 2]
I want to create a calculated field for [Projected Action 2] that says:
If [Type] is "A" then
if [Action 2] Is NotNull, Null
if [Action 1] is NotNull, [Action 1] + 10 workdays
if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 10 workdays
if [Projected Action 1] is NotNull, [Projected Action 1] + 10 workdays
If [Type] is "B" then
if [Action 2] Is NotNull, Null
if [Action 1] is NotNull, [Action 1] + 5 workdays
if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 5 workdays
if [Projected Action 1] is NotNull, [Projected Action 1] + 5 workdays
If [Type] is "C" then
if [Action 2] Is NotNull, Null
if [Action 1] is NotNull, [Action 1] + 30 workdays
if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 30 workdays
if [Projected Action 1] is NotNull, [Projected Action 1] + 30 workdays
If [Type] is "D" then
if [Action 2] Is NotNull, Null
if [Action 1] is NotNull, [Action 1] + 15 workdays
if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 15 workdays
if [Projected Action 1] is NotNull, [Projected Action 1] + 15 workdays
I would be happy to keep using nested IIf but I keep running into problems and I thought that there is probably a better way. I'm just not familiar with VBA or anything.
HELP!
Any suggestions would be deeply appreciated. Thanks!
I want my query to calculate a date based on:
1) A Type field from my table
2) A calculated date based on other query fields.
My query has the following fields:
[Type] [Action 1] [Projected Action 1] [Projected Action 1 Revised] [Action 2]
I want to create a calculated field for [Projected Action 2] that says:
If [Type] is "A" then
if [Action 2] Is NotNull, Null
if [Action 1] is NotNull, [Action 1] + 10 workdays
if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 10 workdays
if [Projected Action 1] is NotNull, [Projected Action 1] + 10 workdays
If [Type] is "B" then
if [Action 2] Is NotNull, Null
if [Action 1] is NotNull, [Action 1] + 5 workdays
if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 5 workdays
if [Projected Action 1] is NotNull, [Projected Action 1] + 5 workdays
If [Type] is "C" then
if [Action 2] Is NotNull, Null
if [Action 1] is NotNull, [Action 1] + 30 workdays
if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 30 workdays
if [Projected Action 1] is NotNull, [Projected Action 1] + 30 workdays
If [Type] is "D" then
if [Action 2] Is NotNull, Null
if [Action 1] is NotNull, [Action 1] + 15 workdays
if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 15 workdays
if [Projected Action 1] is NotNull, [Projected Action 1] + 15 workdays
I would be happy to keep using nested IIf but I keep running into problems and I thought that there is probably a better way. I'm just not familiar with VBA or anything.
HELP!

Any suggestions would be deeply appreciated. Thanks!