If Statement with Dates

[Carly]

I have a form which has 2 dates fields ([txt1stDate] & [txt2ndDate] which are populated by me picking a date out of a calendar pop up. What I would like to do is have a message box pop up if the [txt2ndDate] is before [txt1stDate].

I have tried

me.txt2ndDate.value < me.txt1stDate.value

but this doesn't seem to work.

Please help

Regards
Carly

 

[Mile-O]

Where are you trying this?

 

[Mile-O]

In fact, this thread might help you.

 

[Carly]

This is the actual code I am using:

Private Sub txt2ndDate_AfterUpdate()

If Me.txt2ndDate < Me.txt1stDate Then
    MsgBox ("Must Enter Date which is after 1st Date")
    End If

 

[Carly]

I have got this to work by using the following code:

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)

If Me.txt2ndDate < Me.txt1stDate Then
        MsgBox ("You have entered an invalid 2nd date")
        Me.txt2ndDate.SetFocus
    End If

End Sub

The problem with this is when I cliok OK on the Msgbox nothing happens, it just stays there and I cannot do anything else with the database. What I really want it to do is set focus on [txt2ndDate]

Is there anyway I get automatically make the focus go to the field once OK is pressed?

Regards
Carly

 

[Mile-O]

What about this instead?

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)

    Dim dte As Date

    If Me.txt2ndDate < Me.txt1stDate Then
        dte = Me.txt2ndDate
        Me.txt2ndDate = Me.txt1stDate
        Me.txt1stDate = dte
    End If

End Sub

 

[Carly]

But I want it to show a message box and then set the focus on the incorrect date, like the example:

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)

If Me.txt2ndDate <= Me.txt1stDate Then
        MsgBox ("You have entered an invalid 2nd date")
        Me.txt2ndDate.SetFocus
End If

End Sub

but the message box just stays there when you press OK.

 

[Mile-O]

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)

    Static booFlag As Boolean

    If Not booFlag Then
        If Me.txt2ndDate <= Me.txt1stDate Then
            MsgBox "You have entered an invalid 2nd date", vbExclamation
            booFlag = True
            Me.txt2ndDate.SetFocus
        End If
    End If

End Sub

 

[Carly]

This works fine now, but as i'm a novice I don't understand why.

what is booFlag?

 

[Carly]

Sorry I lied, the message only seems to happen the once, if you change the dates whilst still in the form and they are still wrong the message doesn't appear again

 

[Mile-O]

OK, at the top of your form's module where these lines are:

Option Explicit
Option Compare Database

add this line:

Dim booFlag As Integer

So it should now look like this:

Option Explicit
Option Compare Database

Dim booFlag As Integer

==================================


I'm guessing you have the code like in the example I pointed you towards sometime last week.

So, on your StartDate button (I'll call it cmdStart):

Private Sub cmdStart_Click()
    booFlag = True
    Me.txtStart = Nz(Me.txtStart, Date)
    Call ShowCalendar(Me.txtStart)
End Sub

Likewise, with the end date command button.

Private Sub cmdEnd_Click()
    booFlag = True
    Me.txtEnd = Nz(Me.txtEnd, Date)
    Call ShowCalendar(Me.txtEnd)
End Sub

===================================

Finally, your Detail_MouseMove event:

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)

    If booFlag Then
        If Me.txt2ndDate <= Me.txt1stDate Then
            MsgBox "You have entered an invalid 2nd date", vbExclamation
            booFlag = False
            Me.txt2ndDate.SetFocus
        End If
    End If

End Sub

 

[Mile-O]

booFlag is a Boolean variable (a value that can be one of two values, most notable referred to as TRUE or FALSE).

It's used to determine whether the dates have been reset and if so, retest the dates, otherwise display the message box once.

 

[Carly]

That works a treat - Thanks