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_location\", False, False, "dBase IV")
Set rs = db.OpenRecordset(strSQL)

Sheets(SheetName).Range("A5").CopyFromRecordset rs

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Don't know how DBase does it but SQL is

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 = ' ';"

mceisAuthor Commented:
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)
mceisAuthor Commented:
I also tried:

select createby, sum(do case case(createby='Username1' 1) otherwise 0 endcase) from ...

which also generates a missing operator error
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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?

mceisAuthor Commented:
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.
Don't know.  Let me research a little further.

mceisAuthor Commented:
My previous post was supposed to have the "if" inside of the parentheses.

Any luck?  I still have found nothing...

No luck yet.

it looks like you haven't specified a group by
in the sql statement and that's probably what its objecting to

you need to add

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 = ' '
group by createby
order by createby;"


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
If you are using DAO, CASE is not a valid operand.  You have to use iif, thus

strSQL = "select createby, sum(iif(createby = 'UserName1', 1, 0)) as U1, sum(iif(createby = 'UserName2', 1, 0 )) as U2 from contact1.dbf where department = ' '
group by createby
order by createby


If I understand your code you want to know how many items were created by different users.

Then another possibility can be:

strSQL = "select createby, Count(*) from contact1.dbf where department = ' ' group by createby"

As this gives less columns you may want to make this a cross-table report afterwards

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.