[AccessD] Date Importing from Excel

[John Bartow]

Yes, I agree. Funny thing is that with the last version of Excel it kept prompting me to set it as default for .xlsx files every time I opened one, even though it was set as default. I set notepad as the default for .csv, .txt and other files that I don't want corrupted. My conspiracy theory is that Excel checks those as it opens and prompts people to do this just so they give up and make it default for everything to wants to be default for.

I reviewed some of the records that I imported into my Client table and most of those were just poorly entered dates and bad pastings. Some were actually 2 people listed with their DOBs. But in any case, not my problem now!

John B

-----Original Message-----
From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of Stuart McLachlan
Sent: Friday, August 14, 2020 2:21 AM
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Subject: Re: [AccessD] Date Importing from Excel

Talking of Excel and dates, my other pet hate is  Excel wanting to be the default opener for text filesand conveniently altering text strings to dates in comma and tab delimited data files..  
I ran into this years ago when it kept changing analysis results which has values like "Sep2" 
to "2 September XXXX"

I see that Geneticists have decided to change their gene naming rules because of this.

https://www.theregister.com/2020/08/06/excel_gene_names/


On 14 Aug 2020 at 6:43, John Bartow via AccessD wrote:

> 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
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 


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