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
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