Converting datetime from character string

[Damon Longworth]

Try something similar to:

MyDate = Format(ActiveCell, "yyyy/mm/dd")

--


Damon Longworth - Microsoft Excel MVP

2007 Excel / Access User Conference

Cambridge, England - Nov 29 - Dec 1, 2007


2008 Excel / Access User Conference

Sydney, Australia - Tentatively March 12-14, 2008
www.ExcelUserConference.com/


Hi,

I'm trying to import data from a MS SQL 2005 server. But I'm having
trouble with datetime:
When I give the date hardcoded (e.g. 2007-11-10) for "MyDate" in the
query it works fine.

Stepping true code below gives the value "#10-11-2007#" for MyDate.
Even after giving it a new NumberFormat

This is what I got sofar:
Dim MyDate
Range("A1").Select
Selection.NumberFormat = "yyyy/mm/dd"
MyDate = ActiveCell

With ActiveSheet.QueryTables.Add(Connection:= _

"ODBC;DSN=XXXX;UID=Productie;PWD=XXXXX;APP=Test;WSID=;DATABASE=XXXX" _
, Destination:=Range("A2"))
.CommandText = Array( _
"SELECT O.OrderId, O.DeliveryDate, O.SACity, FROM Orders O
WHERE (O.DeliveryDate>'MyDate')
)

Any idee would be great,
Giel

 

[giel.vanboxtel]

Hi,

I'm trying to import data from a MS SQL 2005 server. But I'm having
trouble with datetime:
When I give the date hardcoded (e.g. 2007-11-10) for "MyDate" in the
query it works fine.

Stepping true code below gives the value "#10-11-2007#" for MyDate.
Even after giving it a new NumberFormat

This is what I got sofar:
Dim MyDate
Range("A1").Select
Selection.NumberFormat = "yyyy/mm/dd"
MyDate = ActiveCell

With ActiveSheet.QueryTables.Add(Connection:= _

"ODBC;DSN=XXXX;UID=Productie;PWD=XXXXX;APP=Test;WSID=;DATABASE=XXXX" _
, Destination:=Range("A2"))
.CommandText = Array( _
"SELECT O.OrderId, O.DeliveryDate, O.SACity, FROM Orders O
WHERE (O.DeliveryDate>'MyDate')
)

Any idee would be great,
Giel

 

[giel.vanboxtel]

Sorry, this is not working

Regards Giel

 

[giel.vanboxtel]

Sorry, this is not working

Regards Giel

I have changed MyDate into a Double
This gives the proper value when stepping through the code.
But it does not give the value to MyDate in the Query.

Is there a way to solve this?

Giel

 

[giel.vanboxtel]

I have changed MyDate into a Double
This gives the proper value when stepping through the code.
But it does not give the value to MyDate in the Query.

Is there a way to solve this?

Giel

After a little search I found the trick:

'" & MyDate & "'

Now it works fine