Solved Little Help with VBA..

[mloucel]

I have the following button that refreshes my form IF the EU changes the dates, so far it was ok, until my boss decided to change a bit:
Now She wants to ADD a field we call [STAT] is a CHECK BOX, I need to add this to the equation:

In my vision when the user use the check mark I want to see the STAT orders first, REGARDLESS of the dates, this is important because it is more important STAT than the dates but since those dates are already there.

This is how it looks [TEST DATA ONLY NOT REAL]

when the EU clicks STAT both RED should be at the top, there are more records not shown here, the whole purpose is to show ALL those STAT records first, if the user UN-CHECK STAT just simply SORT by DATES ignoring [STAT] check mark completely.

CORRECTION: The SORT WILL work ONLY when Checked [TRUE] when is FALSE should be IGNORED completely in the equation.

This is the code I have so far:

Private Sub RefreshButton_Click()
'
'   Code courtesy of @Arnelgp
'
    Dim sql As String
    Dim i As Integer
    Dim Db As DAO.Database
    If IsDate(Me.FromDate) And IsDate(Me.ToDate) Then
        ' change the record source of your form
        Set Db = CurrentDb
        sql = Db.QueryDefs("qryAuthorizationFullView").sql
        sql = Replace$(sql, ";", "")
        i = InStr(1, sql, "WHERE")
        If i <> 0 Then
            sql = Left$(sql, i - 1)
       End If
        ' was "ORDER By ReferDate"
        sql = sql & " WHERE ReferDate Between #" & Format$(Me.FromDate, "mm/dd/yyyy") & _
                    "# AND #" & Format$(Me.ToDate, "mm/dd/yyyy") & "#" & "ORDER By AuthorizationT.AuthorizationID"
        RecordSource = sql
    End If
    Set Db = Nothing
End Sub

 

[theDBguy]

If you have a field to indicate which orders are STAT, then you can use that field's name in the ORDER BY clause to sort on it.

 

[mloucel]

If you have a field to indicate which orders are STAT, then you can use that field's name in the ORDER BY clause to sort on it.

Yes, but how do I ignore the instruction when is OFF?

 

[theDBguy]

Yes, but how do I ignore the instruction when is OFF?

Basically, you check for that info and adjust the ORDER BY clause properly. For instance:

If Me.cboSTAT = True Then
    Me.OrderBy = "[STAT], [OtherSortField]"
Else
    Me.OrderBy = "[OtherSortField]"
End If
Me.OrderByOn = True

Hope that helps...

 

[MajP]

maybe something like

Private Sub RefreshButton_Click()
'
'   Code courtesy of @Arnelgp
'
    Dim sql As String
    Dim i As Integer
    Dim Db As DAO.Database
    If IsDate(Me.FromDate) And IsDate(Me.ToDate) Then
        ' change the record source of your form
        Set Db = CurrentDb
        sql = Db.QueryDefs("qryAuthorizationFullView").sql
        'remove the ; at the end
        sql = Replace$(sql, ";", "")
        'check to see if there is a where clause
        i = InStr(1, sql, "WHERE")
        'Keep everything before the where close
        If i <> 0 Then
            sql = Left$(sql, i - 1)
       End If
        ' was "ORDER By ReferDate"
        
        'I am assuming both date boxes have to be in or it will not filter sometimes peope will do an after or before filter if only one date entered
       'Stat true and both dates fille in
       if Stat = True and isdate(me.fromdate) and isdate(me.todate) then
            sql = sql & " WHERE ReferDate Between #" & Format$(Me.FromDate, "mm/dd/yyyy") & _
                 "# AND #" & Format$(Me.ToDate, "mm/dd/yyyy") & "#" & " OR STAT = TRUE ORDER By STAT, AuthorizationT.AuthorizationID"
        'Stat true but one of the dates not filled in
        elseif Stat = True and not isdate(me.fromdate) or not isdate(me.todate) then
            sql = sql & " WHERE STAT = TRUE Order by AuthorizationT.AuthorizationID"
        'Stat false and both dates filled in
        Elseif Stat = false and Isdate(me.fromDate) and isdate(me.todate) then
          sql = sql & " WHERE ReferDate Between #" & Format$(Me.FromDate, "mm/dd/yyyy") & _
                    "# AND #" & Format$(Me.ToDate, "mm/dd/yyyy") & "#" & "ORDER By AuthorizationT.AuthorizationID"
        end if
        RecordSource = sql
    End If
    Set Db = Nothing
