Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
get rid of single quote in field from Access export
I am exporting from Access to Excel and ending up with a single quote in
front of the dates--which makes them a text field. This matters because I am using the dates as a row source for a chart. If Excel knows they are Friday dates, it fills in the missing Friday dates for me (which is what I want). I have tried selecting the cells and formatting them as date fields and that doesn't work. Manually removing the single quote in front of the date fields has been the only thing that has worked, but I need to be able to do this in code. Does anyone have a solution for me? Thank you, Judy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
get rid of single quote in field from Access export
hi
in a blank cell off to the side, enter the number one(1). copy it. high light the column of text dates. then paste special muliply. you may loose your formating as it changes to number but can easily reformat to date. regards FSt1 "Judy Ward" wrote: I am exporting from Access to Excel and ending up with a single quote in front of the dates--which makes them a text field. This matters because I am using the dates as a row source for a chart. If Excel knows they are Friday dates, it fills in the missing Friday dates for me (which is what I want). I have tried selecting the cells and formatting them as date fields and that doesn't work. Manually removing the single quote in front of the date fields has been the only thing that has worked, but I need to be able to do this in code. Does anyone have a solution for me? Thank you, Judy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
get rid of single quote in field from Access export
Believe it or not, it is as simple as just assigning the Value back to
itself. For example... Single Cell: Range("A2").Value = Range("A2").Value Rangel Of Cells: Range("A2:F9").Value = Range("A2:F9").Value Entire Column: Columns("A").Value = Columns("A").Value -- Rick (MVP - Excel) "Judy Ward" wrote in message ... I am exporting from Access to Excel and ending up with a single quote in front of the dates--which makes them a text field. This matters because I am using the dates as a row source for a chart. If Excel knows they are Friday dates, it fills in the missing Friday dates for me (which is what I want). I have tried selecting the cells and formatting them as date fields and that doesn't work. Manually removing the single quote in front of the date fields has been the only thing that has worked, but I need to be able to do this in code. Does anyone have a solution for me? Thank you, Judy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
get rid of single quote in field from Access export
Hi Judy
Enter 1 in an unused cell, and copy the cell. Select "Date cells" and goto Edit Paste Special Operation: Multiply OK. Format the "Date" cells as date. Hopes this helps --- Per "Judy Ward" skrev i meddelelsen ... I am exporting from Access to Excel and ending up with a single quote in front of the dates--which makes them a text field. This matters because I am using the dates as a row source for a chart. If Excel knows they are Friday dates, it fills in the missing Friday dates for me (which is what I want). I have tried selecting the cells and formatting them as date fields and that doesn't work. Manually removing the single quote in front of the date fields has been the only thing that has worked, but I need to be able to do this in code. Does anyone have a solution for me? Thank you, Judy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
get rid of single quote in field from Access export
Usually running the data though DataText to ColumnsNextNextColumn Data
FormatDate(choose format from dropdown)Finish will do the job. Gord Dibben MS Excel MVP On Tue, 7 Apr 2009 09:32:01 -0700, Judy Ward wrote: I am exporting from Access to Excel and ending up with a single quote in front of the dates--which makes them a text field. This matters because I am using the dates as a row source for a chart. If Excel knows they are Friday dates, it fills in the missing Friday dates for me (which is what I want). I have tried selecting the cells and formatting them as date fields and that doesn't work. Manually removing the single quote in front of the date fields has been the only thing that has worked, but I need to be able to do this in code. Does anyone have a solution for me? Thank you, Judy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export Single Record to Append to Access Table | Excel Discussion (Misc queries) | |||
Export Access to Excel text field with numbers | Excel Discussion (Misc queries) | |||
Remove single quote | Excel Worksheet Functions | |||
Export memo field in Access to Excel - data gets cut off | Excel Discussion (Misc queries) | |||
Quote qualified data export | Excel Programming |