SQL query syntax

[Wim]

I try to run an SQL query in VBA code, to perform a query on an Excel
table and have the result added as an Excel table in the same sheet (to
range j1). (see code below) The SELECT statement comes into the
variable strQuery.

Everything works fine if I use the Microsoft Query syntax for the
query.

Eg in a simplified version that syntax would be:

(1) SELECT ... FROM ... WHERE Name="An" And Age="26" Or Name="John" and
Age="26"

But it doesn't work with the following statement, which in SQL means
the same:

(2) SELECT ... FROM ... WHERE (Name="An" Or Name="John") and Age="26"

If I go into the Microsoft Query program and I enter manually query (2)
in the SQL editor, it translates it automatically into query (1), so
there query (2) does work.
However, in my VBA code I assign the query (2) to strQuery, and then I
get an error message. As I said, I don't get this error message with
query (1). It seems in the VBA code I can only assign the (1) syntax to
strQuery, because the code doesn't know how to handle syntax (2).

Now you may say, why don't you just work with a query syntax like (1)?
Because with much more complicated queries, queries like (2) are much
shorter and easy to build.

Anyone knows how to solve this problem?

With
ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC;DSN=Excel-bestanden;DBQ=C:\test.xls;DefaultDir=C:"),
;DriverId=790;MaxBufferSize=2048;PageTimeout=5;")),
Destination:=Range("j1"))
strQuery = ... '
here comes the SELECT statement
.CommandText = Array(strQuery)
.Name = "Query1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

 

[Harald Staff]

Hi

I see two potential problems. Apoloigies if you know this already:

VBA strings start and end with the sign " , so if you need " in the middle
of the string you have to write it different. This will generate an error:

Dim StrSQL As String
StrSQL = "They call me "Phantom""
MsgBox StrSQL

because the string ends at the " before Phantom. This will work:

Dim StrSQL As String
StrSQL = "They call me ""Phantom"""
MsgBox StrSQL

Standard SQL use single quotes around string variables and nothing around
numeric variables. If Age is a numeric field then the syntax would be

WHERE (Name='An' OR Name='John') And Age=26

This should also work

WHERE Name IN ('An','John') And Age=26

HTH. Best wishes Harald

 

[AA2e72E]

In general, you should use single quotes to wrap strings (although some
drivers are not fussy).

Why is Age in quotes? Is it not numeric?

If you want an even shorter SQL statement, try:

SELECT ... FROM ... WHERE Name IN ('An' ,'John') and Age=26;

 

[Wim]

Thanks Harald, but I knew. The SQL examples in my mail are just
"logical" examples, my actual code is written correctly (without "").

My question is not related to characters, those are OK. It's rather
about the logical structure of the SQL statement.

Instead of

A and X or B and X or C and X or D

I'd like to use

(A or B or C or D) and X

 

[Wim]

Your IN statement is very nice. I tried it, but unfortunately it only
works for exact strings.

I would like to have something like:

SELECT ... FROM ... WHERE Name IN ('*An*' ,'John') and Age=26

so not only An but also Ann will be chosen


I tried already this one:

SELECT ... FROM ... WHERE Name Like "An" And Age=26 Or Name Like "John"
and
Age=26

This works, but

SELECT ... FROM ... WHERE (Name Like "An" Or Name Like "John") and
Age=26

doesnt' work

 

[Ardus Petus]

Microsoft Query is dumb and will automatically turn Syntax 2 into syntax 1.

But you can apply syntax 2 to youur SQL string in VBA code: it works fine.

HTH

 

[Wim]

Ardus,

That's precisely what goes wrong. MS Query indeed does turn syntax 2
into 1 when you do it into the MS Query window.
But when running my code (so without opening MS Query) it doesn't seem
to do it anymore. Syntax 2 gives an error message.

What works in my VBA code is:

SELECT `Sheet1$`.Project, `Sheet1$`.Alt, `Sheet1$`.Ed, `Sheet1$`.Type,
`Sheet1$`.Unit, `Sheet1$`.Des FROM `Sheet1$` `Sheet1$`
WHERE (`Sheet1$`.Des Like '%ga%') AND (`Sheet1$`.Project Like '%5%') OR
(`Sheet1$`.Des Like '%zt%') AND (`Sheet1$`.Project Like '%5%')

My SQL query which doesn't work is this one: (though it is correct,
because MS Query understands it)

SELECT `Sheet1$`.Project, `Sheet1$`.Alt, `Sheet1$`.Ed, `Sheet1$`.Type,
`Sheet1$`.Unit, `Sheet1$`.Des FROM `Sheet1$` `Sheet1$` WHERE
(`Sheet1$`.Project like '%5%') And ((`Sheet1$`.Des like '%ga%') or
(`Sheet1$`.Des like '%zt%'))

 

[AA2e72E]

You can only match anything beginning with 'An' or ending with 'An' in a
single SQL statement, thus:

SELECT ... FROM ... WHERE (Name = 'John' or Name like 'An%') and Age=26;

or

SELECT ... FROM ... WHERE (Name = 'John' or Name like '%An') and Age=26;

If you want anything that has An in it, try:

SELECT ... FROM ... WHERE (Name = 'John' or 0<> INSTR(Name,'An') and Age=26;

The latter will work with the Text and Excel drivers: INSTR is not
universally supported.


In case you have not tweakedit yet, all SQL statements should end with
semi-colon (but some drivers are not fussy).

 

[Wim]

Your idea to use INSTR in an OR construction works perfectly well,
thanks a lot for that.

The code I use now is:

SELECT `Sheet1$`.Project, `Sheet1$`.Alt, `Sheet1$`.Ed, `Sheet1$`.Type,
`Sheet1$`.Unit, `Sheet1$`.Des FROM `Sheet1$` `Sheet1$`
WHERE (`Sheet1$`.Project like '%5%') And (0<>
INSTR(`Sheet1$`.Unit,'ga') or 0<> INSTR(`Sheet1$`.Unit,'zt'))

and it works!

Any references about this Excel SQL on the internet?

 

[AA2e72E]

You could use Google to search for "EXCEL" "SQL".

You might be better off buying a book on SQL: SQL in 10 Minutes (SAMS) or
SQL Pocket Reference (O'Reilley) are good books to start with. Almost all of
the Excel VBA keywords are available for use in SQL statements if you are
using the Excel driver.

It comes with experience ... experience comes with trying ...

 

[Harald Staff]

Thanks Harald, but I knew. The SQL examples in my mail are just
"logical" examples, my actual code is written correctly (without "").

If illustational examples provided are full of silly errors, deliberate or
not, then it's difficult to spot the real problem.

Best wishes Harald