End Sub

 

[mloucel]

maybe something like

Private Sub RefreshButton_Click()
'
'   Code courtesy of @Arnelgp
'
    Dim sql As String
    Dim i As Integer
    Dim Db As DAO.Database
    If IsDate(Me.FromDate) And IsDate(Me.ToDate) Then
        ' change the record source of your form
        Set Db = CurrentDb
        sql = Db.QueryDefs("qryAuthorizationFullView").sql
        'remove the ; at the end
        sql = Replace$(sql, ";", "")
        'check to see if there is a where clause
        i = InStr(1, sql, "WHERE")
        'Keep everything before the where close
        If i <> 0 Then
            sql = Left$(sql, i - 1)
       End If
        ' was "ORDER By ReferDate"
       
        'I am assuming both date boxes have to be in or it will not filter sometimes peope will do an after or before filter if only one date entered
       'Stat true and both dates fille in
       if Stat = True and isdate(me.fromdate) and isdate(me.todate) then
            sql = sql & " WHERE ReferDate Between #" & Format$(Me.FromDate, "mm/dd/yyyy") & _
                 "# AND #" & Format$(Me.ToDate, "mm/dd/yyyy") & "#" & " OR STAT = TRUE ORDER By STAT, AuthorizationT.AuthorizationID"
        'Stat true but one of the dates not filled in
        elseif Stat = True and not isdate(me.fromdate) or not isdate(me.todate) then
            sql = sql & " WHERE STAT = TRUE Order by AuthorizationT.AuthorizationID"
        'Stat false and both dates filled in
        Elseif Stat = false and Isdate(me.fromDate) and isdate(me.todate) then
          sql = sql & " WHERE ReferDate Between #" & Format$(Me.FromDate, "mm/dd/yyyy") & _
                    "# AND #" & Format$(Me.ToDate, "mm/dd/yyyy") & "#" & "ORDER By AuthorizationT.AuthorizationID"
        end if
        RecordSource = sql
    End If
    Set Db = Nothing
End Sub

Yes, you just went full scale above what I imagine, thank you, your code added what I never saw, and made it more clear, hat off to you sir.

 

[mloucel]

Basically, you check for that info and adjust the ORDER BY clause properly. For instance:

If Me.cboSTAT = True Then
    Me.OrderBy = "[STAT], [OtherSortField]"
Else
    Me.OrderBy = "[OtherSortField]"
End If
Me.OrderByOn = True

Hope that helps...

Thank you I was playing around with your Idea, really a great idea, when @MajP sent me his update, I applied it [with my corrections] and is working perfectly, boss is happy, extremely happy now.

This is the final code in case it may be of someone else's use.

