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?

--
Bill Reed

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

Message posted via http://www.officekb.com

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

"ragtopcaddy via OfficeKB.com" <u9289@uwe> wrote in message
news:8128e1ecc80f0@uwe...
>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?
>
> --
> Bill Reed
>
> "If you can't laugh at yourself, laugh at somebody else"
>
> Message posted via http://www.officekb.com
>

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


"ragtopcaddy via OfficeKB.com" <u9289@uwe> wrote in message news:8128e1ecc80f0@uwe...
> 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?
>
> --
> Bill Reed
>
> "If you can't laugh at yourself, laugh at somebody else"
>
> Message posted via http://www.officekb.com
>

My thanks to you both for your suggestions.

Shane Devenshire wrote:
>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,
>[quoted text clipped - 6 lines]
>>
>> Any ideas?

--
Bill Reed

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

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200803/1