Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format - ADO query Access to Excel
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 he 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format - ADO query Access to Excel
gocush wrote: 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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Format Issues Access to Excel | Excel Discussion (Misc queries) | |||
excel - access query - date range | Excel Discussion (Misc queries) | |||
Excel Query Wizard Date Format | Excel Discussion (Misc queries) | |||
Using InputBox to get desired date for a Query pulling data from Access into Excel | Excel Programming | |||
Using InputBox to get desired date for a Query pulling data from Access into Excel | Excel Programming |