VBA SQL Help with Dates - Excel 2007

CarlRostron

New Member
Joined
May 11, 2011
Messages
26
My first post on this forum and I thank all helpers in advance.

I am using Excel 2007. I am running a VBA SQL query on some data in a sheet. Most of my SQL queries work fine apart from the one with dates.

I have tried alsorts to try and debug as to why it isnt working and have resorted to get some help from you guys.

The snippet of code is thus:
........Other code............
Dim aDate As Date
aDate = Format("22/07/2011", "Short Date")

strQuery = "SELECT * FROM [Sheet1$A1:L302] WHERE ([Want Date] > " & aDate & ");"
Sheets("Output").Range("A1").Value = strQuery
If (strQuery = "") Then
Set ResultSet = Query("SELECT * FROM " & MyRange & ";")
Else
'strQuery = "SELECT * FROM " & MyRange & _
' " WHERE " & strQuery
Set ResultSet = Query(strQuery)
End If
........Other code............

Just to confirm the SQL Query going in to the query function I have is:
SELECT * FROM [Sheet1$A1:L302] WHERE ([Want Date] > 22/07/2011);

This as far as I can see is valid. However, the Result Set being returned contains records that are earlier than 22/7/2011.

Please can someone help me to identify as to where this is going wrong? The dates in the sheet are fomatted to 'Short Date'.

Thanks
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Can you post the code for your query function?
 
Upvote 0
Absolutely. Here it is, pretty straight forward. It just takes the Paramater as a string query in my previous post. Just to reiterate, my other SQL queries return a correct Result Set, just the dates causing an issue.

Code:
Function Query(myQry As String) As Object
    Dim strFilePath, strFilename, strQuery As String
    Dim oConn As Object, oFSObj As Object
    Dim f, LastRow As Integer
    Set oRs = CreateObject("ADODB.RECORDSET")
    Set oConn = CreateObject("ADODB.CONNECTION")
    
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";"
    strQuery = myQry
    Sheets("Output").Range("A1").Value = strQuery
oRs.Open strQuery, _
    oConn, adOpenStatic, adLockOptimistic, adCmdText
  'PrintQueryResults
  Set Query = oRs
  
End Function
 
Upvote 0
not sure as I don't use this method, but have you tried
Code:
strQuery = "SELECT * FROM [Sheet1$A1:L302] WHERE ([Want Date] >#" & aDate & "#);"
[code]

ie insert some hashes
 
Upvote 0
Super, that works fine now!!

However, I have one other issue coming through (I have experienced this before when using the Calendar control).

Some of the Date Queries now work fine, particularly the ones where the day of the month is above 12. However when I query the database for a date where the day is less than 12, for some reason the result set comes back wrong. In fact, I have looked into this much deeper and what it is actually doing is swapping around the day and the moth attributes.

So where I try to find all entries where Date>12/4/2011 (ie 12th April 2011) for instance, the only entries returned are those greater than 4th December 2011.

As I said, I have experienced something similar to this before but I couldn't manage to get around it then even after trying to adjust the date format etc.

Maybe you can offer advice?
 
Upvote 0
Sorry, I should have said the way I do it now is that I use the following function to identify the error and reverse the Date accordingly. See attached:

Code:
Function SwapDate(aDate As Date) As Date
  Dim aDay, aMonth, aYear As Integer
  Dim someDate As String
  
  aDay = Day(aDate)
  aMonth = Month(aDate)
  aYear = Year(aDate)
  
  If (aDay <= 12) Then
    someDate = aMonth & "/" & aDay & "/" & aYear
    SwapDate = CDate(someDate)
  Else
    SwapDate = aDate
  End If
End Function

I imagine there to be a much better way of doing this though.
 
Upvote 0
This is probably due to VBA changing the date from US to UK format or vice versa.

You could try wrapping the date in the DateValue function, still with the hashes.
Code:
"#"&DateValue(Range("A1").Value)&"#"

Obviously use the correct range or variable.
 
Upvote 0

Forum statistics

Threads
1,224,603
Messages
6,179,853
Members
452,948
Latest member
UsmanAli786

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