Access SQL to Transact-SQL help please

[Ela]

I am converting some Access queries to SQL Server. I am
trying to say that if the data (which are numbers denoting
legislative Districts) is a single digit number (from 0 to
9) then put a "0" as a prefix to it, for eg: if 1 then
make it 01, if 2 then make it 02.
The rest of the code says to include 2 other district
numbers and the result will be for eg: "12, 23, 31". This
is because some records have only 1 value and some have
upto three Legislative District numbers separated by
commas, depending on whether the project is handled by 1
District or 3 different Districts.

Following is the SQL from Access that achives what I want
to do. How would I change the syntax to Transact SQL so as
to make it work in SQL Server. Please help.
Thanks
Ela

SELECT IIf(Len([LegDist])=1,"0" & [LegDist],[LegDist]) AS
NewLegDist
FROM table

 

[Douglas J. Steele]

The equivalent of IIf in SQL Sever would be the Case statement:

CASE LEN(LegDist)
WHEN 1 THEN '0' + LegDist
ELSE LegDist
END

 

[Guest]

Thank You very much for your help. That worked.
Ela

-----Original Message-----
The equivalent of IIf in SQL Sever would be the Case statement:

CASE LEN(LegDist)
WHEN 1 THEN '0' + LegDist
ELSE LegDist
END

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I am converting some Access queries to SQL Server. I am
trying to say that if the data (which are numbers denoting
legislative Districts) is a single digit number (from 0 to
9) then put a "0" as a prefix to it, for eg: if 1 then
make it 01, if 2 then make it 02.
The rest of the code says to include 2 other district
numbers and the result will be for eg: "12, 23, 31". This
is because some records have only 1 value and some have
upto three Legislative District numbers separated by
commas, depending on whether the project is handled by 1
District or 3 different Districts.

Following is the SQL from Access that achives what I want
to do. How would I change the syntax to Transact SQL so as
to make it work in SQL Server. Please help.
Thanks
Ela

SELECT IIf(Len([LegDist])=1,"0" & [LegDist],[LegDist]) AS
NewLegDist
FROM table

.