mceis
asked on
Case statement not working when accessing a dBase file
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_locati
Set rs = db.OpenRecordset(strSQL)
Sheets(SheetName).Range("A
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?
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_locati
Set rs = db.OpenRecordset(strSQL)
Sheets(SheetName).Range("A
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?
ASKER
Thanks, I just tried that syntax (for 1 user) and I still get this error:
Error: Syntax error (missing operator) in query expression 'sum(case createby when 'ERVICH' then 1 else 0 end)'. (State:37000, Native Code: FFFFF3E4)
Error: Syntax error (missing operator) in query expression 'sum(case createby when 'ERVICH' then 1 else 0 end)'. (State:37000, Native Code: FFFFF3E4)
ASKER
I also tried:
select createby, sum(do case case(createby='Username1' 1) otherwise 0 endcase) from ...
which also generates a missing operator error
select createby, sum(do case case(createby='Username1' 1) otherwise 0 endcase) from ...
which also generates a missing operator error
I suspect you can't use SUM with a CASE.
Can you build a query that does the case then do a sum of the query?
mlmcc
Can you build a query that does the case then do a sum of the query?
mlmcc
ASKER
Even when I remove the SUM and try:
select do case case createby = 'UserName1' 1 otherwise 0 endcase from ...
I still get the error. The CASE function doesn't work. What's worse is that the if...then...else won't run either:
select if(createby = 'UserName1' then 1 else 0 endif)
from ...
also generates a missing operator error.
It's a Borland database engine (dBase IV). Other queries not using a conditional statement as part of the select work fine, so I'm still confused.
select do case case createby = 'UserName1' 1 otherwise 0 endcase from ...
I still get the error. The CASE function doesn't work. What's worse is that the if...then...else won't run either:
select if(createby = 'UserName1' then 1 else 0 endif)
from ...
also generates a missing operator error.
It's a Borland database engine (dBase IV). Other queries not using a conditional statement as part of the select work fine, so I'm still confused.
Don't know. Let me research a little further.
mlmcc
mlmcc
ASKER
My previous post was supposed to have the "if" inside of the parentheses.
Any luck? I still have found nothing...
Mark
Any luck? I still have found nothing...
Mark
No luck yet.
mlmcc
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
strSQL = "select createby, sum(case createby when 'UserName1' then 1 else 0 end) as U1, sum(case createby when 'UserName2' then 1 else 0 end) as U2 from contact1.dbf where department = ' ';"
mlmcc