Need assistance writing SQL for form recordset (1 Viewer)

J

jmaty23

Registered User.
Local time
Today, 09:52
Joined
Jul 24, 2012
Messages
53
Can anyone tell me what could be wrong with the SQL string this code generates for strFinal variable???

When it runs, it doesn't take into account the strUser filter when cboStatus case = "ALL"... Cannot figure it out... I included strFinal output below:

Code: 
Private Sub FormDef()
If ErrOn Then On Error GoTo ErrorHandler

If Len(cboStatus & vbNullString) = 0 Then
    MsgBox ("Choose a Status.")
        GoTo ProcedureExit
End If

If chkDateRange Then
    If Len(txtFromDate & vbNullString) = 0 Or Len(txtToDate & vbNullString) = 0 Then
        MsgBox ("Enter a Date Range.")
            GoTo ProcedureExit
    End If
End If

Dim strSQL As String
Dim strUser As String
Dim strStatus As String
Dim strDates As String
Dim strOrderby As String
Dim strFinal As String
    strSQL = "SELECT tblStatusReportUsers.Username, tblStatusReport.ID, tblStatusReport.Creator, tblStatusReport.CreateDate, tblStatusReport.SubjectMatter, tblStatusReport.Status, tblStatusReport.LastUpdated " & _
             "FROM tblStatusReport INNER JOIN tblStatusReportUsers ON tblStatusReport.ID = tblStatusReportUsers.ReportID_PK " & _
             "GROUP BY tblStatusReportUsers.Username, tblStatusReport.ID, tblStatusReport.Creator, tblStatusReport.CreateDate, tblStatusReport.SubjectMatter, tblStatusReport.Status, tblStatusReport.LastUpdated "

    strUser = "HAVING Username = '" & cboUsername & "' "
    
    Select Case cboStatus
        Case "ALL"
            strStatus = "and Status = 'OPEN' or Status = 'CLOSED' "
        
        Case "OPEN"
            strStatus = "and Status = 'OPEN' "
            
        Case "CLOSED"
            strStatus = "and Status = 'CLOSED' "

    End Select
    
    Select Case chkDateRange
        Case True
            strDates = "and LastUpdated Between #" & txtFromDate & "# and #" & DateAdd("d", 1, txtToDate) & "# "
        
        Case False
            strDates = "and LastUpdated Between #1/1/2010# and #" & DateAdd("d", 1, Now) & "# "
    End Select
    
    strOrderby = "ORDER BY tblStatusReport.LastUpdated DESC;"
    
    strFinal = strSQL & strUser & strStatus & strDates & strOrderby
    
    Forms!frmStatusReports!subMain.Form.RecordSource = strFinal
    'Debug.Print strFinal
    
ProcedureExit:
    Exit Sub
    
ErrorHandler:
    If Err.Number = 2455 Then Resume Next
    MsgBox "Error" & ":  " & Err.Number & vbCrLf & "Description: " _
        & Err.Description, vbExclamation, Me.Name & ".FormDef"
    Resume ProcedureExit

End Sub

Code: 
SELECT tblStatusReportUsers.Username, tblStatusReport.ID, tblStatusReport.Creator, tblStatusReport.CreateDate, tblStatusReport.SubjectMatter, tblStatusReport.Status, tblStatusReport.LastUpdated FROM tblStatusReport INNER JOIN tblStatusReportUsers ON tblStatusReport.ID = tblStatusReportUsers.ReportID_PK GROUP BY tblStatusReportUsers.Username, tblStatusReport.ID, tblStatusReport.Creator, tblStatusReport.CreateDate, tblStatusReport.SubjectMatter, tblStatusReport.Status, tblStatusReport.LastUpdated HAVING Username = 'jmatyjr' and Status = 'OPEN' or Status = 'CLOSED' and LastUpdated Between #1/1/2010# and #2/24/2017 2:33:02 PM# ORDER BY tblStatusReport.LastUpdated DESC;
 
P

plog

Banishment Pending
Local time
Today, 08:52
Joined
May 11, 2011
Messages
11,646
First, that criteria belongs in the WHERE clause not the HAVING. WHERE works on a record level, HAVING works on the aggregate (SUM, COUNT, MAX, etc.).

Second, you have to be real careful when you mix AND and OR. When you use them both you need to use parenthesis to section them off properly. This:

Condition1 OR Condition2 AND Condition3

Is logically different than

Condition1 OR (Condition2 AND Condition3)

Most likely you need parenthesis around the 2 criteria seperated by OR.

