One Textbox Value needs to be Greater than another (1 Viewer)

[mousemat]

Hi all, hope all is well during this Pandemic!

I'm having an issue with an Unbound Form whereby there are two text fields. StartDate and EndDate which are manually filled in by the user before pressing a command button which runs a query, both fields set to short date,

all standard stuff....

However, I want to have some kind of validation when pressing the Command button that the End Date is not earlier than the Start Date. Ive tried

Private Sub cmdRequery_Click()
If EndDate > StartDate Then
MsgBox "You must enter an End Date later than the Start Date!", vbOKOnly, "Incorrect End Date"
        EndDate.SetFocus

Else

Me.Form.[qry_TransactionBreakdown Subform].Form.Requery
Me.txt_TotalExpenses.Requery
Me.txt_TotalIncome.Requery

End If


End Sub

What am I missing?

 

[theDBguy]

Hi. Can you tell us what is happening now when you click the button with that code in it? Also, it seems backwards. Shouldn't it be If StartDate>EndDate?

 

[mousemat]

Hmmm, thats odd.

I also have a combo box on the form which also populates both txt boxes with the financial year dates, if I populate via the combo box then I get the error message about the date being incorrect, whereas if ienter the dates manually i dont get the error

 

[theDBguy]

Hmmm, thats odd.

I also have a combo box on the form which also populates both txt boxes with the financial year dates, if I populate via the combo box then I get the error message about the date being incorrect, whereas if ienter the dates manually i dont get the error

Still, if you use the combos and purposely select the wrong dates, do you get the expected message?

 

[mousemat]

If I use the combos to populate the fields, I get the error message.

If I manually enter the wrong dates, I get the error message.

I I manually enter the correct dates, I don;t get the error message and the form works

 

[HalloweenWeed]

Personally, I don't like working with date strings, there is just too many possible ways to screw that up.
Here's what I do.
Format the textboxes as some sort of date (short date?), unbound.

• Set the start date validation rule:

  ([Start_Date] Is Null) Or ([End_Date] Is Null) Or ([Start_Date]<=[End_Date])

• Set the end date validation rule:

  ([Start_Date] Is Null) Or ([End_Date] Is Null) Or ([Start_Date]<=[End_Date])

• Set the validation text:

  Start date must be on or before "end date"!

• And work directly with the date&time variable, it's just easier.

And this way the user can use the date picker if they so desire (if you allow it).

 

[bob fitz]

The following appears to work:

Private Sub cmdRequery_Click()
    If Not IsDate(Me.StartDate) Then
        MsgBox "Enter a Start Date"
        Me.StartDate.SetFocus
        Exit Sub
    End If
    If Not IsDate(Me.EndDate) Then
        MsgBox "Enter a End Date"
        Me.EndDate.SetFocus
        Exit Sub
    End If
    
    If DateValue(StartDate) > DateValue(EndDate) Then
'       if Not IsNull(Me.StartDate) And Not IsNull(Me.StartDate)
        MsgBox "You must enter an End Date later than the Start Date!", vbOKOnly, "Incorrect End Date"
        EndDate.SetFocus
    
    Else
    
        Me.Form.[qry_TransactionBreakdown Subform].Form.Requery
        Me.txt_TotalExpenses.Requery
        Me.txt_TotalIncome.Requery
        
    End If

End Sub

 

[Isaac]

If I use the combos to populate the fields, I get the error message.

If I manually enter the wrong dates, I get the error message.

I I manually enter the correct dates, I don;t get the error message and the form work

This sounds like exactly like you want. Right?

1) It might not be a bad idea to use cdate(controlname) just to be sure
2) You may want to consider using Exit Sub after the SetFocus command...if your sub is going to have more code that saves the record or proceeds with the operation in some way..

 

[mousemat]

Hi Bob.

That does indeed seem to work exactly as required.

I guess it has something to do with the DateValue string.

Thanks

 

[Isaac]

I guess it has something to do with the DateValue string.

See my earlier post about explicitly converting to Date.

 

[bob fitz]

Hi Bob.

That does indeed seem to work exactly as required.

I guess it has something to do with the DateValue string.

Thanks

Indeed. You may find the following link of interest: http://allenbrowne.com/ser-36.html

 

[Minty]

In addition to Bob's answer, I suspect that this might originally actually be to do with the lack of a Me. in the original code, and the fact it was around the wrong way?

If Me.StartDate > Me.EndDate Then

As it's an unbound form ?