Private Sub RefreshButton_Click()
'
'   Code courtesy of @Arnelgp AWF
'   Update courtesy of @MajP AWF
'  Thanks and shout out to TheDBGuy..
    Dim sql As String
    Dim i As Integer
    Dim Db As DAO.Database
    If IsDate(Me.FromDate) And IsDate(Me.ToDate) Then
        ' change the record source of your form
        Set Db = CurrentDb
        sql = Db.QueryDefs("qryAuthorizationFullView").sql
        'remove the ; at the end
        sql = Replace$(sql, ";", "")
        'check to see if there is a where clause
        i = InStr(1, sql, "WHERE")
        'Keep everything before the where close
        If i <> 0 Then
            sql = Left$(sql, i - 1)
       End If
        ' was "ORDER By ReferDate"
       
        'I am assuming both date boxes have to be in or it will not filter sometimes peope will do an after or before filter if only one date entered
       'Stat true and both dates fille in
       If Me.StatCheck = True And IsDate(Me.FromDate) And IsDate(Me.ToDate) Then
            sql = sql & " WHERE ReferDate Between #" & Format$(Me.FromDate, "mm/dd/yyyy") & _
                 "# AND #" & Format$(Me.ToDate, "mm/dd/yyyy") & "#" & " OR AuthorizationT.Urgent = TRUE ORDER By AuthorizationT.Urgent, AuthorizationT.AuthorizationID"
        'Stat true but one of the dates not filled in
        ElseIf Me.StatCheck = True And Not IsDate(Me.FromDate) Or Not IsDate(Me.ToDate) Then
            sql = sql & " WHERE StatCheck = TRUE Order by AuthorizationT.AuthorizationID"
        'Stat false and both dates filled in
        ElseIf Me.StatCheck = False And IsDate(Me.FromDate) And IsDate(Me.ToDate) Then
          sql = sql & " WHERE ReferDate Between #" & Format$(Me.FromDate, "mm/dd/yyyy") & _
                    "# AND #" & Format$(Me.ToDate, "mm/dd/yyyy") & "#" & "ORDER By AuthorizationT.AuthorizationID"
        End If
        RecordSource = sql
    End If
    Set Db = Nothing
End Sub

 

[theDBguy]

Thank you I was playing around with your Idea, really a great idea, when @MajP sent me his update, I applied it [with my corrections] and is working perfectly, boss is happy, extremely happy now.

This is the final code in case it may be of someone else's use.

Private Sub RefreshButton_Click()
'
'   Code courtesy of @Arnelgp AWF
'   Update courtesy of @MajP AWF
'  Thanks and shout out to TheDBGuy..
    Dim sql As String
    Dim i As Integer
    Dim Db As DAO.Database
    If IsDate(Me.FromDate) And IsDate(Me.ToDate) Then
        ' change the record source of your form
        Set Db = CurrentDb
        sql = Db.QueryDefs("qryAuthorizationFullView").sql
        'remove the ; at the end
        sql = Replace$(sql, ";", "")
        'check to see if there is a where clause
        i = InStr(1, sql, "WHERE")
        'Keep everything before the where close
        If i <> 0 Then
            sql = Left$(sql, i - 1)
       End If
        ' was "ORDER By ReferDate"
      
        'I am assuming both date boxes have to be in or it will not filter sometimes peope will do an after or before filter if only one date entered
       'Stat true and both dates fille in
       If Me.StatCheck = True And IsDate(Me.FromDate) And IsDate(Me.ToDate) Then
            sql = sql & " WHERE ReferDate Between #" & Format$(Me.FromDate, "mm/dd/yyyy") & _
                 "# AND #" & Format$(Me.ToDate, "mm/dd/yyyy") & "#" & " OR AuthorizationT.Urgent = TRUE ORDER By AuthorizationT.Urgent, AuthorizationT.AuthorizationID"
        'Stat true but one of the dates not filled in
        ElseIf Me.StatCheck = True And Not IsDate(Me.FromDate) Or Not IsDate(Me.ToDate) Then
            sql = sql & " WHERE StatCheck = TRUE Order by AuthorizationT.AuthorizationID"
        'Stat false and both dates filled in
        ElseIf Me.StatCheck = False And IsDate(Me.FromDate) And IsDate(Me.ToDate) Then
          sql = sql & " WHERE ReferDate Between #" & Format$(Me.FromDate, "mm/dd/yyyy") & _
                    "# AND #" & Format$(Me.ToDate, "mm/dd/yyyy") & "#" & "ORDER By AuthorizationT.AuthorizationID"
        End If
        RecordSource = sql
    End If
    Set Db = Nothing
End Sub

Glad to hear you got it sorted out. Good luck with your project.