Access problems with Excel spreadsheet  
Author Message
mrosengarth





PostPosted: Fri Mar 14 16:53:08 CDT 2008 Top

Excel >> Access problems with Excel spreadsheet

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.hide-link.com/

Excel308  
 
 
Mika





PostPosted: Fri Mar 14 16:53:08 CDT 2008 Top

Excel >> Access problems with Excel spreadsheet 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



>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
>


 
 
Shane





PostPosted: Sat Mar 15 10:15:49 CDT 2008 Top

Excel >> Access problems with Excel spreadsheet This is a multi-part message in MIME format.

------=_NextPart_000_0082_01C88674.C7283B10
Content-Type: text/plain;
charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=20

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,
> 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.
>=20
> Any ideas?
>=20
> --=20
> Bill Reed
>=20
> "If you can't laugh at yourself, laugh at somebody else"
>=20
> Message posted via http://www.officekb.com
>
------=_NextPart_000_0082_01C88674.C7283B10
Content-Type: text/html;
charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dunicode">
<META content=3D"MSHTML 6.00.6000.16609" name=3DGENERATOR></HEAD>
<BODY id=3DMailContainerBody=20
style=3D"PADDING-RIGHT: 10px; PADDING-LEFT: 10px; PADDING-TOP: 15px"=20
bgColor=3D#ffffff leftMargin=3D0 topMargin=3D0 CanvasTabStop=3D"true"=20
name=3D"Compose message area">
<DIV><FONT face=3DArial size=3D2>Hi Bill,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>You could treat this in Access&nbsp;by =
defining the=20
Access field as Text and then running an Update query that would remove =
the=20
non-date entries and then convert the data type of the field to =
date.&nbsp; Or=20
in access you could create a query with a calculated field which brings =
all the=20
text dates into it as dates.&nbsp; You could also create another =
calculated=20
column to extract the non dates.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Here is an example of the new field =
which extracts=20
the dates </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Real Date:=20
IIf(IsDate([Date]),[Date],"")</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Where Real Date is a calculated new =
field in the=20
query and [Date] is the text field containing a mix of text dates, text =
and so=20
on.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Cheers,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Shane Devenshire</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Microsoft Excel MVP</FONT></DIV>

message=20

some=20
putz has typed in text, such as<BR>&gt; "Closed", or "OnGoing". Some of =
the=20
records actually have dates in them,<BR>&gt; though. When I link to the =
sheet in=20
Access, it sees that the first several<BR>&gt; records are text and =
shows the=20
field as text in Access, causing headaches.The<BR>&gt; spreadsheet is a =
report=20
based on several sources and I'm pretty much stuck<BR>&gt; with it. =
However, I'm=20
thinking of adding a column to the spreadsheet that<BR>&gt; could put a =
bogus=20
date in when it finds nulls, empty strings, or text, such<BR>&gt; as =
"1/1/2099"=20
or something, and returns the short date when it finds one.<BR>&gt; =
<BR>&gt; Any=20
ideas?<BR>&gt; <BR>&gt; -- <BR>&gt; Bill Reed<BR>&gt; <BR>&gt; "If you =
can't=20
laugh at yourself, laugh at somebody else"<BR>&gt; <BR>&gt; Message =
posted via=20
http://www.officekb.com<BR>&gt;</DIV></BODY></HTML>

------=_NextPart_000_0082_01C88674.C7283B10--

 
 
ragtopcaddy





PostPosted: Mon Mar 17 07:12:00 CDT 2008 Top

Excel >> Access problems with Excel spreadsheet My thanks to you both for your suggestions.


>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.aspx/excel-new/200803/1