If...then in ADP queries


G

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...
 
Ad

Advertisements

V

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,
....
 
G

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



Van T. Dinh said:
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)



GB said:
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...
 
V

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.
 
G

Guest

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

Thank you Van T. Dinh

:)

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

Van T. Dinh said:
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)



GB said:
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
 
Ad

Advertisements

V

Van T. Dinh

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

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top