Using Parenthesis in VBA

[jo15765]

I want to run this statement in VBA (which I know is a valid statement)

''''''Begin & Finish are input boxes that are receiving values as dates...
WHERE tblnew.partName = 'Muffler' AND (tblnew.partEnteredDate Between " & Begin & " And " & Finish & ") 
And tblnew.supervisorCheck IS NOT NULL AND tblnew.OrderDate IS NOT NULL AND tblnew.Ordered IS NOT NULL"

The statement runs fine, but it doesn't return any results and if I run the query in SQL Server it returns results. That being said, I am thinking that I am missing some parenthesis around pieces of the Where clause....can someone assist?

 

[Norie]

That looks more like SQL than VBA to me and when I copy it into the VBE it turns that nice red colour, with a touch of green for the comment.

Is this part of other code?

 

[jo15765]

You are correct Norie, it is running SQL in VBA. I know it is a valid SQL statement in VBA I guess should have been a better way to have worded that (I know this becuase I am running the VERY similar code for about 4 different clients, and it works flawlessly on the other sites. Also, the code executes fine before I add in the WHERE clause. And yes it is part of another code, I'll give full code:

Public Sub SQLPull()
    
    Begin = Application.InputBox(Prompt:="Please Enter an Start Date.", Title:="Begin Date")
    Finish = Application.InputBox(Prompt:="Please Enter an End Date.", Title:="End Date")

varConn = "ODBC;DBQ=M:\New\"TestingDatabase.mdb;Driver={Driver do Microsoft Access (*.mdb)}"

    varSQL = "SELECT tblnew.custfirstName, tblnew.custlastName, tblnew.custaddress, tblnew.custphone, tblnew.custemail FROM tblnew WHERE tblnew.partName = 'Muffler' AND (tblnew.partEnteredDate Between " & Begin & " And " & Finish & ") And tblnew.supervisorCheck IS NOT NULL AND tblnew.OrderDate IS NOT NULL AND tblnew.Ordered IS NOT NULL"
        With ActiveSheet.QueryTables.Add(Connection:=varConn, Destination:=Range("A1"))
             .CommandText = varSQL
             .Name = "Query14456"
             .RefreshStyle = xlInsertDeleteCells
             .FieldNames = True
             .PreserveColumnInfo = True
             .PreserveFormatting = True
             .AdjustColumnWidth = True
             .PreserveColumnInfo = True
             .Refresh BackgroundQuery:=True
        End With
End Sub

 

[jo15765]

Bump....Anyone have any ideas or resolution?

 

[jo15765]

I posted this ? here to see if I could get assistance also

Parenthesis Help with SQL in VBA

 

[Norie]

You aren't missing any parentheses but the dates might be in the wrong format.

I'll need to check it out in the morning.

 

[Kyle123]

Hmm, you mention SQL server, but your connection string is for an access database, which are you trying to query? The syntax is different for each, SQL server requires dates passed enclosed in single quotation marks, access enclosed in hash tags. You've included norther of these in your statement.

You should also really be formatting your dates as yyyy-mm-DD since without doing this you can't be sure how the date will be interpreted.

 

[jo15765]

It's an access query. When I input the date for both of the input boxes if I run a message box, the date shows as the proper syntax (example 9/01/2012) which is exactly how I would enter it if, I was running the query in Access itself into the input box (9/01/2012).

 

[Kyle123]

That's because access corrects it for you, it won't when you're doing it like this. You need to pass the dates formatted correctly a above, so in that format and if they're stored as dates wrap them in hashes

 

[jo15765]

Can I format the date in my actual SQL Statement similar to this formatting:

(tblnew.partEnteredDate Between & "#" & Begin & " & # & And & "#" & Finish & "#" & ")

Or do i have to format the date before it is ever passed to the statement in order for it to be recognized?