Time display 1/0/1900 after imported!
 

I created a macro to execute a sub routine to import data from a view in
MS sql 2000. Everything is fine except there is a "time" column in
excel is giving me 1/0/1900. I formatted the column as "time 1:30:50
PM" before the macro is run.
During the importing, I saw the time shown (which is according to the
date/time field in database with data type = time), but as soon as the
macro finishes; the entire column show 1/0/1900.
So, I created another sub routine to converted the date format to time.
It's work on my PC, but doesnot work on my client PC; the error message
complaining of error 13 type mismatch.

Can someone help me with what is my client PC missing? or give me a
different approach on getting the time column to show time entry not
1/0/1900 after imported?

Thanks,


Here are my sub routines

'Show form when start this excel workbook
Sub StartUpExcel()
frmDataXGetDate.Show
End Sub
Sub DataX_ExtractData()
'Create connection
Dim cnH2O As ADODB.Connection
Set cnH2O = New ADODB.Connection
'Create connection string
Dim strConn As String
strConn = "Provider=SQLOLEDB;data Source = H2O;Initial Catalog =
SMXP;Integrated Security=SSPI;"
'Open connection
cnH2O.Open strConn
'Create recordset to hold data
Dim rsSMXP As ADODB.Recordset
Set rsSMXP = New ADODB.Recordset
With rsSMXP
Dim Sdate As Date
Dim Edate As Date
'Get Start date in from user form frmDataXGetDate
Sdate = frmDataXGetDate.txtSDate.Text
'Get End date in from user form frmDataXGetDate
Edate = frmDataXGetDate.txtEDate.Text
..Activeconnection = cnH2O
'Select statement to create record set
..Open "Select * From V_DataX_Export where CollectDate between "
& "'" & Sdate & " ' and '" & Edate & "'"
'insert data from record set
Sheet1.Range("A2").CopyFromRecordset rsSMXP
..Close
End With
cnH2O.Close 'close connection
Set rsSMXP = Nothing 'empty record set
Set cnH2O = Nothing 'empty
End Sub
' Change column F to time from cell F2: on
Sub DateToTime()
Dim cel As Range, tblRange As Range
Set tblRange = Sheet1.Range("F:F")
For Each cel In tblRange
cel.Value = Format(cel.Value, "hh:mm:ss AM/PM")
Next cel
End Sub


--
kdoan
------------------------------------------------------------------------
kdoan's Profile: http://www.excelforum.com/member.php...o&userid=34230
View this thread: http://www.excelforum.com/showthread...hreadid=539969

Time display 1/0/1900 after imported!
 

1/0/1900 is how zero is displayed in a date number format. Check the format
and numerical values of the incoming data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"kdoan" wrote in
message ...

I created a macro to execute a sub routine to import data from a view in
MS sql 2000. Everything is fine except there is a "time" column in
excel is giving me 1/0/1900. I formatted the column as "time 1:30:50
PM" before the macro is run.
During the importing, I saw the time shown (which is according to the
date/time field in database with data type = time), but as soon as the
macro finishes; the entire column show 1/0/1900.
So, I created another sub routine to converted the date format to time.
It's work on my PC, but doesnot work on my client PC; the error message
complaining of error 13 type mismatch.

Can someone help me with what is my client PC missing? or give me a
different approach on getting the time column to show time entry not
1/0/1900 after imported?

Thanks,


Here are my sub routines

'Show form when start this excel workbook
Sub StartUpExcel()
frmDataXGetDate.Show
End Sub
Sub DataX_ExtractData()
'Create connection
Dim cnH2O As ADODB.Connection
Set cnH2O = New ADODB.Connection
'Create connection string
Dim strConn As String
strConn = "Provider=SQLOLEDB;data Source = H2O;Initial Catalog =
SMXP;Integrated Security=SSPI;"
'Open connection
cnH2O.Open strConn
'Create recordset to hold data
Dim rsSMXP As ADODB.Recordset
Set rsSMXP = New ADODB.Recordset
With rsSMXP
Dim Sdate As Date
Dim Edate As Date
'Get Start date in from user form frmDataXGetDate
Sdate = frmDataXGetDate.txtSDate.Text
'Get End date in from user form frmDataXGetDate
Edate = frmDataXGetDate.txtEDate.Text
Activeconnection = cnH2O
'Select statement to create record set
Open "Select * From V_DataX_Export where CollectDate between "
& "'" & Sdate & " ' and '" & Edate & "'"
'insert data from record set
Sheet1.Range("A2").CopyFromRecordset rsSMXP
Close
End With
cnH2O.Close 'close connection
Set rsSMXP = Nothing 'empty record set
Set cnH2O = Nothing 'empty
End Sub
' Change column F to time from cell F2: on
Sub DateToTime()
Dim cel As Range, tblRange As Range
Set tblRange = Sheet1.Range("F:F")
For Each cel In tblRange
cel.Value = Format(cel.Value, "hh:mm:ss AM/PM")
Next cel
End Sub


--
kdoan
------------------------------------------------------------------------
kdoan's Profile:
http://www.excelforum.com/member.php...o&userid=34230
View this thread: http://www.excelforum.com/showthread...hreadid=539969

Time display 1/0/1900 after imported!
 

Thanks Jon, I check and the incoming data from my SQL's View = 5:30:00
PM.
ANy other suggestion?
Thanks,


--
kdoan
------------------------------------------------------------------------
kdoan's Profile: http://www.excelforum.com/member.php...o&userid=34230
View this thread: http://www.excelforum.com/showthread...hreadid=539969

Time display 1/0/1900 after imported!
 

Is it text, or a real numerical value for time? I suspect it's coming across
as text, to which Excel assigns a numerical value of zero.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"kdoan" wrote in message
...

Thanks Jon, I check and the incoming data from my SQL's View = 5:30:00
PM.
ANy other suggestion?
Thanks,


--
kdoan
------------------------------------------------------------------------
kdoan's Profile:
http://www.excelforum.com/member.php...o&userid=34230
View this thread: http://www.excelforum.com/showthread...hreadid=539969

Time display 1/0/1900 after imported!
 

Or if it's just a time, the integer value of that time is zero.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"Jon Peltier" wrote in message
...
Is it text, or a real numerical value for time? I suspect it's coming
across as text, to which Excel assigns a numerical value of zero.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"kdoan" wrote in
message ...

Thanks Jon, I check and the incoming data from my SQL's View = 5:30:00
PM.
ANy other suggestion?
Thanks,


--
kdoan
------------------------------------------------------------------------
kdoan's Profile:
http://www.excelforum.com/member.php...o&userid=34230
View this thread:
http://www.excelforum.com/showthread...hreadid=539969