Case statement in MS Access

[Guest]

I am using the following query in MS SQL but I cannot use it in MS Access:

SELECT
i.id,
completeid =
CASE
WHEN i.department is null
THEN (SELECT Max(i2.id) FROM importpackage i2
WHERE i2.id < i.id and category is not null)
ELSE i.id
END,
i.department,
i.category,
i.subcategory,
i.classification,
i.icode,
FROM
importpackage i

 

[Douglas J. Steele]

Try:

SELECT
i.id,
IIf(IsNull(i.department), (SELECT Max(i2.id) FROM importpackage i2
WHERE i2.id < i.id and category is not null), i.id) As completeid,
i.department,
i.category,
i.subcategory,
i.classification,
i.icode,
FROM
importpackage i

(although I don't think it'll work: I'm not sure that you can put a select
statement into an IIf like that)

 

[Guest]

Douglas,
Thanks for the quick response. I did try that earlier and could not get it
to work. The IIF statement would work perfectly if it was able to have SQL
embedded in it. I also read about SWITCH and TRANSFORM but I don't think
they will work in this situation. I will continue my research. I may have
to create a function and use that instead.

Thanks for your help.

corwinc

 

[david epsom dot com dot au]

It works for me:

SELECT tblDP_Entities.*,
IIf((select max(idxRecord) from tblDP_Address ) Is Null,1,2) AS b
FROM tblDP_Entities;

Jet 4.0 SQL

(david)




the new field [a] must be included in the select query

 

[John Spencer]

And it works for me.

SELECT FAQ.fID, FAQ.fSubject
, IIf([FID] Mod 2=0,(Select Max(FID) From FAQ AS F WHERE F.FID =
FAQ.FID),Null) AS Expr1
FROM FAQ;

I posted this since my construct is slightly different from David's. Plus I
hadn't read ahead to see his test and I'd constructed my own.

It works for me:

SELECT tblDP_Entities.*,
IIf((select max(idxRecord) from tblDP_Address ) Is Null,1,2) AS b
FROM tblDP_Entities;

Jet 4.0 SQL

(david)




the new field [a] must be included in the select query

Try:

SELECT
i.id,
IIf(IsNull(i.department), (SELECT Max(i2.id) FROM importpackage i2
WHERE i2.id < i.id and category is not null), i.id) As completeid,
i.department,
i.category,
i.subcategory,
i.classification,
i.icode,
FROM
importpackage i

(although I don't think it'll work: I'm not sure that you can put a
select statement into an IIf like that)

 

[Guest]

Golly... you are right. I found my problem. I changed the following in my
IIF statement:

"IsNull(i.department)" to "i.department is null"

....so now my query is as follows:

SELECT
IIf(i.[department] is null,(SELECT Max(i2.id) FROM importpackage i2 WHERE
i2.id < i.id and category is not null),i.[id]) AS completeno,
i.id,
i.department,
i.category,
i.subcategory,
i.classification,
i.icode
FROM
importpackage i



Thanks everyone for your help!

corwinc

And it works for me.

SELECT FAQ.fID, FAQ.fSubject
, IIf([FID] Mod 2=0,(Select Max(FID) From FAQ AS F WHERE F.FID =
FAQ.FID),Null) AS Expr1
FROM FAQ;

I posted this since my construct is slightly different from David's. Plus I
hadn't read ahead to see his test and I'd constructed my own.

It works for me:

SELECT tblDP_Entities.*,
IIf((select max(idxRecord) from tblDP_Address ) Is Null,1,2) AS b
FROM tblDP_Entities;

Jet 4.0 SQL

(david)




the new field [a] must be included in the select query

Try:

SELECT
i.id,
IIf(IsNull(i.department), (SELECT Max(i2.id) FROM importpackage i2
WHERE i2.id < i.id and category is not null), i.id) As completeid,
i.department,
i.category,
i.subcategory,
i.classification,
i.icode,
FROM
importpackage i

(although I don't think it'll work: I'm not sure that you can put a
select statement into an IIf like that)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I am using the following query in MS SQL but I cannot use it in MS
Access:

SELECT
i.id,
completeid =
CASE
WHEN i.department is null
THEN (SELECT Max(i2.id) FROM importpackage i2
WHERE i2.id < i.id and category is not null)
ELSE i.id
END,
i.department,
i.category,
i.subcategory,
i.classification,
i.icode,
FROM
importpackage i