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

S_W_Langdon

New Member
Joined
Feb 5, 2018
Messages
13
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
can you change your access data to dd mmm yyyy, the month is then not misunderstood
 
Upvote 0
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
 
Upvote 0
Consider using longs, something like


Code:
SELECT *
FROM DataBaseTable
WHERE StatusStartDate BETWEEN CLng(" & var & ") AND CLng(" & var1 & ")"
 
Upvote 0
To avoid ambiguity yyyy-mm-dd for the date format.

Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top