Date format - ADO query Access to Excel

G

gocush

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top