| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format in Excel query
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 -- Med venlig hilsen Peter Hesselager-Olesen SBS-IT Aps |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date format in Excel query
"WHERE (DebJournal.Dato={'" & format(StartDate,"yyyy-mm-dd") & "'})" &
Chr(13) & "" & Chr -- Regards, Tom Ogilvy "Peter Hesselager" wrote: 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 -- Med venlig hilsen Peter Hesselager-Olesen SBS-IT Aps |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date format in Excel query
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;NAME CASE=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: "WHERE (DebJournal.Dato={'" & format(StartDate,"yyyy-mm-dd") & "'})" & Chr(13) & "" & Chr -- Regards, Tom Ogilvy |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date format in Excel query
"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 "Peter Hesselager" wrote: 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;NAME CASE=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: "WHERE (DebJournal.Dato={'" & format(StartDate,"yyyy-mm-dd") & "'})" & Chr(13) & "" & Chr -- Regards, Tom Ogilvy |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date format in Excel query
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. -- Regards, Tom Ogilvy "Peter Hesselager" wrote: 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;NAME CASE=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: "WHERE (DebJournal.Dato={'" & format(StartDate,"yyyy-mm-dd") & "'})" & Chr(13) & "" & Chr -- Regards, Tom Ogilvy |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date format in Excel query
for the query part, any book on SQL.
for the connection strings http://www.carlprothman.net/ Look under Technology, then connection strings -- Regards, Tom Ogilvy "Peter Hesselager" wrote: 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. -- Regards, Tom Ogilvy "Peter Hesselager" wrote: 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;NAME CASE=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: "WHERE (DebJournal.Dato={'" & format(StartDate,"yyyy-mm-dd") & "'})" & Chr(13) & "" & Chr -- Regards, Tom Ogilvy |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| MS Query and date format | Excel Discussion (Misc queries) | |||
| MS Query Date Format | Excel Discussion (Misc queries) | |||
| Excel Query Wizard Date Format | Excel Discussion (Misc queries) | |||
| Excel 2000 date format cannot be set to Australian date format | Excel Discussion (Misc queries) | |||
| Date format - ADO query Access to Excel | Excel Programming | |||
Linear Mode
