2

I am attempting to import a date column from Excel into Access. The problem is that over the years, users of the Excel spreadsheet sporadically inserted text notes into this date column a few thousand times. It would be impossible to delete all of the text manually. I was able to import this date column into Access by converting the format to Text at the time of import. The problem is that I still need to find a way to convert the new Access Text field containing my dates back into a Date field, so that I can perform basic arithmetic functions on it (adding days for various different projected project lead times). Unfortunately, Access won't allow me to convert the entries with text in them back to Dates, any more than it let me import them as Dates.

My first question would be, is there a way to override this? If not, then I was thinking about some kind of conditional UPDATE statement that would let me eliminate only the text in these cells, while leaving the numeric values untouched (i.e.- “IF any text is found, UPDATE this field to delete the text only”). I'm not sure if this would be possible though.

If neither of these are possible, then does anyone have any other ideas? Thanks in advance!

Improve this question
4
  • 2
    Does this have to be done in Access? Seems to me it would be a good idea to condition the data BEFORE it is imported into the database. Have you tried removing text from the column, format as date, then import into Access?
    – CharlieRB
    Commented Jun 13, 2014 at 13:45
  • If the notes are always following or preceding the date you can extract easily the date in excel with left(cell;x) or right(a,x). Where x is the number of characters you need to extract. After that you just have to use the convert command and manually correct he few excpetiosn left. It's a job of twenty minutes. Alternatively you can use ctrl+h and suppress all the characters you don't want going through the alphabet one by one. Or you can write a macro to to that at once .Either case it should take 30mn or os. Provide a sample if you want more precise help.
    – P. O.
    Commented Jun 13, 2014 at 14:00
  • Hi Charlie. While I would have no personal issue with removing the text, it would be near impossible to do manually. Users of this spreadsheet have entered textual notes in thousands of these "Date" cells. Further complicating the matter is the fact that they may actually need to keep these notes, meaning that I'd have to find a way to copy all of the text into the adjacent cells of another blank column before deleting the text in the Date column.
    – user333102
    Commented Jun 13, 2014 at 14:05
  • Make a column adjacent to the one in question and copy a formula like this throughout it: =IF(ISTEXT(A1),"",A1) (this assumes column A is the one you want to import). The new column will contain only the dates and you can import from there without fear of including the text or spending hours removing it.
    – techturtle
    Commented Jun 13, 2014 at 14:14

1 Answer 1

Reset to default
0

This is quite easy. Add a date column to your table in Access. Open up the query design view and write an update query to transfer the good dates to the new field, deleting them from the old field.

UPDATE Table1 SET [Table1].[NewDateField] = [Table1].[OldDateField], [Table1].[OldDateField] = "" WHERE IsDate([Table1].[OldDateField])=True;

This SQL query works in Access.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .