VBA/ SQL to pull data from MS Access depending on Date Range

[S_W_Langdon]

Hello Everyone,

I am having an issue with a code that I am using to pull data from an MS Access Database into Excel based on a date range.

The issue is that it is pulling the data through, but it seems to be using the MM-DD-YYYY format to pull information when it within the low date range, but DD-MM-YYYY when it is in the later month.

Now I want it to pull DD-MM-YYYY as this is the format that we work within, below is the code that I am using, so any help would be great as I am currently at a loss. (am quite new to VBA/ SQL after not looking at it since college over 10 years ago so probably missing something basic)

Also the Database is setup to United Kingdom as well as the Excel Spreadsheet.
I have only added in the code for the date range, if you need the whole thing please let me know and I will add in the full code from start to finish.

---------------------------------------------------------------------------------------------------------------
'Variables
dbPath = Sheet2.Range("AK4").Value
var = DateValue(Sheet2.Range("AK2"))
var1 = DateValue(Sheet2.Range("AK3"))




'Create the SQL statement to retrieve the data from table.
If Sheet2.Range("AL2").Value = "Yes" Then
SQL = "SELECT * FROM DataBaseTable WHERE StatusStartDate BETWEEN #" & var & " # AND # " & var1 & "#"
Else
SQL = "SELECT * FROM DataBaseTable WHERE StatusStartDate LIKE '" & var & "%" & "'"
End If
---------------------------------------------------------------------------------------------------------------


Thanks
Stephen

 

[mole999]

can you change your access data to dd mmm yyyy, the month is then not misunderstood

 

[Kyle123]

You really need to be using yyyy-mm-dd this only needs to be in the sql though so just format it like that when sending to the database

 

[Fazza]

Consider using longs, something like

SELECT *
FROM DataBaseTable
WHERE StatusStartDate BETWEEN CLng(" & var & ") AND CLng(" & var1 & ")"

 

[Norie]

To avoid ambiguity yyyy-mm-dd for the date format.

If Sheet2.Range("AL2").Value = "Yes" Then
SQL = "SELECT * FROM DataBaseTable WHERE StatusStartDate BETWEEN #" & Format(var. "yyyy-mm-dd") & " # AND # " & Format(var1, "yyyy-mm-dd") & "#"
Else
SQL = "SELECT * FROM DataBaseTable WHERE StatusStartDate LIKE '" & var & "%" & "'"
End If

 

[S_W_Langdon]

Thank you everyone, adding in the Format to yyyy-mm-dd worked and it is now pulling through the data just how I need it to.