Date format in Excel query

[Guest]

Hi Group.
I created this query with Excel Macro, and it works.
Then I put a date in a cell in Excel, and assigned the value to Variable
StartDate, and it works.
Now I want to put the StartDate into the WHERE instead of 2005-01-04, but
that does NOT work.
Any Ideas ?

"SELECT DebJournal.Konto, DebJournal.Dato, DebJournal.Varebeløb" & Chr(13) &
"" & _
Chr(10) & "FROM DebJournal DebJournal" & Chr(13) & "" & Chr(10) &
"WHERE (DebJournal.Dato>={d '2005-01-04'})" & Chr(13) & "" & Chr(10) & "ORDER
BY DebJournal.Konto, DebJournal.Dato" _
)
.Refresh BackgroundQuery:=False

 

[Guest]

"WHERE (DebJournal.Dato>={'" & format(StartDate,"yyyy-mm-dd") & "'})" &
Chr(13) & "" & Chr

 

[Guest]

Sorry, it didn´t work yet.

Here is the complete statement after implementing your suggestion.
I wonder, if it is the national default for dates, that is causing the
problem ?

Dim StartDate As Date
StartDate = Range("H1").Value

With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=C5
IMBOX;DBQ=c:\c5-4-sp0-hf5\c5data.imb;CODEPAGE=1252;CHARTYPE=VARCHAR;NAMECASE=Unchanged;DISPLAYNAME=DICT;HIGHASCII=TRUE;B" _
), Array("LANKISNULL=FALSE;DEBUG=FALSE;"))
.CommandText = Array( _
"SELECT DebJournal.Konto, DebJournal.Dato, DebJournal.Varebeløb" &
Chr(13) & "" & _
Chr(10) & "FROM DebJournal DebJournal" & Chr(13) & "" & Chr(10) &
"WHERE (DebJournal.Dato>={'" & Format(StartDate, "yyyy-mm-dd") & "'})" &
Chr(13) & "" & Chr(10) & "ORDER BY DebJournal.Konto, DebJournal.Dato" _
)
.Refresh BackgroundQuery:=False
End With

--
Med venlig hilsen
Peter Hesselager-Olesen
SBS-IT Aps



"Tom Ogilvy" skrev:

 

[Guest]

"WHERE (DebJournal.Dato>={d '2005-01-04'})"
is the model

I gave you
"WHERE (DebJournal.Dato>={'" & format(StartDate,"yyyy-mm-dd") & "'})" &

iIf you look closely, you can see that when I was editing your original
string, a character or two got inadvertently deleted. (d space)

"WHERE (DebJournal.Dato>={d '" & format(StartDate,"yyyy-mm-dd") & "'})" &

so now we test out the revision:

Sub ABC()
Dim StartDate As Date
s1 = "WHERE (DebJournal.Dato>={d '2005-01-04'})"
StartDate = CDate("01/04/2005")
s = "WHERE (DebJournal.Dato>={d '" & Format(StartDate, "yyyy-mm-dd") & "'})"
Debug.Print s
Debug.Print s1
End Sub

produces:

WHERE (DebJournal.Dato>={d '2005-01-04'})
WHERE (DebJournal.Dato>={d '2005-01-04'})

Try it that way. Sorry for the editing error.

--
Regards,
Tom Ogilvy

Sorry, it didn´t work yet.

Here is the complete statement after implementing your suggestion.
I wonder, if it is the national default for dates, that is causing the
problem ?

Dim StartDate As Date
StartDate = Range("H1").Value

With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=C5
IMBOX;DBQ=c:\c5-4-sp0-hf5\c5data.imb;CODEPAGE=1252;CHARTYPE=VARCHAR;NAMECASE=Unchanged;DISPLAYNAME=DICT;HIGHASCII=TRUE;B" _
), Array("LANKISNULL=FALSE;DEBUG=FALSE;"))
.CommandText = Array( _
"SELECT DebJournal.Konto, DebJournal.Dato, DebJournal.Varebeløb" &
Chr(13) & "" & _
Chr(10) & "FROM DebJournal DebJournal" & Chr(13) & "" & Chr(10) &
"WHERE (DebJournal.Dato>={'" & Format(StartDate, "yyyy-mm-dd") & "'})" &
Chr(13) & "" & Chr(10) & "ORDER BY DebJournal.Konto, DebJournal.Dato" _
)
.Refresh BackgroundQuery:=False
End With

--
Med venlig hilsen
Peter Hesselager-Olesen
SBS-IT Aps



"Tom Ogilvy" skrev:

 

[Guest]

Hi
That did the trick !
Thank's !

I have tried to find info on how to build / edit these formula's - would you
have a link ??
--
Med venlig hilsen
Peter Hesselager-Olesen
SBS-IT Aps



"Tom Ogilvy" skrev:

"WHERE (DebJournal.Dato>={d '2005-01-04'})"
is the model

I gave you
"WHERE (DebJournal.Dato>={'" & format(StartDate,"yyyy-mm-dd") & "'})" &

iIf you look closely, you can see that when I was editing your original
string, a character or two got inadvertently deleted. (d space)

"WHERE (DebJournal.Dato>={d '" & format(StartDate,"yyyy-mm-dd") & "'})" &

so now we test out the revision:

Sub ABC()
Dim StartDate As Date
s1 = "WHERE (DebJournal.Dato>={d '2005-01-04'})"
StartDate = CDate("01/04/2005")
s = "WHERE (DebJournal.Dato>={d '" & Format(StartDate, "yyyy-mm-dd") & "'})"
Debug.Print s
Debug.Print s1
End Sub

produces:

WHERE (DebJournal.Dato>={d '2005-01-04'})
WHERE (DebJournal.Dato>={d '2005-01-04'})

Try it that way. Sorry for the editing error.

 

[Guest]

for the query part, any book on SQL.

for the connection strings

http://www.carlprothman.net/

Look under Technology, then connection strings