Question regarding compare fields

[Shohoku79]

Hello:

I have a created a form with two textboxes on there with the intention of
letting users fill in dates on there for further input. In order to prevent
entry errors, I have created the Calendar object that will be activated when
the user double clicks in a textbox and will fill in the date when the user
select the date.

Now having two of these textboxes taking in dates means that I would like to
create a range for this. But I would like to program a check for this, where
textbox 1 (From) should not have a later date than textbox 2 (To). If that
error occurs, there should be an error message generated stating "Invalid
Date Range entered" and ask the user to select the range in the correct
format and will not let the user continue to pass the data until a valid
range is entered.

How would I go about accomplishing this?

Thank you,

 

[mscertified]

Assuming this is a bound form, in your BEFORE UPDATE event, check that date1
is earlier than or equal to date 2. If not issue an error message and set
CANCEL = TRUE. You could also set the focus back to the first date control.

-Dorian

 

[Shohoku79]

Thanks for your response.

Well, the intention was to give the user a bit of freedom in the date
inclusion so those date fields were left unbound. But that means I could
still use a check after Afterupdate() event right?

Is the logical comparison for dates also < > = like numbers?

Thanks again.

 

[Douglas J. Steele]

Yes. Dates actually are numbers in Access: they're 8 byte floating point
numbers where the integer portion represents the date as the number of days
relative to 30 Dec, 1899, and the decimal portion represents the time as a
fraction of a day.

 

[Shohoku79]

Hello:

Thanks for the response.

Well, I've attempted to proceed with the idea and can get the dates to fill
in correctly via the Calendar object. However, I am not able to get the
validation process to activate.

I attempted to use the following....

txtTo_AfterUpdate()
If txtFrom.Value > txtTo.Value Then
MsgBox "Invalid Date Range Selected. Please re-select."
Cancel = True
End If
End Sub

What am I not doing correctly?

Thanks,

 

[Douglas J. Steele]

First of all, it needs to be

Private Sub txtTo_AfterUpdate()

but perhaps that was just a problem with copy-and-pasted.

More important, though, is that the AfterUpdate event doesn't have a Cancel
value. Try using the BeforeUpdate event:

Private Sub txtTo_BeforeUpdate(Cancel As Integer)

If txtFrom.Value > txtTo.Value Then
MsgBox "Invalid Date Range Selected. Please re-select."
Cancel = True
End If

End Sub

Of course, you have no way of guaranteeing that your users will fill the
fields in in the order you laid them out. You might want to put to use

Private Sub txtTo_BeforeUpdate(Cancel As Integer)

If IsNull(Me.txtTo) = False And IsNull(Me.txtFrom) = False then
If txtFrom.Value > txtTo.Value Then
MsgBox "Invalid Date Range Selected. Please re-select."
Cancel = True
End If
End If

End Sub

(and put the same code in txtFrom_BeforeUpdate)