Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2089
  • Last Modified:

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?
0
mceis
Asked:
mceis
3 Solutions
 
mlmccCommented:
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 = ' ';"

mlmcc
0
 
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)
0
 
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
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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

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

Any luck?  I still have found nothing...

Mark
0
 
mlmccCommented:
No luck yet.

mlmcc
0
 
LowfatspreadCommented:
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;"


hth
0
 
RichardCorrieCommented:
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

Regards,

Richard
0
 
m_lazorCommented:
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

Martin
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now