Excel + SQL + Date problem

[murphyc]

I have wrote this code. But isn't work. What wrong?


startdate = "{d '2003-07-17'}"

"SELECT *" & Chr(13) & "" & Chr(10) & "FROM master" & Chr(13) & "" & Chr(10) & "WHERE (delh.date = [" & startdate & "])"

 

[ETID]

Lose the brackets...and maybe the single quotes also


startdate = "'2003-07-17'"

You may need to enclose the date in # symbols for some database types.

 

[murphyc]

Display the following error

Error message '1004'
ODBC error

 

[SkipVought]

Hi,

1. get rid of crlf stuff -- it adds nothing and detracts from ease of reading.

2. I can't remember whether or not you need tics around dates in SQL statements -- and it might vary by language, but

startdate = "2003-07-17"
sQuery = "SELECT * FROM master WHERE (delh.date = [" & startdate & "])"

All your date is, is a string to insert into the query string.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[TonyJollans]

Hi murphyc,

To add to the good advice from ETID and Skip ..

Syntax might depend a little on where the data is coming from via ODBC but I wouldn't expect you to need square brackets round a literal (date or otherwise}.

Enjoy,
Tony

 

[lucrahouse]

The correct way to write a SQL statement is as follows:

sQuery = "SELECT * FROM master WHERE date = '17-jul-2003'"
now try this. If it doesn't work, then it might be your connection to the database. Does other SQL statements work?
Lets say that this works.

 

[clayton74]

Private Sub CommandButton1_Click()
Dim conn As New Connection
Dim rec As New Recordset
Dim ws As Worksheet
Dim sql$
Dim strcompany
Dim startdate

Set ws = ThisWorkbook.Worksheets("sheet2&quot
strcompany = ws.Cells(1, 1).Value
If strcompany = "" Then
startdate="2003-07-01"
conn.Open "provider=msdasql.1;driver=sql server;server=SERVERNAME;database=DATABASE"
End If

SQL = "SELECT * FROM master WHERE (delh.date = '" & startdate & "')"

rec.Open sql, conn
'rec.Close


End Sub

Worked for me Type in your servername and database