Date format - ADO query Access to Excel

[Guest]

I'm querying an Access db with ADO. One db field -DOB- has data of Date/Time
datatype, but when I import it, it displays as General in xl. I know I can
manually change the column format to a Date format, but I want to do it with
code.

The recordset may or may not have this field: users select the fields as
well as the order to query for by entering fields in an xl range.

Part of the code is here:
strStartDate = Range("A1")
strEndDate = Range("A2")

rs1.Open "Select " & sFields & _
" From " & sTable & _
" WHERE (((DOB) Between #" & StartDate & "# And #" & EndDate &
"#))", cn

The recordset may only have 4-5 fields out of 40 in the Access Table. So I
have used the following code after the rs is dumped to xl, but this seems too
clumbsy:


'''FORMAT THE COLUMNS
On Error Resume Next
Set rDOB = ExtractFields.Find(What:=UCase("*DOB*"))
rDOB.Select
If Err <> 0 Then
Err = 0
Set rDOB = ExtractFields.Find(What:=UCase("*Birth*"))
rDOB.Select
If Err <> 0 Then Exit Sub
End If
Selection.EntireColumn.NumberFormat = "mm/dd/yy"

ExtractFields in the range of Headers

Isn't there a way, say in my WHERE clause to make this part of the query?

Also, the format would need to clear when a new query is run which may put
the DOB in a different column.

 

[Jamie Collins]

I'm querying an Access db with ADO. One db field -DOB- has data of Date/Time
datatype, but when I import it, it displays as General in xl.

How are you writing the recordset data to the worksheet? When I use the
CopyFromRecordset method it seems to pick up the correct format,
including my local (UK) date format.

Jamie.

--