SQL syntax
 

Im having trouble writing a dynamic SQL statement and was hoping someon
knew where I could get some help on how Excel breaks up the string.

.CommandText = Array( _
"SELECT COUNT(*)" & Chr(13) & "" & Chr(10) & _
"FROM `P:\Capacity Planning`.InternalBORIssue
InternalBORIssues" & Chr(13) & "" & Chr(10) & _
"WHERE (InternalBORIssues.Date={ts '2005-06-10 00:00:00'}) AN
(InternalBORIssues.intext=)" & mine & _
)
I keep getting seperator expected or end of line expectd also the las
where statement doesnt seem to work either, how do I use variables?
Thanks in advance
Jef

--
Jeffie
-----------------------------------------------------------------------
Jeffiec's Profile: http://www.excelforum.com/member.php...fo&userid=2429
View this thread: http://www.excelforum.com/showthread.php?threadid=37900

SQL syntax
 

Implementations of SQL vary by the source database, but generally you do not
need the Array() nor the Chr(13) & Chr(10); the biggest trick is to handle
any text criteria or parameters properly by inserting (normally) single
quotes; so try this:

..CommandText = "SELECT COUNT(*) FROM 'P:\Capacity Planning'.InternalBORIssues
InternalBORIssues WHERE (InternalBORIssues.Date={ts '2005-06-10 00:00:00'})
AND (InternalBORIssues.intext='" & mine & "')

around & mine & please note the single quotes around the double quotes.

"Jeffiec" wrote:


Im having trouble writing a dynamic SQL statement and was hoping someone
knew where I could get some help on how Excel breaks up the string.

.CommandText = Array( _
"SELECT COUNT(*)" & Chr(13) & "" & Chr(10) & _
"FROM `P:\Capacity Planning`.InternalBORIssues
InternalBORIssues" & Chr(13) & "" & Chr(10) & _
"WHERE (InternalBORIssues.Date={ts '2005-06-10 00:00:00'}) AND
(InternalBORIssues.intext=)" & mine & _
)
I keep getting seperator expected or end of line expectd also the last
where statement doesnt seem to work either, how do I use variables?
Thanks in advance
Jeff


--
Jeffiec
------------------------------------------------------------------------
Jeffiec's Profile: http://www.excelforum.com/member.php...o&userid=24296
View this thread: http://www.excelforum.com/showthread...hreadid=379009