[AccessD] Date Importing from Excel

Stuart McLachlan stuart at lexacorp.com.pg
Fri Aug 14 02:20:48 CDT 2020


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
> 




More information about the AccessD mailing list