Adding "And" clause in SQL string causing SQL Syntax error

[Android]

I am having a frustrating time finding why simply adding a "AND" clause
within my WHERE clause would cause a problem. I am getting an SQL syntax
error.

In the code below, textDate and textTodaysDate are both dates in string
format "yyyy-mm-dd hh:mm:ss"

The following works:
sSql = "SELECT 'Confirmations' AS [dCategory], `DONE
confirmations`.`Sender Name` AS `dFrom`, `DONE confirmations`.Received AS
[dReceived], 1 AS [dAllOnes] "
sSql = sSql & Chr(13) & "" & Chr(10) & "FROM `" & textDBQ & "`.`DONE
confirmations` `DONE confirmations` "
sSql = sSql & Chr(13) & "" & Chr(10) & "WHERE(`DONE
confirmations`.Received>{ts '" & textDate & "'}) "

The following gives an "SQL Syntax error". The errror occurs when I refresh
the query using ths sql statement in the CommendText

sSql = "SELECT 'Confirmations' AS [dCategory], `DONE
confirmations`.`Sender Name` AS `dFrom`, `DONE confirmations`.Received AS
[dReceived], 1 AS [dAllOnes] "
sSql = sSql & Chr(13) & "" & Chr(10) & "FROM `" & textDBQ & "`.`DONE
confirmations` `DONE confirmations` "
sSql = sSql & Chr(13) & "" & Chr(10) & "WHERE(`DONE
confirmations`.Received>{ts '" & textDate & "'} And `DONE
confirmations`.Received<{ts '" & textTodaysDate & "'}) "


Regards,

Android.

 

[Jamie Collins]

...

I am having a frustrating time finding why simply adding a "AND" clause
within my WHERE clause would cause a problem. I am getting an SQL syntax
error.

As I see it, you have two choices:

1) Build the query using the MS Query wizard while recording a macro,
as you originally did, and see what the SQL looks like (awful,
usually).
2) Write some proper SQL e.g. no need to alias the table if there is
only one table used (and what's the point in using the full table name
as the alias anyhow?!), the carriage returns are not needed, use an
unambiguous date format that your provider (did you say which one?)
recognizes as a valid date, use the BETWEEN syntax if your provider
supports it, etc. The SQL a human would write looks more like this
(provider = Jet):

sSql = "SELECT Confirmations AS dCategory, [Sender Name] AS dFrom," &
_
" Received AS dReceived, 1 AS dAllOnes" & _
" FROM [DONE confirmations] WHERE" & _
" Received BETWEEN #01 JUN 2004# AND #30 JUN 2004#;"

Jamie.

--

 

[Android]

I had in fact used MS Query wizard to get the syntax. Being new to SQL, that
is the reason I left in the aliases.

Have not tried the BETWEEN statement.

The data comes from Access tables. Knowing this, is there a better way to
compare dates in this scenario without converting to a text string?

...

I am having a frustrating time finding why simply adding a "AND" clause
within my WHERE clause would cause a problem. I am getting an SQL syntax
error.

As I see it, you have two choices:

1) Build the query using the MS Query wizard while recording a macro,
as you originally did, and see what the SQL looks like (awful,
usually).
2) Write some proper SQL e.g. no need to alias the table if there is
only one table used (and what's the point in using the full table name
as the alias anyhow?!), the carriage returns are not needed, use an
unambiguous date format that your provider (did you say which one?)
recognizes as a valid date, use the BETWEEN syntax if your provider
supports it, etc. The SQL a human would write looks more like this
(provider = Jet):

sSql = "SELECT Confirmations AS dCategory, [Sender Name] AS dFrom," &
_
" Received AS dReceived, 1 AS dAllOnes" & _
" FROM [DONE confirmations] WHERE" & _
" Received BETWEEN #01 JUN 2004# AND #30 JUN 2004#;"

Jamie.

--

 

[Jamie Collins]

...

Have not tried the BETWEEN statement.

Give it a go.

The data comes from Access tables. Knowing this, is there a better way to
compare dates in this scenario without converting to a text string?

Think about it: the SELECT query is one long string. So, in this
scenario you *must* convert it to a string. Using the # characters
(e.g. #01 JUN 2004#) tells the Jet SQL parser it is a date. Depending
on various settings, you could probably get away with '06/01/2004' by
why run the risk when #01 JUN 2004# is unambiguous?

FWIW you could use a native VB Date value as a parameter in an ADO
Command object, but even then under the hood I would wager it was
convert the value to a string for the sake of the SQL parser.

Jamie.

--