Finding out wrong dates from MS Access table

Asatoma Sadgamaya
Asatoma Sadgamaya used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
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!!).
Juan OcasioApplication Developer

Commented:
IsDate() may help

Author

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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Rob HensonFinance Analyst

Commented:
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.
Hamed NasrRetired IT Professional

Commented:
Upload a sample table with few records.
Show the required output from the sample table.
Most Valuable Expert 2015
Distinguished Expert 2018

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

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

thx

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial