Finding out wrong dates from MS Access table

Hi,

Is there any way I can find wrong dates( one eg: 28/02/0217) from a table which contains many date columns which are in character datatype. The table is in MS Access database.

Any help, greatly appreciated.

Thanks
K
Asatoma SadgamayaAnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
You could write a query that looks at the date columns for dates less than a particular date eg 01/01/1900

However, it depends what you mean by wrong. The example is fairly obvious with it being year 217, unless you're logging 3rd century activities like the crisis of the Roman Empire (Googled 3rd century, not a historian at all!!).
0
Juan OcasioApplication DeveloperCommented:
IsDate() may help
0
Asatoma SadgamayaAnalystAuthor Commented:
Hi, i need the list of wrong dates from the table  so that i can send it for correction along with its unique sl_no. By the way my dates belong only last and this centuries.
Thanks
K
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rob HensonFinance AnalystCommented:
I suspect you'll have to run two queries, one to identify items that are not genuine dates and one to identify where a date is genuine but outside of a specific range.

As Juan suggested, you could make use of the IsDate function in a query to identify non-genuine dates but I guess you'd need to combine with NOT to extract items eg NOT(IsDate())

If you can extract the table to an Excel sheet and upload it I can take look; I have only dabbled with Access queries but could happily do it in Excel.
0
hnasrCommented:
Upload a sample table with few records.
Show the required output from the sample table.
0
Gustav BrockCIOCommented:
Use a query like this to list purely invalid entries:

Select *
From YourTable
Where Not IsDate([YourDateField])

Open in new window

And a query like this to list those of too old or future dates:

Select *
From YourTable
Where IsDate([YourDateField]) And (DateValue([YourDateField]) < #1/1/1900 Or DateValue([YourDateField]) > Date())

Open in new window

0
Asatoma SadgamayaAnalystAuthor Commented:
I made all date columns into one, then converted into date format using cdate, then sort by ascending order

thx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Asatoma SadgamayaAnalystAuthor Commented:
I made all date columns into one, then converted into date format using cdate, then sort by ascending order. After that check them manually. wrong dates populates itself either at the top or at the bottom.

thx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.