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

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

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

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

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