Access reading Excel VBA date in US format

Question

I am trying to run a query from within Excel VBA with variables from the Excel, but Access is turning my dates into US format:

Sub upload()
Last = ActiveSheet.UsedRange.Rows.Count
Dim LastOrderDate As Date
Dim LastOrderDateYear As Integer
Dim LastOrderDateMonth As Integer
Dim LastOrderDateDay As Integer

FirstOrderDate = Range("D" & Last)

FirstOrderDDate = DateSerial(Year(FirstOrderDate), Month(FirstOrderDate), Day(FirstOrderDate))

'Import Data to Benji's Ecommerce Database
ssheet = Application.ActiveWorkbook.FullName
Set acApp = CreateObject("Access.Application")
    acApp.OpenCurrentDatabase ("X:\ECommerce Database.accdb")
    acApp.Visible = True
    acApp.UserControl = True
    acApp.DoCmd.RunSQL "DELETE * FROM [OrdersDetailed] WHERE [OrderDate] >= #" & FirstOrderDDate & "#"
    acApp.CloseCurrentDatabase
    acApp.Quit
Set acApp = Nothing

End Sub

So for instance, with the variable 01/03/2017, it uses 03/01/2017 and deletes everything past 3rd Jan 2017, instead of the desired 1st Mar 2017.

I have checked my date display settings on my computer, and as far as I can see my region is UK and date format is UKs, I have never seen US style dates ever on my comp!

What is going on here, and how can I solve it?

Thanks,

Benji

Specifically set the format of the date by FORMAT(FirstOrderDate,"dd/mm/yyyy) — PeterT
@BenjiKnightsJohnson there is nothing wrong with the code, you just need to control the format of your dates. First make sure that on your Excel file, the dates are really dates and not texts. To text, just write a formula in a cell and add 1 to one of the dates and you should get a date as the result, if it was a text you would get an error. — Ibo
Where do I put the FORMAT? I have tried creating a new variable that is defined with that format: FirstOrderDDDate = Format(FirstOrderDDate, "dd/mm/yyyy") And have tried having it inside the SQL code like: acApp.DoCmd.RunSQL "DELETE * FROM [OrdersDetailed] WHERE [OrderDate] >= #" & Format(FirstOrderDDate, "dd/mm/yyyy") & "#" But both of those are still deleting data from 3rd Jan onwards... — Benji Knights Johnson
I have eventually found out how to do this. You were nearly right Peter, not sure if it was a typo, but I needed to format the Excel variable as mm/dd/yyyy (not the mm and dd the other way round to Peter. So specifically what I did was: LastOrderDate = Format(Range("D2"), "mm/dd/yyyy hh:mm:ss") within the Excel VBA. — Benji Knights Johnson

Kiran Kiran · Accepted Answer · 2020-02-16T16:43:04

Use DateSerial() in the query. Always use DateSerial(). It will never let you down. You want the SQL to look like this:

DELETE * FROM [OrdersDetailed] WHERE [OrderDate] >= DateSerial(2020, 01, 02)

So your VBA should be:

"DELETE * FROM [OrdersDetailed] WHERE [OrderDate] >= DateSerial(" & Year(FirstOrderDDate) & ", " & Month(FirstOrderDDate) & ", " & Day(FirstOrderDDate) & ")"

Access reading Excel VBA date in US format

1 Answers