Using a form to show data when one date is greater than another

[breezett93]

Hello

I have a promise date vs actual date form that I am creating. The user can enter a date range from x to y to see all orders that shipped between those dates. The data that comes up also displays the promise date and actual(ship) date for the orders within the user's range.

I'm trying to implement a "Show Late Orders' button that will display orders where the actual date is greater than the promise date while staying within the date range specified by the user.

I thought that the best option would be to implement the query in the VBA of the form since some of the criteria is dependent on what the user inputs.
Here is what I have so far:

SELECT all the stuff I need
FROM TableA INNER JOIN TableB ON TableA.[OrdId] = TableB.[OrdId]"
WHERE ([Actual Date] is Between [Forms]![frmMyForm]![txtFrom] AND [Forms]![frmMyForm]![txtTo]) And [Actual Date] > [Promised Date]"
ORDER BY TableA.IvcID DESC;"

After that, I'll then need to calculate the percentage of late orders, but that's a separate problem.

Thanks

 

[dhookom]

Is there a question? Did you try what you thought or do you need help?


Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

Yes, the bold is the part I can't get to work properly.

 

[dhookom]

The bold is not VBA. The quotes don't match, there are no variables or mention of record sources or queries.
Between doesn't include "is"

This would be some partial code depending on how you want to use this.

Dim strWhere as String
strWhere = "[Actual Date] Between #" & Me.[txtFrom] & "# AND #" & Me.[txtTo] " & _
      "# And [Actual Date] > [Promised Date] "

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

Sorry for the confusion. I know the bold is not VBA. I was just writing out what I wanted the code to do.

I know there are a couple methods to implement the query in VBA, and I can't use RunSQL because of the SELECT statement. What's the best way to implement the query in VBA?

 

[dhookom]

I would use a checkbox in the form header (with the from and to text boxes) to toggle late or not. Then add all of the following code which will set the form's filter property and make sure it is on.

Option Compare Database
Option Explicit

Private Sub chkLate_AfterUpdate()
    ApplyTheFilter
End Sub

Private Sub txtFrom_AfterUpdate()
    ApplyTheFilter
End Sub
Sub ApplyTheFilter()
    Dim strWhere As String
    strWhere = "1 = 1 "
    
    If IsNull(Me.chkLate) Then  'default late to false
        Me.chkLate = 0
    End If
    
    If Not IsNull(Me.txtFrom) Then
        strWhere = strWhere & " AND [Actual Date] >=#" & Me.txtFrom & "# "
    End If
    
    If Not IsNull(Me.txtTo) Then
        strWhere = strWhere & " AND [Actual Date] <=#" & Me.txtTo & "# "
    End If
    
    If Me.chkLate Then
        strWhere = strWhere & " AND [Actual Date] > [Promised Date] "
    End If
    
    If Len(strWhere) > 6 Then
        Me.Filter = strWhere
        Me.FilterOn = True
     Else
        Me.FilterOn = False
    End If
    
End Sub

Private Sub txtTo_AfterUpdate()
    ApplyTheFilter
End Sub

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[kjv1611]

I know this one could possibly be resolved already, but if you have one of the last few versions of Access, at least 2007 or 2010, I believe, you can use Conditional Formatting similar to Excel. I've used it on fields in Access forms, and it works great. So if you're always comparing to "Today", you could use that in your conditional formatting of your date field. Then you do not need any code.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57