DateAdd along with CASE query

[nupur]

Hi!

I have a date column in my database (suppose start date) which I have to use for calculating other dates (suppose the calculated dates columns are x, y and z). To do this I used the DateAdd function and the results were good. But now based on different conditions (suppose there are 4 different scenarios; 1, 2 ,3 and 4) some of the calculated date fields are "NA".
For eg: for scenario 2 , 3 the columns x and y are "NA" but have the dates for 1 and 4.

The example code i used was:

select *, dateadd ("d", 5, "start date") as x , dateadd ("d", 10, "startdate") as y, dateadd ("d", 15, "startdate") as z
from database;

i dont know how to add the case (when, then) function with this code.
I would really appreciate your advice.
Thanks a lot

 

[Beetle]

Hard to be specific withour knowing more, but you can use the IIf function in a query;

IIf([ThisField]="x" Or [ThisField]="y","N/A",DateAdd("d",5,[StartDate]))

 

[nupur]

Thanks for your response.
I am sorry but I didn't understand the query. What I want to do is if scenario is 1 or 2 then x (which is the calculated column) = "NA" else if scenario is 3 or 4, then x = x.
Could you please explain your query?

 

[Beetle]

Could you please explain your query?

It's not a query, it's just an expression you could use in a query.

What I want to do is if scenario is 1 or 2 then x (which is the calculated column) = "NA" else if scenario is 3 or 4, then x = x.

What do you mean by "scenario"? Are you evaluating data in another field in the same table?

For example, let's say I have a table (called MyTable) with the fields StartDate and MyCondition. Data might look like;

StartDate------MyCondition
7/5/2011-----------1
7/5/2011-----------2
7/5/2011-----------3
7/5/2011-----------4

I could then write a query like the following;

Select StartDate, IIf([MyCondition]="1" Or [MyCondition]="2", "N/A", DateAdd("d", 5, [StartDate])) As X, IIf([MyCondition]="3" Or [MyCondition]="4", "N/A", DateAdd("d", 10, [StartDate)) As Y From MyTable;

Results would look like;

StartDate--------X----------Y
7/5/2011--------N/A-----7/15/2011
7/5/2011--------N/A-----7/15/2011
7/5/2011-----7/10/2011-----N/A
7/5/2011-----7/10/2011-----N/A

Although, I'm not completely sure if this is what you are trying to accomplish or not. I'm just guessing based on what I can gather from your post.

 

[nupur]

Hi
Thank you so much for your explanation.
This worked but based on your assumed scenario, I also want to add different dates for columns X and Y based on the condition.
For eg:
If my condition is 1 then dateadd("d", 5, start date) as X but if my condition is 2 then dateadd ("d", 10, start date) as X, and similarly for Y.

I tried to use this query but it didnt work.
IIf([MyCondition]="1", dateadd ("d" 5, [StartDate])) as X
IIf([MyCondition]="2", dateadd ("d" 10, [StartDate])) as X

I know this query is wrong and its not running because I am assigning duplicate values as X. I have tried to modify ot a lot but of no help.

 

[Beetle]

Then you have to nest your IIf statements;

IIf([MyCondition]="1", DateAdd("d", 5, [StartDate]), IIf([MyCondition]="2", DateAdd("d", 10, [StartDate]), "N/A")) As X