SQL statement failing

My SQL string is failing because of the dates. I believe I need to use parenthesis because it has to be one date or the other. The code is listed below.
            strSQL = "Select * " _
            & "             From qDealLog " _
            & "          Where numDealerID= " & numDealer & "" _
            & "              And (dtDeal between #" & dtBegin & "# and #" & dtEnd & "#" ) " _
            & "                 or (dtDead between #" & dtBegin & "# and #" & dtEnd & "#" )"
            & "     Order By " & strCriteria & ""
smm6809Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You're right. Need parenthesis to enclosure the OR:
            strSQL = "Select * " _
             & "             From qDealLog " _
             & "          Where numDealerID= " & numDealer & "" _
             & "              And ((dtDeal between #" & dtBegin & "# and #" & dtEnd & "#" ) " _
             & "                 or (dtDead between #" & dtBegin & "# and #" & dtEnd & "#" ))" 
             & "     Order By " & strCriteria & "" 

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<Assuming Access VBA>

Just for kicks and giggles, add a -->  Debug.Print strSQL  <--  immediately below the code you posted, and execute.   Then copy what is printed in the Immediate Window (Ctrl-G) into this question.
0
Dale FyeOwner, Developing Solutions LLCCommented:
I agree that Vitor is probably correct, that you need an extra set of ( ) to set the OR conditions off from the NumDealerID condition.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

smm6809Author Commented:
I can't even debug print because the syntax is incorrect (red)
0
ste5anSenior DeveloperCommented:
And you should use an explicit format. Otherwise your date will result in an unrecognized or ambiguous format. E.g.

 
Public Function FormatDate(ADate As Variant) As String

  On Local Error GoTo LocalError
  FormatDate = Format(ADate, "\#m\/d\/yyyy#")
  
  Exit Function
  
LocalError:
  FormatDate = Format(Now, "\#m\/d\/yyyy#")
  
End Function

Public Sub Test()

  Dim dtBegin As Date
  Dim dtEnd As Date
  Dim numDealer As Long
  Dim strCriteria As String
  Dim strSQL As String

  strSQL = _
    "SELECT * " & _
    "FROM qDealLog " & _
    "WHERE numDealerID = " & numDealer & " " & _
    "AND (dtDeal BETWEEN " & FormatDate(dtBegin) & " AND " & FormatDate(dtEnd) & " " & _
    "OR dtDead BETWEEN " & FormatDate(dtBegin) & " AND " & FormatDate(dtEnd) & ") " & _
    "ORDER BY " & strCriteria & ";"
    
  Debug.Print strSQL

End Sub

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For starters, there's two double-quotes at the end on the above line, guessing there should only be one
             & "          Where numDealerID= " & numDealer & "" _ 

Open in new window


The below line is missing an underscore at the end
 
            & "                 or (dtDead between #" & dtBegin & "# and #" & dtEnd & "#" ))"

Open in new window


Below is my style.  A little easier to read.

Dim sSQL as String

sSQL = "Select * " 
sSQL = sSQL & "From qDealLog " 
sSQL = sSQL & "Where numDealerID= " & numDealer & " " 
sSQL = sSQL & "   And ((dtDeal between #" & dtBegin & "# and #" & dtEnd & "#" ) "
sSQL = sSQL & "   or (dtDead between #" & dtBegin & "# and #" & dtEnd & "#" )) " 
sSQL = sSQL & "Order By " & strCriteria 

Debug.Print sSQL 

Open in new window

0
smm6809Author Commented:
Jim, When I pasted your code into my app, these 3 lines are red.

sSQL = sSQL & "Where numDealerID= " & numDealer &
sSQL = sSQL & "   And ((dtDeal between #" & dtBegin & "# and #" & dtEnd & "#" ) "
sSQL = sSQL & "   or (dtDead between #" & dtBegin & "# and #" & dtEnd & "#" ))"
0
ste5anSenior DeveloperCommented:
And here is my style:

Public Function FormatDate(ADate As Variant) As String

  On Local Error GoTo LocalError
  FormatDate = Format(ADate, "\#m\/d\/yyyy#")
  
  Exit Function
  
LocalError:
  FormatDate = Format(Now, "\#m\/d\/yyyy#")
  
End Function

Public Function FormatStr(AString As String, ParamArray AValues() As Variant) As String

  Dim Count As Long
  Dim Result As String
  
  Result = AString
  
  For Count = 0 To UBound(AValues())
    Result = Replace(Result, "{" & Count & "}", Nz(AValues(Count), "NULL"))
  Next Count
  
  Result = Replace(Result, "{CRLF}", vbCrLf)
  FormatStr = Result

End Function

Public Sub Test()

  Const SQL_SELECT As String = _
    "SELECT * FROM qDealLog " & _
    "WHERE numDealerID = {0} " & _
    "AND (dtDeal BETWEEN {1} AND {2} " & _
    "OR dtDead BETWEEN {1} AND {2}) " & _
    "ORDER BY {3};"
  
  Dim dtBegin As Date
  Dim dtEnd As Date
  Dim numDealer As Long
  Dim strCriteria As String
  Dim strSQL As String
  
  strSQL = FormatStr(SQL_SELECT, numDealer, FormatDate(dtBegin), FormatDate(dtEnd), strCriteria)
    
  Debug.Print strSQL

End Sub

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Jim's code it's ok. The line is sSQL = sSQL & "Where numDealerID= " & numDealer & " " and you forgot the last space. Anyway isn't needed and you can delete the last '&': sSQL = sSQL & "Where numDealerID= " & numDealer
0
smm6809Author Commented:
Now I am getting red code on

sSQL = sSQL & "   And ((dtDeal between #" & dtBegin & "# and #" & dtEnd & "#" ) "
sSQL = sSQL & "   or (dtDead between #" & dtBegin & "# and #" & dtEnd & "#" )) "

based on what you added Vitor
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I can see why. Had an extra ". Corrected Jim's code:
sSQL = sSQL & "   And ((dtDeal between #" & dtBegin & "# and #" & dtEnd & "# ) "
 sSQL = sSQL & "   or (dtDead between #" & dtBegin & "# and #" & dtEnd & "# )) "

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.