I am using Excel to create some reports off of a dBase IV database using SQL through VBA. It's working fine except when I try to use a CASE statement in the SQL statement. Here's essentially what I'm using to create a report for empty 'department' field counts by user ('createby'):
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strSQL as string
Dim SheetName as string
strSQL = "select createby, sum(case when createby = 'UserName1' then 1 else 0 end) as U1, sum(case when createby = 'UserName2' then 1 else 0 end) as U2 from contact1.dbf where department = ' ';"
Set db = OpenDatabase("i:\db_location\", False, False, "dBase IV")
Set rs = db.OpenRecordset(strSQL)
I keep getting the "Syntax error (missing operator) in query expression..." error message.
Even when I use another SQL query tool (e.g. WinSQL) I am unable to get any conditional statements (CASE, if...then...else) to run without that error.
The dbf file is a Borland dBase IV file. If conditional statements are not supported for some reason, do you have any suggestions on how to create the report above?