• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

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 & ""
0
smm6809
Asked:
smm6809
  • 3
  • 3
  • 2
  • +2
2 Solutions
 
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 FyeCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now