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

 

[Jonmo1]

39564 is a date...

Format the Destination Cells as Date instead of General or Number...

Hope this helps.

 

[stapuff]

How?


The data is getting dumped into Excel via CopyRecordSet from Access. Do you have an example?

 

[Jonmo1]

Click Format - Cells - Number
Chose Date
Choose a Date Style
Click OK.

 

[stapuff]

Yer kill'in me.

I have about a thousand different ranges to go through. I need this via VBA since the columns do not line up to make this simple.

 

[Jonmo1]

Well, basic code would be something like this...

Range("A1:A100").NumberFormat = "mm/dd/yyyy"

I'm sure something could be written up, but need to know HOW the could would know which columns to format as a date?
Do you have Header Names or something ?

 

[stapuff]

The yer kill'in me was me laughing BTW.

I was thinking about a Find Loop looking for word "DATE", selecting offset(1,0), Range(Selection, Selection.End(xlDown)).Select, Selection.NumberFormat = "m/d/yyyy"

Your thoughts

 

[NateO]

In your SQL, try:

CDate([Field])

*untested*

 

[Jonmo1]

The yer kill'in me was me laughing BTW.

I was thinking about a Find Loop looking for word "DATE", selecting offset(1,0), Range(Selection, Selection.End(xlDown)).Select, Selection.NumberFormat = "m/d/yyyy"

Your thoughts

I know...lol..
Yes that would work, but you dont' have to loop...

Assuming headers in row 1

x = Application.Match("DATE",Rows(1),0)
Range(Cells(2,x),Cells(Rows.Count,x).End(xlup)).NumberFormat = "m/d/yyyy"

Also, Nate's Idea is probably better. Should get it right at the source...

 

[stapuff]

Nate:

Back to the old pic?

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


Expr1006 this was ENTER_DATE
39429
39429
39429
39429
39429
39429
39500
39519
39519