INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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

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

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

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

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

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

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.

CODE --> vba

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

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

(OP)
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?

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

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.

CODE --> vba

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

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close