[AccessD] Date Importing from Excel

[John Bartow]

Thanks all,
So here's what I did with the queries data fields, insert built-in functions to convert the crap data, these are two of the fields (of many) in the make table query:
DOB: IIf([DATE OF BIRTH],CDate([DATE OF BIRTH]),Null)
ClientNotes: (IIf(IsDate([DATE OF BIRTH]),"",("DOB Note: " & [DATE OF BIRTH]) & " ?"))

So when I create the Client table I get clean DOB dates and those that don't have clean data in the imported crap get a Client Note that the staff can figure out and update the client info on their time.

John B
PS I once again adequately hate spreadsheets.

-----Original Message-----
From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of John Bartow via AccessD
Sent: Thursday, August 13, 2020 2:06 PM
To: DBA-Access (AccessD at databaseadvisors.com) <AccessD at databaseadvisors.com>
Cc: John Bartow <jbartow at winhaven.net>
Subject: [AccessD] Date Importing from Excel

Hi All,
I'm doing some Access work for the first time in years). It's a pretty simple little db and app. The exception being the importing of the old data from a spreadsheet. The spreadsheet is a mess and that's why they finally asked me for a better solution. This is so basic that I'm just doing an Access app so they can see there is life beyond spreadsheets. 

The biggest issue is the old data - specifically all of the date fields. Each date field has dozens of records where the data won't import unless I set them to short text.

I have used queries in the past to add a date field and have the query convert the short text to dates where possible. And then I would use the new date field (where null) to find the old date text fields wouldn't convert so that I don't lose the badly formatted old date field data and can manually investigate it. Problem is none of my old DBs open in the latest access.

How would you suggest I handle the old data?

TIA,
John B

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com