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.
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.