DECODE Help

[cranebill]

SUM(DECODE(DATEDIFF(date_sold,date_of_birth),(Need help here,>6570 AND <6935 ),1,0)) "18-19"

is it possible to have a range?

any help appreciated

 

[rac2]

You probably already noticed that you have posted in the Access Forum some code that is peculiar to Oracle dialect of SQL.

I think that the CASE expression is a "standard" SQL alternative to the DECODE function. It might look like this.

SUM (
         CASE
              WHEN DATEDIFF(day, date_sold, date_of_birth) BETWEEN 6570 AND 6935 THEN 1
              ELSE 0
         END
        )

Well then there is the question, does Access provide CASE expressions. Probably does, but if not you can get the result with the IIf(condition, value_when_true, value_when_false) function. And the DATEDIFF function might have different values to specify the units for the difference between the dates.

 

[PHV]

As we are in the Access query / JetSQl forum:
Sum(IIf(([date_sold]-[date_of_birth]) Between 6571 And 6934, 1, 0)) AS [18-19]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244