Query conditions

[Pietro]

Hi,

In the below query,i need to add an external field called "type" so:
1- If the field "subject" contains the word "(RO)" then the field "type"="RO".
2- If the field "subject" contains the word "(FO)" then the field "type"="FO".
2- If the field "subject" does not contains the word "(FO)" or the word
"(FO)" then the field "type"="E-mail".

the query is:

SELECT [E-mails].ID, [E-mails].[Service Request], [E-mails].[Idle Time],
[E-mails].Owner, [E-mails].Queue, [E-mails].Status, [E-mails].Customer,
[E-mails].Subject, [E-mails].[Opened Date]
FROM [E-mails];

 

[Tom van Stiphout]

On Sun, 15 Jun 2008 09:18:00 -0700, Pietro

You could probably do that with the InStr function to test the subject
field, and two nested iif functions.

-Tom.

 

[Pietro]

Ok ,but how ?

On Sun, 15 Jun 2008 09:18:00 -0700, Pietro

You could probably do that with the InStr function to test the subject
field, and two nested iif functions.

-Tom.

Hi,

In the below query,i need to add an external field called "type" so:
1- If the field "subject" contains the word "(RO)" then the field "type"="RO".
2- If the field "subject" contains the word "(FO)" then the field "type"="FO".
2- If the field "subject" does not contains the word "(FO)" or the word
"(FO)" then the field "type"="E-mail".

the query is:

SELECT [E-mails].ID, [E-mails].[Service Request], [E-mails].[Idle Time],
[E-mails].Owner, [E-mails].Queue, [E-mails].Status, [E-mails].Customer,
[E-mails].Subject, [E-mails].[Opened Date]
FROM [E-mails];

 

[Tom van Stiphout]

On Sun, 15 Jun 2008 10:10:00 -0700, Pietro

Try this:
iif(instr(subject,"(RO)")>0,"RO",iif(instr(subject,"(FO)")>0,"FO","E-Mail")

-Tom.

Ok ,but how ?

On Sun, 15 Jun 2008 09:18:00 -0700, Pietro

You could probably do that with the InStr function to test the subject
field, and two nested iif functions.

-Tom.

Hi,

In the below query,i need to add an external field called "type" so:
1- If the field "subject" contains the word "(RO)" then the field "type"="RO".
2- If the field "subject" contains the word "(FO)" then the field "type"="FO".
2- If the field "subject" does not contains the word "(FO)" or the word
"(FO)" then the field "type"="E-mail".

the query is:

SELECT [E-mails].ID, [E-mails].[Service Request], [E-mails].[Idle Time],
[E-mails].Owner, [E-mails].Queue, [E-mails].Status, [E-mails].Customer,
[E-mails].Subject, [E-mails].[Opened Date]
FROM [E-mails];

 

[Pietro]

It works,thank you...

On Sun, 15 Jun 2008 10:10:00 -0700, Pietro

Try this:
iif(instr(subject,"(RO)")>0,"RO",iif(instr(subject,"(FO)")>0,"FO","E-Mail")

-Tom.

Ok ,but how ?

On Sun, 15 Jun 2008 09:18:00 -0700, Pietro

You could probably do that with the InStr function to test the subject
field, and two nested iif functions.

-Tom.


Hi,

In the below query,i need to add an external field called "type" so:
1- If the field "subject" contains the word "(RO)" then the field "type"="RO".
2- If the field "subject" contains the word "(FO)" then the field "type"="FO".
2- If the field "subject" does not contains the word "(FO)" or the word
"(FO)" then the field "type"="E-mail".

the query is:

SELECT [E-mails].ID, [E-mails].[Service Request], [E-mails].[Idle Time],
[E-mails].Owner, [E-mails].Queue, [E-mails].Status, [E-mails].Customer,
[E-mails].Subject, [E-mails].[Opened Date]
FROM [E-mails];