Decode 1

[NVSbe]

The ANSI SQL standard allows the function decode in a query, I believe. It appears Access doesn't allow this. Is thre an equivalent for the fucntion?

select val1, min(val2), max(val3)
from tbl1
group by val1
having min(decode(val2,0,999,val2)) > max(val3)

That's what I'm trying to do. --------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.

 

[smedvid]

I think you may want the IIF() here... Not sure if it will work as you need it though.

Another consideration could be to use an SQL Pass-Through Query - that way you can use the native SQL syntax "Decode".

There are many advantages to using Pass-through queries, I try to use them when possible, especially when I need to work with large data sets.

htwh, Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[NVSbe]

The IIF did the trick, thanks...


SELECT val1, min(val2), max(val3)
FROM tbl1
GROUP BY val1
HAVING IIF(MIN(val2) = 0,999, MIN(val2)) > MAX(val3)

--------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.