If...then in ADP queries

[Guest]

Working with APD files I faced a problem. In one of the old MDB
files I created the following field in a query:

HelpFieldA: IIF([FieldA]>1,[FieldA],[FieldB])

How do I make the equivalent in a ADP query?

Thanks...

 

[Van T. Dinh]

I don't use ADP but AFAIK, ADP uses T-SQL so you need to use the CASE
statement. Something like (***untested***):

SELECT [HelpFieldA] =
CASE
WHEN [FieldA] > 1 THEN [FieldA]
ELSE [FieldB]
END,
....

 

[Guest]

Thank you for the reply it brought me a good step further.

The following works OK:
CASE WHEN [FieldA] 1 THEN [FieldA] ELSE [FieldB] END

But adding the > does not work, so the following is NOT OK:
CASE WHEN [FieldA] >1 THEN [FieldA] ELSE [FieldB] END

Hope for help...

Thanks
GB, DK

I don't use ADP but AFAIK, ADP uses T-SQL so you need to use the CASE
statement. Something like (***untested***):

SELECT [HelpFieldA] =
CASE
WHEN [FieldA] > 1 THEN [FieldA]
ELSE [FieldB]
END,
....

--
HTH
Van T. Dinh
MVP (Access)

Working with APD files I faced a problem. In one of the old MDB
files I created the following field in a query:

HelpFieldA: IIF([FieldA]>1,[FieldA],[FieldB])

How do I make the equivalent in a ADP query?

Thanks...

 

[Van T. Dinh]

That's strange. The syntax is correct.

Here's a very similar example from book on line:

****
USE pubs
GO
SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO
****

Suggest you re-post in the ADP newsgroup in case something different is
required in ADP.

 

[Guest]

I'l sure re-post the problem in the ADP newsgroup

Thank you Van T. Dinh



------------------------------

That's strange. The syntax is correct.

Here's a very similar example from book on line:

****
USE pubs
GO
SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO
****

Suggest you re-post in the ADP newsgroup in case something different is
required in ADP.

--
HTH
Van T. Dinh
MVP (Access)

Thank you for the reply it brought me a good step further.

The following works OK:
CASE WHEN [FieldA] 1 THEN [FieldA] ELSE [FieldB] END

But adding the > does not work, so the following is NOT OK:
CASE WHEN [FieldA] >1 THEN [FieldA] ELSE [FieldB] END

Hope for help...

Thanks
GB, DK

 

[Van T. Dinh]

If you are using a newsreader, the newsgroup is
"microsoft.public.access.adp.sqlserver".