Shane
Sat Mar 15 10:15:49 CDT 2008
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
"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.
>=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> </DIV>
<DIV><FONT face=3DArial size=3D2>You could treat this in Access 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. Or=20
in access you could create a query with a calculated field which brings =
all the=20
text dates into it as dates. You could also create another =
calculated=20
column to extract the non dates.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </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> </DIV>
<DIV><FONT face=3DArial size=3D2>Real Date:=20
IIf(IsDate([Date]),[Date],"")</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </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> </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>
<DIV><BR><BR>"ragtopcaddy via OfficeKB.com" <u9289@uwe> wrote in =
message=20
news:8128e1ecc80f0@uwe...<BR>> I have a column labeled date, in which =
some=20
putz has typed in text, such as<BR>> "Closed", or "OnGoing". Some of =
the=20
records actually have dates in them,<BR>> though. When I link to the =
sheet in=20
Access, it sees that the first several<BR>> records are text and =
shows the=20
field as text in Access, causing headaches.The<BR>> spreadsheet is a =
report=20
based on several sources and I'm pretty much stuck<BR>> with it. =
However, I'm=20
thinking of adding a column to the spreadsheet that<BR>> could put a =
bogus=20
date in when it finds nulls, empty strings, or text, such<BR>> as =
"1/1/2099"=20
or something, and returns the short date when it finds one.<BR>> =
<BR>> Any=20
ideas?<BR>> <BR>> -- <BR>> Bill Reed<BR>> <BR>> "If you =
can't=20
laugh at yourself, laugh at somebody else"<BR>> <BR>> Message =
posted via=20
http://www.officekb.com<BR>></DIV></BODY></HTML>
------=_NextPart_000_0082_01C88674.C7283B10--