using a nested select as "true condition" for IIF

[mitchmcc]

I cannot get a nested select statement to work as the "true" condition of an IIF statement, e.g.

SELECT [Certification List].ID, 
[Certification List].Certification, 
[Group List].Group, 
[Group List].ID 
FROM [Group List] 
INNER JOIN ([Certification List] 
INNER JOIN [Group Certifications] ON [Certification List].ID=[Group Certifications].Certification_Id) 
ON [Group List].ID=[Group Certifications].Group_Id 
WHERE ([Group List].ID=IIf(IsNull(forms.[Employee Certification Tracking].txtGroupCertsGroupId),(SELECT Min([Group List].ID) AS MinOfID FROM [Group List]),CInt(forms.[Employee Certification Tracking].txtGroupCertsGroupId)));

When I hard-code the nested select as "3", it works fine.

Does anyone know if this *should* work?

Thanks,

Mitch

[June7]

What goes wrong - error message, wrong results, nothing?

I don't think an IIf in WHERE will work. My attempts always fail. Create a new field with expression that will return True or False then set the criteria for that field to whichever you want.

[mitchmcc]

The Iff worked fine for me, with a hard-coded "true" condition... but using the nested select did not.

[June7]

Wow, it does work! I guess I just always gave up on IIf in criteria or I have forgotten where I succeeded.

I also got a SELECT in the criteria to work.

I can't see anything wrong with your query. I would have to test with data if you want to provide.