Lastly, you use no aggregate functions at all (COUNT, MAX, SUM), so I really question why you have a GROUP BY clause.
 
J

jmaty23

Registered User.
Local time
Today, 09:52
Joined
Jul 24, 2012
Messages
53
Parenthesis around the Or criteria worked... Thanks!

Code: 
    Select Case cboStatus
        Case "ALL"
            strStatus = "and (Status = 'OPEN' or Status = 'CLOSED') "
        
        Case "OPEN"
            strStatus = "and Status = 'OPEN' "
            
        Case "CLOSED"
            strStatus = "and Status = 'CLOSED' "

    End Select
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 14:52
Joined
Sep 21, 2011
Messages
14,306
jmaty23 said:
Parenthesis around the Or criteria worked... Thanks!

Code: 
    Select Case cboStatus
        Case "ALL"
            strStatus = "and (Status = 'OPEN' or Status = 'CLOSED') "
        
        Case "OPEN"
            strStatus = "and Status = 'OPEN' "
            
        Case "CLOSED"
            strStatus = "and Status = 'CLOSED' "

    End Select
Click to expand...

Hi, I would ask if you want ALL why are you even querying the status?
 
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:52
Joined
Jan 20, 2009
Messages
12,852
plog said:
This:

Condition1 OR Condition2 AND Condition3

Is logically different than

Condition1 OR (Condition2 AND Condition3)

Most likely you need parenthesis around the 2 criteria seperated by OR.
Click to expand...

Right idea, but those two expressions are actually logically identical because AND takes precedence over OR.

BTW One way to remember this by remembering that AND also precedes OR alphabetically.
 
J

jmaty23

Registered User.
Local time
Today, 09:52
Joined
Jul 24, 2012
Messages
53
Gasman said:
Hi, I would ask if you want ALL why are you even querying the status?
Click to expand...

Your right, there really is no reason to query status at all because all records will either be OPEN or CLOSED. Works good. Thanks!

Code: 
Private Sub FormDef()
If ErrOn Then On Error GoTo ErrorHandler

If Len(cboStatus & vbNullString) = 0 Then
    MsgBox ("Choose a Status.")
        GoTo ProcedureExit
End If

If chkDateRange Then
    If Len(txtFromDate & vbNullString) = 0 Or Len(txtToDate & vbNullString) = 0 Then
        MsgBox ("Enter a Date Range.")
            GoTo ProcedureExit
    End If
End If

Dim strSQL As String
Dim strUser As String
Dim strStatus As String
Dim strDates As String
Dim strOrderby As String
Dim strFinal As String
    strSQL = "SELECT tblStatusReportUsers.Username, tblStatusReport.ID, tblStatusReport.Creator, tblStatusReport.CreateDate, tblStatusReport.SubjectMatter, tblStatusReport.Status, tblStatusReport.LastUpdated " & _
             "FROM tblStatusReport INNER JOIN tblStatusReportUsers ON tblStatusReport.ID = tblStatusReportUsers.ReportID_PK " & _
             "GROUP BY tblStatusReportUsers.Username, tblStatusReport.ID, tblStatusReport.Creator, tblStatusReport.CreateDate, tblStatusReport.SubjectMatter, tblStatusReport.Status, tblStatusReport.LastUpdated "

    strUser = "HAVING Username = '" & cboUsername & "' "
    
    Select Case cboStatus
        Case "OPEN"
            strStatus = "and Status = 'OPEN' "
            
        Case "CLOSED"
            strStatus = "and Status = 'CLOSED' "
            
        Case Else
            strStatus = ""

    End Select
    
    Select Case chkDateRange
        Case True
            strDates = "and LastUpdated Between #" & txtFromDate & "# and #" & DateAdd("d", 1, txtToDate) & "# "
        
        Case False
            strDates = "and LastUpdated Between #1/1/2010# and #" & DateAdd("d", 1, Now) & "# "
    End Select
    
    strOrderby = "ORDER BY tblStatusReport.LastUpdated DESC;"
    
    strFinal = strSQL & strUser & strStatus & strDates & strOrderby
    
    Forms!frmStatusReports!subMain.Form.RecordSource = strFinal
    'Debug.Print strFinal
    
ProcedureExit:
    Exit Sub
    
ErrorHandler:
    If Err.Number = 2455 Then Resume Next
    MsgBox "Error" & ":  " & Err.Number & vbCrLf & "Description: " _
        & Err.Description, vbExclamation, Me.Name & ".FormDef"
    Resume ProcedureExit

End Sub
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom