SQL if then

  • Thread starter Thread starter Kraig
  • Start date Start date
K

Kraig

I am trying to compare values and calculate the difference between them based
on the compairason. Here is the code I have so far but I always get a syntax
error.

IF [U_Start date] <= [C_Start date] AND [U_End date] <= [C_End date] THEN
[Expr1] = DateDiff("n", [C_Start date], [U_End date]); {[Expr1] =
DateDiff("n", [C_Start date], [U_End date])}}

{ELSEIF [U_Start date] >= [C_Start date] AND [U_End date] >= [C_End date]
THEN [Expr1] = DateDiff("n", [U_Start date], [C_End date]); {[Expr1] =
DateDiff("n", [U_Start date], [C_End date])}}

{ELSEIF [U_Start date] >= [C_Start date] AND [U_End date] <= [C_End date]
THEN [Expr1] = DateDiff("n", [U_Start date], [U_End date]); {[Expr1] =
DateDiff("n", [U_Start date], [U_End date])}}

[ELSE [U_Start date] <= [C_Start date] AND [U_End date] >= [C_End date]
THEN [Expr1] = DateDiff("n", [C_Start date], [C_End date]); {[Expr1] =
DateDiff("n", [C_Start date], [C_End date])}]
ENDIF AS Expr1,

I modeled my syntax from http://docs.ingres.com/sqlref/IfThenElse, if anyone
can help I would appreciate it.
Thanks.
 
If Then is VBA
VBA does not work in SQL
There are two way you can do this. One would be to write a VBA Public
function in a standard module and call it from the query. The other is to
use the IIf statment. It works something like If Then, but there are some
differences. The most important being that both the True and False
expressions are evaluated whether they are returned or not, so anything that
would not evaluate correctly could throw an error. The basics are

=IIf(SomeCondition, True Expression, False Expression)
in other words
If SomeCondition is True, the True Expression is returned. If it is False,
the False Expression is returned.
IIf statments can be nested:

=IIf(SomeCondition, True Expression, IIf(AnotherCondition, True
Expression,False Expression))

Here is how it would look using your first example:
In the query builder:

Expr1: IIf([U_Start date] <= [C_Start date] AND [U_End date] <= [C_End
date], DateDiff("n", [C_Start date], [U_End date]), DateDiff("n", [C_Start
date], [U_End date]))

In SQL:

SELECT IIf([U_Start date] <= [C_Start date] AND [U_End date] <= [C_End
date], DateDiff("n", [C_Start date], [U_End date]), DateDiff("n", [C_Start
date], [U_End date])) AS Expr1
 
Back
Top