Extracting a date from Short Text (1 Viewer)

alexfwalker81

Member
Local time
Today, 13:02
Joined
Feb 26, 2016
Messages
93
I'm importing data from eBay, Amazon and my own website into individual tables in Access. I'll then simplify each table and create queries which pull out the common data fields (such as transaction date, SKU, quantity etc etc) and append these all into a master table so that I can analyse sales by channel, and overall, easily.

However, each of these sources treat the dates differently and I have had to import the data into Short Text fields.

Amazon, for example, formats their dates like this; 2023-09-03T22:50:08+00:00
eBay formats their dates like this; 13 Sep 2023

I can easily extract something which looks like a date from these data, with something like left([field],10) for Amazon and some weird combination of left, mid and right for eBay. What I don't know to do is to make Access then recognise this as a Date/Time field as I append it into the aforementioned master table.

I can work with VBA to some extent. (In other words, if you give me VBA, I know where to copy and paste it and make basic amendments to make it work!)

Thanks in advance!
 

Minty

AWF VIP
Local time
Today, 21:02
Joined
Jul 26, 2013
Messages
10,378
Use an UPDATE or INSERT query?

SQL:
UPDATE tbPurchases SET MyDateField = DateValue(MyEBayDate)
WHERE RecordNo = UniqueRecID
 

alexfwalker81

Member
Local time
Today, 13:02
Joined
Feb 26, 2016
Messages
93
Ah, ok. I've never worked with UPDATE or INSERT.

So, in simple language, I would do whatever extraction I need to do to create something which looks like a date in my initial query (from the raw imported data) and then use UPDATE or INSERT query to push the data into a new table but with the Date/Time data type set as per your SQL?
 

Minty

AWF VIP
Local time
Today, 21:02
Joined
Jul 26, 2013
Messages
10,378
That's probably the best way to do it, as each data source has a different date format.

You could do it in one go, but the date extraction formula will get messy if you stuff it into one operation.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:02
Joined
Feb 19, 2002
Messages
43,513
Wrap the string conversion in the cdate() function So

Me.MyDate = cdate(Left(AmazonDate, 10))

Access recognises 2023-09-03 as a date

You can also use DateValue() to convert a string to a date
Code:
print cdate("13 Sep 2023")
9/13/2023
print datevalue("13 Sep 2023")
9/13/2023
 

alexfwalker81

Member
Local time
Today, 13:02
Joined
Feb 26, 2016
Messages
93
Thanks for your help with this. I tried both methods, just to see what happened and they both worked well.
 

Users who are viewing this thread

Top Bottom