SQL syntax

[Jeffiec]

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

 

[K Dales]

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.