Access problems with Excel spreadsheet

  • Thread starter Thread starter ragtopcaddy via OfficeKB.com
  • Start date Start date
R

ragtopcaddy via OfficeKB.com

I have a column labeled date, in which some putz has typed in text, such as
"Closed", or "OnGoing". Some of the records actually have dates in them,
though. When I link to the sheet in Access, it sees that the first several
records are text and shows the field as text in Access, causing headaches.The
spreadsheet is a report based on several sources and I'm pretty much stuck
with it. However, I'm thinking of adding a column to the spreadsheet that
could put a bogus date in when it finds nulls, empty strings, or text, such
as "1/1/2099" or something, and returns the short date when it finds one.

Any ideas?
 
You have a problem in your design. As importing data to a database, each
column (field) should have a proper datatype. Meaning, if you are planning
to actually use the date in a field, all data in this field (vertical data)
needs to be Date formatted (blanks are allowed). The "closed" or some other
informative (vertical) data should have it's own descriptive column deifined
as Text.

So, it seems that you'll need to do some data rearranging in Excel before
the infomation is in database format. You could use formulas and extra
columns to arrange data to correct format, but Access needs a solid range or
a solid sheet (and good to have with proper fieldnames too) as a source to
be properly imported to Access.

Hopefully this helps

Mika Oukka
IT-Consultant
 
Hi Bill,

You could treat this in Access by defining the Access field as Text and then running an Update query that would remove the non-date entries and then convert the data type of the field to date. Or in access you could create a query with a calculated field which brings all the text dates into it as dates. You could also create another calculated column to extract the non dates.

Here is an example of the new field which extracts the dates

Real Date: IIf(IsDate([Date]),[Date],"")

Where Real Date is a calculated new field in the query and [Date] is the text field containing a mix of text dates, text and so on.

Cheers,
Shane Devenshire
Microsoft Excel MVP
 
My thanks to you both for your suggestions.

Shane said:
Hi Bill,

You could treat this in Access by defining the Access field as Text and then running an Update query that would remove the non-date entries and then convert the data type of the field to date. Or in access you could create a query with a calculated field which brings all the text dates into it as dates. You could also create another calculated column to extract the non dates.

Here is an example of the new field which extracts the dates

Real Date: IIf(IsDate([Date]),[Date],"")

Where Real Date is a calculated new field in the query and [Date] is the text field containing a mix of text dates, text and so on.

Cheers,
Shane Devenshire
Microsoft Excel MVP
I have a column labeled date, in which some putz has typed in text, such as
"Closed", or "OnGoing". Some of the records actually have dates in them,
Click to expand...
[quoted text clipped - 6 lines]
Any ideas?
Click to expand...
Click to expand...

--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-new/200803/1
 
Back
Top