Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding "And" clause in SQL string causing SQL Syntax error
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding "And" clause in SQL string causing SQL Syntax error
"Android" wrote ...
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. -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding "And" clause in SQL string causing SQL Syntax error
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? "Jamie Collins" wrote in message om... "Android" wrote ... 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. -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding "And" clause in SQL string causing SQL Syntax error
"Android" wrote ...
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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
correct syntax for nesting "if", "and", and "vlookup"....if possib | Excel Worksheet Functions | |||
Converting "uppercase" string data to "lower case" in CSV file | Excel Discussion (Misc queries) | |||
nested if contains a syntax error because of a "." | Excel Discussion (Misc queries) | |||
Problem with SQL "Like" clause in Excel | Charts and Charting in Excel | |||
"string too long" error message | Excel Discussion (Misc queries) |