how do i write if and else statement in access query

[Guest]

Hi

I have 2 date fields eg date1 and date 2. I would like to create an
expression in access query with if and else statement to count the number of
day diff between date 1 and date 2.
If the expression WTime : (sum(4-([date 1]-[date 2]))) is positive value
– display the positive value, if the sum is equal to a negative value – to
display it as ‘0†value.

Do you have an idea how to write in the query?

 

[Guest]

Try this, using iif with datediff
sum(IIf(datediff("d",[date1],[date2])>0,datediff("d",[date1],[date2]),0))

 

[fredg]

Hi

I have 2 date fields eg date1 and date 2. I would like to create an
expression in access query with if and else statement to count the number of
day diff between date 1 and date 2.
If the expression WTime : (sum(4-([date 1]-[date 2]))) is positive value
¡V display the positive value, if the sum is equal to a negative value ¡V to
display it as ¡¥0¡¨ value.

Do you have an idea how to write in the query?

In a query you would use an IIF statement:

Exp:IIf(sum(4-([date 1]-[date 2]))>0,sum(4-([date 1]-[date 2])),0)

Create a Totals query (by clicking on the Sigma tool button.
Change Group By to Expression for this column.

 

[John Spencer (MVP)]

Use an immediate if statement

WTime: IIF((sum(4-([date 1]-[date 2])))>0,(sum(4-([date 1]-[date 2]))),0)

 

[Guest]

Hi John Spenser

The expression works for me. Thanks.

I have another question which needs your help -
if there is a blank [date 1] in my earlier expression
WTime : (sum(4-([date 1]-[date 2]))) - the result will reflect as a "blank"
entry and this is what I wish to maintain for statistics purpose. How do I
incorporate this criteria in the expression you have shown me.
I appreciate your help. Thank you once again.

Use an immediate if statement

WTime: IIF((sum(4-([date 1]-[date 2])))>0,(sum(4-([date 1]-[date 2]))),0)

Hi

I have 2 date fields eg date1 and date 2. I would like to create an
expression in access query with if and else statement to count the number of
day diff between date 1 and date 2.
If the expression WTime : (sum(4-([date 1]-[date 2]))) is positive value
– display the positive value, if the sum is equal to a negative value – to
display it as ‘0â€* value.

Do you have an idea how to write in the query?

 

[Guest]

Hi fredg,

Thanks. Your expression is right for me. I appreciate your help.

Hi

I have 2 date fields eg date1 and date 2. I would like to create an
expression in access query with if and else statement to count the number of
day diff between date 1 and date 2.
If the expression WTime : (sum(4-([date 1]-[date 2]))) is positive value
– display the positive value, if the sum is equal to a negative value – to
display it as ‘0†value.

Do you have an idea how to write in the query?

In a query you would use an IIF statement:

Exp:IIf(sum(4-([date 1]-[date 2]))>0,sum(4-([date 1]-[date 2])),0)

Create a Totals query (by clicking on the Sigma tool button.
Change Group By to Expression for this column.

 

[Guest]

Hi Ofer,

The expressions from fredg and John Spencer are what I am looking for. Thank
for your help.

Try this, using iif with datediff
sum(IIf(datediff("d",[date1],[date2])>0,datediff("d",[date1],[date2]),0))

Hi

I have 2 date fields eg date1 and date 2. I would like to create an
expression in access query with if and else statement to count the number of
day diff between date 1 and date 2.
If the expression WTime : (sum(4-([date 1]-[date 2]))) is positive value
– display the positive value, if the sum is equal to a negative value – to
display it as ‘0†value.

Do you have an idea how to write in the query?

 

[John Spencer (MVP)]

One way.

WTime: IIF([Date 1] Is Null, Null, IIF((sum(4-([date 1]-[date
2])))>0,(sum(4-([date 1]-[date 2]))),0))

Hi John Spenser

The expression works for me. Thanks.

I have another question which needs your help -
if there is a blank [date 1] in my earlier expression
WTime : (sum(4-([date 1]-[date 2]))) - the result will reflect as a "blank"
entry and this is what I wish to maintain for statistics purpose. How do I
incorporate this criteria in the expression you have shown me.
I appreciate your help. Thank you once again.

Use an immediate if statement

WTime: IIF((sum(4-([date 1]-[date 2])))>0,(sum(4-([date 1]-[date 2]))),0)

Hi

I have 2 date fields eg date1 and date 2. I would like to create an
expression in access query with if and else statement to count the number of
day diff between date 1 and date 2.
If the expression WTime : (sum(4-([date 1]-[date 2]))) is positive value
– display the positive value, if the sum is equal to a negative value – to
display it as ‘0â€* value.

Do you have an idea how to write in the query?

 

[Guest]

Hi,

Great!, the expression works. Thanks once again.

One way.

WTime: IIF([Date 1] Is Null, Null, IIF((sum(4-([date 1]-[date
2])))>0,(sum(4-([date 1]-[date 2]))),0))

Hi John Spenser

The expression works for me. Thanks.

I have another question which needs your help -
if there is a blank [date 1] in my earlier expression
WTime : (sum(4-([date 1]-[date 2]))) - the result will reflect as a "blank"
entry and this is what I wish to maintain for statistics purpose. How do I
incorporate this criteria in the expression you have shown me.
I appreciate your help. Thank you once again.

Use an immediate if statement

WTime: IIF((sum(4-([date 1]-[date 2])))>0,(sum(4-([date 1]-[date 2]))),0)

ann chan wrote:

Hi

I have 2 date fields eg date1 and date 2. I would like to create an
expression in access query with if and else statement to count the number of
day diff between date 1 and date 2.
If the expression WTime : (sum(4-([date 1]-[date 2]))) is positive value
– display the positive value, if the sum is equal to a negative value – to
display it as ‘0â€* value.

Do you have an idea how to write in the query?