Extracting a date from Short Text (1 Viewer)

[alexfwalker81]

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]

Use an UPDATE or INSERT query?

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

 

[alexfwalker81]

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]

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]

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

print cdate("13 Sep 2023")
9/13/2023
print datevalue("13 Sep 2023")
9/13/2023

 

[alexfwalker81]

Thanks for your help with this. I tried both methods, just to see what happened and they both worked well.