Access to Excel Date issue

[stapuff]

When I am bring data into Excel from Access - the dates are getting converted from mm/dd/yyyy to the general format (39564). How can I change this back to a date? Can it be done in the SQL statement? The selecting of a column and formatting it will not work for my app. Columns do not line up.


Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=N:\opr\database.mdb"
MySQLcheck = "SELECT [WAREHOUSE],[PARTNUMBER],[ORDER_NUM],[TRANS_DESC],[QUANTITY],[AVAIL_QTY],[ENTER_DATE],[TRANS_DATE] from CUS_REQ"
Set rst = cnn.Execute(MySQLcheck)
FieldCount = (rst.Fields.Count)
rst.MoveFirst

 

[stapuff]

jonmo -

"The selecting of a column and formatting it will not work for my app. Columns do not line up." Not everything in Column D is dates.

The word "Date" is in multiple column's & multiple rows. That's why I was thinking about a Find loop.

 

[Norie]

stapuff

Could you actually post the code that does the import/export?

The only code you've posted so far only seems to open (execute?) a recordset and move to the first record in it.

Don't see anything being transferred to Excel.

 

[Jonmo1]

Not everything in Column D is dates.

Ahh, that could also have something to do with why Nate's suggestion didn't work...

Are there any other Number Type of Data or is the rest just text?

If there's no other numbers, you could just format ALL Cells as the date...

 

[stapuff]

Norie - nice hearing from you.

A piece of the code

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim MySQLcheck As String
Dim i, j As Long
Dim rng As Range
Dim FieldCount, RowCount, tbcount, cbc As Integer
Dim rcArray As Variant


Sub Button1_Click()
Sheets("sheet1").Range("A3:Z10000").ClearContents
Sheets("sheet1").Range("A3:Z10000").Font.Bold = False
Range("A3").Select
Range("A3").Value = "Customer Requirements"
Selection.Font.Bold = True
Set rst = New ADODB.Recordset
Set cnn = New ADODB.Connection
MySQLcheck = "Select * from CUS_REQ"
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=N:\opr\database.mdb;"
.Open
End With
rst.Open MySQLcheck, cnn, adOpenStatic
RowCount = (rst.RecordCount)
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
If RowCount = 0 Then GoTo error1
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=N:\opr\database.mdb"
MySQLcheck = "SELECT [WAREHOUSE],[PARTNUMBER],[ORDER_NUM],[TRANS_DESC],[QUANTITY],[AVAIL_QTY],CDate([ENTER_DATE]),CDate([TRANS_DATE]) from CUS_REQ"
Set rst = cnn.Execute(MySQLcheck)
FieldCount = (rst.Fields.Count)
rst.MoveFirst
For i = 0 To FieldCount - 1
With Sheets("Sheet1").Range("A3").Offset(0, i)
.Value = rst.Fields(i).Name
End With
Next i
ActiveSheet.Range("A4").CopyFromRecordset rst
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Selection.End(xlDown).Select
error1:
ActiveCell.Offset(2, 0).Select

'/////////////////////////////////////////////////////////////////////////////////////////////

ActiveCell.Value = "On Hand"
Selection.Font.Bold = True
Set rst = New ADODB.Recordset
Set cnn = New ADODB.Connection
MySQLcheck = "Select * from ON_HAND"
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=N:\opr\database.mdb;"
.Open
End With
rst.Open MySQLcheck, cnn, adOpenStatic
RowCount = (rst.RecordCount)
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
If RowCount = 0 Then GoTo error2
ActiveCell.Offset(1, 0).Select
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=N:\opr\database.mdb"
MySQLcheck = "SELECT [WAREHOUSE],[PARTNUMBER],[QUANTITY] from ON_HAND"
Set rst = cnn.Execute(MySQLcheck)
FieldCount = (rst.Fields.Count)
rst.MoveFirst
For i = 0 To FieldCount - 1
With ActiveCell.Offset(0, i)
.Value = rst.Fields(i).Name
End With
Next i
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, 0).CopyFromRecordset rst
cnn.Close
Set rst = Nothing
Set cnn = Nothing
If RowCount = 1 Then GoTo error2
Selection.End(xlDown).Select
error2:
ActiveCell.Offset(2, 0).Select

 

[NateO]

I tried CDate([ENTER_DATE]) - the only thing I see that changed was the heading.
Went from ENTER_DATE to Expr1006?

If you want to mess with the Field Name in the Recordset, you have to use 'As X', e.g.,

SELECT CDate([test date]) AS myDate, CDate([test date]) AS foo FROM DateTest;

But if it's not working then forget that mess. Just format the column.

Yes, back to the old pic!

 

[stapuff]

I created a little procedure I call to do what I needed:

Public Sub date_conv()
With Sheets("Sheet1").Range("a3:Z10000")
Set c = .Find("_DATE", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "m/d/yyyy"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

 

[Norie]

stapuff

I'm glad you've found a solution.

But I'm a little confused as to what you mean here.

Columns do not line up.

 

[stapuff]

Norie -

What I met was everything in the column will not be a date.


D3 to D57 is a date

D59 to D101 is a number.

so highlighting the column and doing a format will not work...... in this case.

Thanks,

Kurt