Using Case When for SQL statement (IIF doesnt work VBA)

[gal4y]

I need to generate a SQL statement for use in a recordset via VBA. I have 89000 records but when I run the query in VBA Module I have one record. The problem is the IIF statement. So I tried to use CASE WHEN but I get a "Missing Operator" Error. Thank you for you assistance in advance.


My code:
Public Function Load() As Double
Dim recnum As Double
Dim db As Database, rs As DAO.Recordset
Dim SQLString As Variant

SQLString = "SELECT Score.ScoreID, IIf([Value_True_30]=Yes,(Format([DValue_30],'Standard'))," _
& " (Format([Score_30],'Standard'))) AS Score30"
& " FROM Score" _
& " ORDER BY Score.ScoreID;"

Set db = CurrentDb
Set rs = db.OpenRecordset(SQLString)
recnum = rs.RecordCount

End Function
______________________
Here is the statement I tried to use:

SQLString = "SELECT Score.ScoreID, (Case When ([Value_True_30]=Yes) THEN [DValue_30] ELSE [Score_30] END ) as [Score30]" _
& " FROM Score ORDER BY Score.ScoreID;"

Thanks
Greg

 

[nicsin]

Hi Greg,

I believe you need to wrap YES with quotes like "YES" in the query.

"SELECT Score.ScoreID, IIf([Value_True_30]=""Yes"",(Format([DValue_30],'Standard'))," _
& " (Format([Score_30],'Standard'))) AS Score30"
& " FROM Score" _
& " ORDER BY Score.ScoreID;"

 

[gal4y]

I still get one record but when I dump the SQL string back into the query I get all the records. It has to do something with the IIF? I read somewhere that you have to use the case when.

Thanks

Greg

 

[cmmrfrds]

Case When is an SQL Server function that can replace the iif functionality in SQL Server. It does not appear from your syntax that you are using SQL Server?