IF THEN query in SQL

[richardb]

I am trying to write a query in Transact-SQL to create a user view in my SQL
database. I am trying to populate columns for each "aging" category (30, 60,
90, etc), so the correct age receives the amount due, but other columns are
zero. However, I can't find the correct CASE or IF...THEN syntax. It needs to
say:

If ServiceDateAge BETWEEN 0 AND 29 Then CurrentDue = PatientDue Else
CurrentDue = 0.
If ServiceDateAge BETWEEN 30 and 59 Then 30DayDue = PatientDue Else 30DayDue
= 0.
....etc

I would be grateful for help...Thank you.

 

[Douglas J. Steele]

You'd probably be better off asking in a newsgroup related to SQL Server,
since there are some differences in the SQL used in Access and that in SQL
Server.

 

[richardb]

OK I'll do that. I brought it here because I am using Access as the "front
end" for the report and may actually send the query as a "pass through" from
Access.

 

[Douglas J. Steele]

Pass-through queries use SQL Server's dialect of SQL (or whatever dialect is
applicable to the DBMS being updated), not Access's.

 

[TipTop]

Have you tried IIF()?

Example from Help:
SELECT IIf(PostalCode Between 98101 And 98199, “Localâ€, “Nonlocalâ€)
FROM Publishers

I would assume (dunno) you can nest them - which might serve your needs
if you don't have that many cases.