How to exclude? or Include based on True/False

[Guest]

I'm not sure how to go about doing what I need, so I defer to your expertise...

I have a table which I need to query 1) [DestName] or 2) [TransferedTo]
depending on the value of 3) [Transfered]

Can someone help me with the criteria for this. Exactly what I need is this:

If [Transfered] = True then I want [TransferedTo]. There will be a
[DestName] if [Transfered] is True, but I only want the value of
[TransferedTo].

If [Transfered] is False, then I want [DestName].


Any help is appreciated.

 

[Guest]

Forgot to add that [DesName] and [TransferedTo] are text fields while
[Transfered] is a Y/N field.

Cheers

 

[Saran]

Paul,

Below query might do what you expect.

Select iif(transfered,TransferedTo,DestName) from TableName

Regards,
Saran.

 

[Guest]

Saran,

Thanks, this works except now I have a bunch of blanks. Any idea how to get
rid of the blanks?

Cheers

 

[Saran]

Paul,

Couple of ways to do this.

1. Select iif(transfered,TransferedTo,DestName) as MyCol from
TableName Where not iif(transfered,TransferedTo,DestName) is null

2. Select * from (Select iif(transfered,TransferedTo,DestName)
as MyCol from TableName) TN Where not TN.Mycol is null

Use whichever you feel perform better.

Want to get rid of any other values? extend the WHERE condition.

Regards,
Saran.

 

[Guest]

Thanks Saran,

I couldn't get example 2 to work, but I did get the first one to work with
the inclusion of DISTINCT.

Thanks again for your help.