My SQL String is not working in my vba code!!! (1 Viewer)

[armesca]

This is a small part of my code where I am pulling all the columns below from a query I created [REG - Master - Summary - Final]. I attempt to run and get a syntax error(missing operator). I think my error is near the end of the string in my WHERE statement, or my LIKE statement. Any thoughts??

Thanks!

strSQL = "Select [Fiscal Year/Program], [Amount Appropriated], [SumOfSumOfAllocation], [SumOfCurrent Obligated Amount], [SumOfDraw Downs], [SumOfAward Balance], [SumOfCurrent Holds], [SumOfAvailable Funds], [SumOfNumber of Awards], [SumOfSumOfNumber of Awards with Holds], [Pct of Draw Downs], [Pct of Award Balance], [Pct of Current Holds], [Pct of Available Funds], [Pct of Award Balance on Hold], [Pct of Award Balance Available] From [REG - Master - Summary - Final] WHERE [Region] = '" & stRegions & "'AND WHERE [Fiscal Year/Program]LIKE " & mycrit

 

[spikepl]

SQL is just like VBA - it needs spaces to understand what you write, so check the ] and the LIKE nearby .and also the last AND

As to checking - a good method is to
debug.print strSQL then you can directly see what it looks like

 

[boblarson]

Also, why are you using LIKE here:

'AND WHERE [Fiscal Year/Program]LIKE " & mycrit

It makes no sense. You either have it EQUAL to mycrit or if you are going to use LIKE you need to use wildcards (unless you intend to have those typed in or something). How is mycrit built?

 

[armesca]

I have my crit below built into a case statement for 14 different excel worksheets. I pasted three below for brevity

For j = 1 To 14
Select Case j
Case 1
strProgram = "DLSGP Summary"
strProgram_detail = "DLSGP Details"
mycrit = "'*Real*' Or '*Driver's*'"
objExcel.ActiveSheet.Range("A12").Value = "CFO Data as of " & Forms.Form1.txt_cfodate.Value
Case 2
strProgram = "EMPG Summary"
strProgram_detail = "EMPG Details"
mycrit = "'*Emergency Management Performance*'"
Case 3
strProgram = "EOC Summary"
strProgram_detail = "EOC Details"
mycrit = "'*Emergency Operations Center*'"

 

[armesca]

I spaced out my SQL code, and it stills seems to not like it. If anyone has any other ideas, I would gladly try them. Thanks.

 

[boblarson]

What did it print when you put in

Debug.Print strSQL

just after the place where you assign that SQL to the string? Post that (you'll find it in the VBA IMMEDIATE WINDOW) so we can see what the resulting string is. That can tell us a lot (as Spike as said).

 

[spikepl]

mycrit = "'*Real*' Or '*Driver's*'" is gonna kill you But yes, show the actual output

 

[boblarson]

Oooh, Drivers with an apostrophe is BADDDDDDD... Good spot

 

[armesca]

Ok, I think I did it correctly and pasted it below:

Select [Fiscal Year/Program], [Amount Appropriated], [SumOfSumOfAllocation], [SumOfCurrent Obligated Amount], [SumOfDraw Downs], [SumOfAward Balance], [SumOfCurrent Holds], [SumOfAvailable Funds], [SumOfNumber of Awards], [SumOfSumOfNumber of Awards with Holds], [Pct of Draw Downs], [Pct of Award Balance], [Pct of Current Holds], [Pct of Available Funds], [Pct of Award Balance on Hold], [Pct of Award Balance Available] From [REG - Master - Summary - Final] WHERE [Region] = 'Region I' AND WHERE [Fiscal Year/Program] LIKE ''*Real*' Or '*Driver's*''

 

[boblarson]

Well, that did shed light on it. You have two WHERE in there. You can only have one WHERE word in this. I didn't see it before.

 

[boblarson]

So here it is with the spacings fixed:

[COLOR=black][FONT=Verdana]strSQL = "Select [Fiscal Year/Program], [Amount Appropriated], [SumOfSumOfAllocation], [SumOfCurrent Obligated Amount], [SumOfDraw Downs], [SumOfAward Balance], [SumOfCurrent Holds], [SumOfAvailable Funds], [SumOfNumber of Awards], [SumOfSumOfNumber of Awards with Holds], [Pct of Draw Downs], [Pct of Award Balance], [Pct of Current Holds], [Pct of Available Funds], [Pct of Award Balance on Hold], [Pct of Award Balance Available] From [REG - Master - Summary - Final] WHERE [Region] = '" & stRegions & "' AND [Fiscal Year/Program] LIKE " & mycrit [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]

[/FONT][/COLOR]

​

You may still need to change this part:

mycrit = "'*Real*' Or '*Driver's*'"


to this:

mycrit = "'*Real*' Or '*Driver*'"

 

[armesca]

I am still fairly new to coding. Can I simply take the second WHERE Out? Also, could you explain the ' in Driver's and why it is bad? Thanks so much for your help

 

[armesca]

It is working! Thanks!

 

[boblarson]

The apostrophe can be a problem when you also are using *' for surrounding the values. It will think it has a missing quote.

 

[spikepl]

To build a query, a short cut can be to use the query builder: that should save you ftom a number of syntax errors. You can then switch to SQLview, and copy the code from there.