Improve company productivity with a Business Account.Sign Up

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

VBA SQL query syntax

Good Afternoon Experts,

I am having an issue with running a sql query within access 2010 VBA.

What is wrong with it?

strSQL = "SELECT DISTINCT Sum(_ChartDBSource_Template.Jan) AS SumOfJan " & vbCrLf & _
"FROM MOR_ChartDBSource_Template " & vbCrLf & _
"WHERE (((MOR_ChartDBSource_Template.CHARTDB_BIN) Like ""CalcHC"") AND ((_ChartDBSource_Template._GROUP) Like ""*Design B Other*"" Or (_ChartDBSource_Template._GROUP) Like ""*Design C Other*"" Or (_ChartDBSource_Template._GROUP) Like ""*Design CD Other*"" Or (_ChartDBSource_Template._GROUP) Like ""*Design Large Other*"" Or (_ChartDBSource_Template._GROUP) Like ""*Design Other*""));"

Thanks in advance !!
0
UserName935
Asked:
UserName935
3 Solutions
 
COACHMAN99Commented:
not sure what the error is but
try removing the double quotes e.g. LIKE '*Design*'
and why embed linefeeds?

e.g.
strSQL = "SELECT DISTINCT Sum(_ChartDBSource_Template.Jan) AS SumOfJan FROM MOR_ChartDBSource_Template WHERE (((MOR_ChartDBSource_Template.CHARTDB_BIN) Like 'CalcHC') AND ((_ChartDBSource_Template._GROUP) Like '*Design B Other*' Or (_ChartDBSource_Template._GROUP) Like '*Design C Other*' Or (_ChartDBSource_Template._GROUP) Like '*Design CD Other*' Or (_ChartDBSource_Template._GROUP) Like '*Design Large Other*' Or (_ChartDBSource_Template._GROUP) Like '*Design Other*'));"
0
 
MacroShadowCommented:
Remove  all & vbCrLf & _
0
 
hnasrCommented:
May you list a record, and the how do you expect it to appear.
0
 
mbizupCommented:
The double quotes *should* be fine, since you have them in pairs.

Check your spelling/typing /table names.

You have several instances of _ChartDBSource_Template which probably should be MOR_ChartDBSource_Template

(You seem to have omitted a MOR_ prefix in several places)
0
 
PatHartmanCommented:
There is nothing variable about this query so there is no reason for it to not be a querydef.  Even if for some reason you prefer embedded SQL, creating the query and getting out the syntax errors is much easer with the QBE.

If your BE is Jet/ACE it is more efficient to store the queries as querydefs.  That way Access calculates the execution plan the first time you run the query and saves it.  Then every other time you run the query, Access can save the step of calculating a new execution plan.  With embedded SQL, Access must calculate a new execution plan every time you run the query.  The overhead is minimal but inefficient methods eventually build up to make applications sluggish.  If the BE is SQL Server, the query will be sent to the server which will calculation the execution plan so it makes no difference whether the query is a querydef or embedded string.
0
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.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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