?
Solved

Case statement not working when accessing a dBase file

Posted on 2003-03-31
13
Medium Priority
?
2,031 Views
Last Modified: 2010-08-05
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
Comment
Question by:mceis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 8240470
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
 

Author Comment

by:mceis
ID: 8245979
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
 

Author Comment

by:mceis
ID: 8246092
I also tried:

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

which also generates a missing operator error
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 101

Expert Comment

by:mlmcc
ID: 8255802
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
 

Author Comment

by:mceis
ID: 8255960
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 8257530
Don't know.  Let me research a little further.

mlmcc
0
 

Author Comment

by:mceis
ID: 8300773
My previous post was supposed to have the "if" inside of the parentheses.

Any luck?  I still have found nothing...

Mark
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8304104
No luck yet.

mlmcc
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 672 total points
ID: 8438863
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
 
LVL 10

Assisted Solution

by:RichardCorrie
RichardCorrie earned 664 total points
ID: 8494808
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
 

Assisted Solution

by:m_lazor
m_lazor earned 664 total points
ID: 8748294
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question