query with IFF question

Hi,

I have this query below:

select groupname
from detentgroups
where groupid = (select notedDestGroupID from DetentRequestedDest
where groupid =1 and detentnumber =1)

the groupid and detentnumber would be a variable...

My question is when the notedDestGroupID = 0, I should display
AUTOMATIC, but I don't have value 0 in my detentgroups, so I was
thinking to put an IFF in my query so that when is 0, display
AUTOMATIC otherwise display the groupname

Can someone tell me how to add an IFF in my query?

Cheers

Re: query with IFF question

claudia.inaciofong@googlemail.com wrote:
> Hi,
>
> I have this query below:
>
> select groupname
> from detentgroups
> where groupid = (select notedDestGroupID from DetentRequestedDest
> where groupid =1 and detentnumber =1)
>
> the groupid and detentnumber would be a variable...
>
> My question is when the notedDestGroupID = 0, I should display
> AUTOMATIC, but I don't have value 0 in my detentgroups, so I was
> thinking to put an IFF in my query so that when is 0, display
> AUTOMATIC otherwise display the groupname
>
> Can someone tell me how to add an IFF in my query?
>
> Cheers
>
If you don't have a value of 0 in detentgroups then I would expect, no
matter what, you would have a return of 0 records.

On another note, tho some may disagree, I find subselects attrociously
slow and avoid them to the best of my ability. Access does not seem to
be optimized for subselects...in my experience. I suggest pulling up
the QueryBuilder and adding the two tables and setting the link lines
between groupid and notedDestGroupID and set your criteria on groupid
and detentnumber.

Now, to create a IIF(). I suppose you'd do that by creating an alias.
Instead of
select groupname
you could create an alias like
Select IIF(groupid <> 0,[GroupName],"Automatic) AS GrpName
and you'd reference GrpName instead.

Mermaid
http://www.youtube.com/watch?v=cucKok05nEI