Validate Two Date Fields

[Guest]

I want to validate two date fields so one date cannot be later than the other.

frmReferral accepts two date entries named txtReferralDate and
txtDischargeDate (underlying fields are Date/Time - Short Date format). A
pop-up calendar populates both txt boxes. I want to stop the user adding a
date in txtDischargeDate that is earlier than txtReferralDate. Perhaps
showing a message box.

I have searched this site - read all entries on Dates and looked elsewhere!

M

 

[Guest]

On the before Update event of the form, you can add the validation check

If Not IsNull(Me.txtReferralDate) And Not IsNull(Me.txtDischargeDate) Then
If Me.txtReferralDate > Me.txtDischargeDate Then
MsgBox "Date Discharge must be grater"
Cancel = True 'wont let the user exit the form
End If
End If

 

[Guest]

Thanks that worked great - so simple - however when I click the OK button on
the MsgBox the form does close. When I use the 'Exit' cmd button after
entering the dates the MsgBox appears - I click OK and the form closes (no
record created). It is a subfrm I am using - with a value passed from a
field from the mainfrm .

nB

 

[Allen Browne]

M, you might prefer to do this in the table itself rather than the form.

1. Open your table in design view.
2. Open the Properties box (View menu.)
3. Beside the Validation Rule in the Properties box, enter:
[DischargeDate] >= [ReferralDate]
4. Save.

The Validation Rule in the Properties box is the one for the table
(record-level validation), so that's what you need to use for comparing
fields.

In older versions of Access, that rule would have the effect of making both
fields required. You might want to use:
([DischargeDate] Is Null) OR
([DischargeDate] Is Null) OR
([DischargeDate] >= [ReferralDate])

 

[Guest]

Thanks Allen, works great - very smooth - however how do I alert the user the
dates are wrong and stop the form from closing - so they can make the
necessary changes.

M

M, you might prefer to do this in the table itself rather than the form.

1. Open your table in design view.
2. Open the Properties box (View menu.)
3. Beside the Validation Rule in the Properties box, enter:
[DischargeDate] >= [ReferralDate]
4. Save.

The Validation Rule in the Properties box is the one for the table
(record-level validation), so that's what you need to use for comparing
fields.

In older versions of Access, that rule would have the effect of making both
fields required. You might want to use:
([DischargeDate] Is Null) OR
([DischargeDate] Is Null) OR
([DischargeDate] >= [ReferralDate])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I want to validate two date fields so one date cannot be later than the
other.

frmReferral accepts two date entries named txtReferralDate and
txtDischargeDate (underlying fields are Date/Time - Short Date format). A
pop-up calendar populates both txt boxes. I want to stop the user adding
a
date in txtDischargeDate that is earlier than txtReferralDate. Perhaps
showing a message box.

I have searched this site - read all entries on Dates and looked
elsewhere!

M

 

[Allen Browne]

Whatever message you wish to display, you can place in the Validation Text
property of the table (Properties box in table design view.)

Now, if the user enters a record that does not meet the rule, the bad entry
is blocked, and your message is displayed. That happens regardless of how
the save occurred (e.g. moving to another record, applying a filter,
changing the sort order, closing the form, closing Access, pressing
Shift+Enter, etc.)

If the user was closing the form by pressing the built-in Close button (X at
the right end of the form's Title bar), Access will give them a suitable
message that they will lose the entry if they really close, and give them
the option to back up and fix the issue.

If you Use the Close action in a macro or code to close the form, Access
just silently loses the user's entry. To me, that's a pretty serious bug, so
we have it outlined in this article:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html
As the article suggests, once you know about the bug, you can add a line to
your macro or code to force the record to save before you ever issue a Close
on a bound form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen, works great - very smooth - however how do I alert the user
the
dates are wrong and stop the form from closing - so they can make the
necessary changes.

M

M, you might prefer to do this in the table itself rather than the form.

1. Open your table in design view.
2. Open the Properties box (View menu.)
3. Beside the Validation Rule in the Properties box, enter:
[DischargeDate] >= [ReferralDate]
4. Save.

The Validation Rule in the Properties box is the one for the table
(record-level validation), so that's what you need to use for comparing
fields.

In older versions of Access, that rule would have the effect of making
both
fields required. You might want to use:
([DischargeDate] Is Null) OR
([DischargeDate] Is Null) OR
([DischargeDate] >= [ReferralDate])

I want to validate two date fields so one date cannot be later than the
other.

frmReferral accepts two date entries named txtReferralDate and
txtDischargeDate (underlying fields are Date/Time - Short Date format).
A
pop-up calendar populates both txt boxes. I want to stop the user
adding
a
date in txtDischargeDate that is earlier than txtReferralDate. Perhaps
showing a message box.

I have searched this site - read all entries on Dates and looked
elsewhere!

 

[Guest]

Thanks Allen however aftern placing some text in the Validation Text property
(Properties box in table design) I still dont get a message displayed - the
form closes and no date data is saved. If there was other data entered into
other controls then that data is saved.

M

Whatever message you wish to display, you can place in the Validation Text
property of the table (Properties box in table design view.)

Now, if the user enters a record that does not meet the rule, the bad entry
is blocked, and your message is displayed. That happens regardless of how
the save occurred (e.g. moving to another record, applying a filter,
changing the sort order, closing the form, closing Access, pressing
Shift+Enter, etc.)

If the user was closing the form by pressing the built-in Close button (X at
the right end of the form's Title bar), Access will give them a suitable
message that they will lose the entry if they really close, and give them
the option to back up and fix the issue.

If you Use the Close action in a macro or code to close the form, Access
just silently loses the user's entry. To me, that's a pretty serious bug, so
we have it outlined in this article:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html
As the article suggests, once you know about the bug, you can add a line to
your macro or code to force the record to save before you ever issue a Close
on a bound form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen, works great - very smooth - however how do I alert the user
the
dates are wrong and stop the form from closing - so they can make the
necessary changes.

M

M, you might prefer to do this in the table itself rather than the form.

1. Open your table in design view.
2. Open the Properties box (View menu.)
3. Beside the Validation Rule in the Properties box, enter:
[DischargeDate] >= [ReferralDate]
4. Save.

The Validation Rule in the Properties box is the one for the table
(record-level validation), so that's what you need to use for comparing
fields.

In older versions of Access, that rule would have the effect of making
both
fields required. You might want to use:
([DischargeDate] Is Null) OR
([DischargeDate] Is Null) OR
([DischargeDate] >= [ReferralDate])

I want to validate two date fields so one date cannot be later than the
other.

frmReferral accepts two date entries named txtReferralDate and
txtDischargeDate (underlying fields are Date/Time - Short Date format).
A
pop-up calendar populates both txt boxes. I want to stop the user
adding
a
date in txtDischargeDate that is earlier than txtReferralDate. Perhaps
showing a message box.

I have searched this site - read all entries on Dates and looked
elsewhere!

 

[Allen Browne]

Is the form bound to your table (its Record Source property)?

Is the text box bound to your field (its Control Source property)?

How are you closing the form?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen however aftern placing some text in the Validation Text
property
(Properties box in table design) I still dont get a message displayed -
the
form closes and no date data is saved. If there was other data entered
into
other controls then that data is saved.

M

Whatever message you wish to display, you can place in the Validation
Text
property of the table (Properties box in table design view.)

Now, if the user enters a record that does not meet the rule, the bad
entry
is blocked, and your message is displayed. That happens regardless of how
the save occurred (e.g. moving to another record, applying a filter,
changing the sort order, closing the form, closing Access, pressing
Shift+Enter, etc.)

If the user was closing the form by pressing the built-in Close button (X
at
the right end of the form's Title bar), Access will give them a suitable
message that they will lose the entry if they really close, and give them
the option to back up and fix the issue.

If you Use the Close action in a macro or code to close the form, Access
just silently loses the user's entry. To me, that's a pretty serious bug,
so
we have it outlined in this article:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html
As the article suggests, once you know about the bug, you can add a line
to
your macro or code to force the record to save before you ever issue a
Close
on a bound form.

Thanks Allen, works great - very smooth - however how do I alert the
user
the
dates are wrong and stop the form from closing - so they can make the
necessary changes.

M

:

M, you might prefer to do this in the table itself rather than the
form.

1. Open your table in design view.
2. Open the Properties box (View menu.)
3. Beside the Validation Rule in the Properties box, enter:
[DischargeDate] >= [ReferralDate]
4. Save.

The Validation Rule in the Properties box is the one for the table
(record-level validation), so that's what you need to use for
comparing
fields.

In older versions of Access, that rule would have the effect of making
both
fields required. You might want to use:
([DischargeDate] Is Null) OR
([DischargeDate] Is Null) OR
([DischargeDate] >= [ReferralDate])

I want to validate two date fields so one date cannot be later than
the
other.

frmReferral accepts two date entries named txtReferralDate and
txtDischargeDate (underlying fields are Date/Time - Short Date
format).
A
pop-up calendar populates both txt boxes. I want to stop the user
adding
a
date in txtDischargeDate that is earlier than txtReferralDate.
Perhaps
showing a message box.

I have searched this site - read all entries on Dates and looked
elsewhere!

 

[Guest]

Yes and Yes.

fsubReferral is opened via cmdAddNew (from frmPatient), cmdAddNew allocates
frmPatient PatientID to a new record in fsubReferral PatientID (the link):

Private Sub cmdAddNew_Click()
Dim PatientID As Integer
PatientID = Forms!frmPatient.[PatientID]
DoCmd.OpenForm "fsubReferral", acNormal, "", acAdd, acNormal
Forms!fsubReferral.SetFocus
Forms!fsubReferral.[PatientID] = PatientID
End Sub

Once fsubReferral is open data can be entered into the form - Date
Validation is set at the table level. Recordsource of fsubReferral is
tblReferral and recordsource of both Date fields is the relevant field from
tblReferral.

If the incorrect dates had been entered (DischargeDate earlier than
ReferralDate) this data is not saved - if no other data was entered no record
is saved and the form closes. If other data was entered this data - and not
the incorrect Date data - is saved and fsubReferral closes.

fsubReferral is exited via cmdClose.


Private Sub CmdClose_Click()
On Error GoTo Err_CmdClose_Click

DoCmd.Close

Exit_CmdClose_Click:
Exit Sub

Err_CmdClose_Click:
MsgBox Err.Description
Resume Exit_CmdClose_Click

End Sub

Is the form bound to your table (its Record Source property)?

Is the text box bound to your field (its Control Source property)?

How are you closing the form?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen however aftern placing some text in the Validation Text
property
(Properties box in table design) I still dont get a message displayed -
the
form closes and no date data is saved. If there was other data entered
into
other controls then that data is saved.

M

Whatever message you wish to display, you can place in the Validation
Text
property of the table (Properties box in table design view.)

Now, if the user enters a record that does not meet the rule, the bad
entry
is blocked, and your message is displayed. That happens regardless of how
the save occurred (e.g. moving to another record, applying a filter,
changing the sort order, closing the form, closing Access, pressing
Shift+Enter, etc.)

If the user was closing the form by pressing the built-in Close button (X
at
the right end of the form's Title bar), Access will give them a suitable
message that they will lose the entry if they really close, and give them
the option to back up and fix the issue.

If you Use the Close action in a macro or code to close the form, Access
just silently loses the user's entry. To me, that's a pretty serious bug,
so
we have it outlined in this article:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html
As the article suggests, once you know about the bug, you can add a line
to
your macro or code to force the record to save before you ever issue a
Close
on a bound form.

Thanks Allen, works great - very smooth - however how do I alert the
user
the
dates are wrong and stop the form from closing - so they can make the
necessary changes.

M

:

M, you might prefer to do this in the table itself rather than the
form.

1. Open your table in design view.
2. Open the Properties box (View menu.)
3. Beside the Validation Rule in the Properties box, enter:
[DischargeDate] >= [ReferralDate]
4. Save.

The Validation Rule in the Properties box is the one for the table
(record-level validation), so that's what you need to use for
comparing
fields.

In older versions of Access, that rule would have the effect of making
both
fields required. You might want to use:
([DischargeDate] Is Null) OR
([DischargeDate] Is Null) OR
([DischargeDate] >= [ReferralDate])

I want to validate two date fields so one date cannot be later than
the
other.

frmReferral accepts two date entries named txtReferralDate and
txtDischargeDate (underlying fields are Date/Time - Short Date
format).
A
pop-up calendar populates both txt boxes. I want to stop the user
adding
a
date in txtDischargeDate that is earlier than txtReferralDate.
Perhaps
showing a message box.

I have searched this site - read all entries on Dates and looked
elsewhere!

 

[Allen Browne]

Your cmdClose button uses the Close method.

That is exactly the scenario discussed in the article I suggested:
http://allenbrowne.com/bug-01.html
The workaround is to force the record save, and the article gives you 4
alternatives, discusses their merits, and provides other links.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yes and Yes.

fsubReferral is opened via cmdAddNew (from frmPatient), cmdAddNew
allocates
frmPatient PatientID to a new record in fsubReferral PatientID (the link):

Private Sub cmdAddNew_Click()
Dim PatientID As Integer
PatientID = Forms!frmPatient.[PatientID]
DoCmd.OpenForm "fsubReferral", acNormal, "", acAdd, acNormal
Forms!fsubReferral.SetFocus
Forms!fsubReferral.[PatientID] = PatientID
End Sub

Once fsubReferral is open data can be entered into the form - Date
Validation is set at the table level. Recordsource of fsubReferral is
tblReferral and recordsource of both Date fields is the relevant field
from
tblReferral.

If the incorrect dates had been entered (DischargeDate earlier than
ReferralDate) this data is not saved - if no other data was entered no
record
is saved and the form closes. If other data was entered this data - and
not
the incorrect Date data - is saved and fsubReferral closes.

fsubReferral is exited via cmdClose.


Private Sub CmdClose_Click()
On Error GoTo Err_CmdClose_Click

DoCmd.Close

Exit_CmdClose_Click:
Exit Sub

Err_CmdClose_Click:
MsgBox Err.Description
Resume Exit_CmdClose_Click

End Sub

Is the form bound to your table (its Record Source property)?

Is the text box bound to your field (its Control Source property)?

How are you closing the form?

Thanks Allen however aftern placing some text in the Validation Text
property
(Properties box in table design) I still dont get a message displayed -
the
form closes and no date data is saved. If there was other data entered
into
other controls then that data is saved.

M

:

Whatever message you wish to display, you can place in the Validation
Text
property of the table (Properties box in table design view.)

Now, if the user enters a record that does not meet the rule, the bad
entry
is blocked, and your message is displayed. That happens regardless of
how
the save occurred (e.g. moving to another record, applying a filter,
changing the sort order, closing the form, closing Access, pressing
Shift+Enter, etc.)

If the user was closing the form by pressing the built-in Close button
(X
at
the right end of the form's Title bar), Access will give them a
suitable
message that they will lose the entry if they really close, and give
them
the option to back up and fix the issue.

If you Use the Close action in a macro or code to close the form,
Access
just silently loses the user's entry. To me, that's a pretty serious
bug,
so
we have it outlined in this article:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html
As the article suggests, once you know about the bug, you can add a
line
to
your macro or code to force the record to save before you ever issue a
Close
on a bound form.

Thanks Allen, works great - very smooth - however how do I alert the
user
the
dates are wrong and stop the form from closing - so they can make
the
necessary changes.

M

:

M, you might prefer to do this in the table itself rather than the
form.

1. Open your table in design view.
2. Open the Properties box (View menu.)
3. Beside the Validation Rule in the Properties box, enter:
[DischargeDate] >= [ReferralDate]
4. Save.

The Validation Rule in the Properties box is the one for the table
(record-level validation), so that's what you need to use for
comparing
fields.

In older versions of Access, that rule would have the effect of
making
both
fields required. You might want to use:
([DischargeDate] Is Null) OR
([DischargeDate] Is Null) OR
([DischargeDate] >= [ReferralDate])

message
I want to validate two date fields so one date cannot be later
than
the
other.

frmReferral accepts two date entries named txtReferralDate and
txtDischargeDate (underlying fields are Date/Time - Short Date
format).
A
pop-up calendar populates both txt boxes. I want to stop the
user
adding
a
date in txtDischargeDate that is earlier than txtReferralDate.
Perhaps
showing a message box.

I have searched this site - read all entries on Dates and looked
elsewhere!

 

[Guest]

Thanks Allen, my apologies for not looking at the article when you first
suggested. It works great and I am very happy.

Cheers,
M

Your cmdClose button uses the Close method.

That is exactly the scenario discussed in the article I suggested:
http://allenbrowne.com/bug-01.html
The workaround is to force the record save, and the article gives you 4
alternatives, discusses their merits, and provides other links.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yes and Yes.

fsubReferral is opened via cmdAddNew (from frmPatient), cmdAddNew
allocates
frmPatient PatientID to a new record in fsubReferral PatientID (the link):

Private Sub cmdAddNew_Click()
Dim PatientID As Integer
PatientID = Forms!frmPatient.[PatientID]
DoCmd.OpenForm "fsubReferral", acNormal, "", acAdd, acNormal
Forms!fsubReferral.SetFocus
Forms!fsubReferral.[PatientID] = PatientID
End Sub

Once fsubReferral is open data can be entered into the form - Date
Validation is set at the table level. Recordsource of fsubReferral is
tblReferral and recordsource of both Date fields is the relevant field
from
tblReferral.

If the incorrect dates had been entered (DischargeDate earlier than
ReferralDate) this data is not saved - if no other data was entered no
record
is saved and the form closes. If other data was entered this data - and
not
the incorrect Date data - is saved and fsubReferral closes.

fsubReferral is exited via cmdClose.


Private Sub CmdClose_Click()
On Error GoTo Err_CmdClose_Click

DoCmd.Close

Exit_CmdClose_Click:
Exit Sub

Err_CmdClose_Click:
MsgBox Err.Description
Resume Exit_CmdClose_Click

End Sub

Is the form bound to your table (its Record Source property)?

Is the text box bound to your field (its Control Source property)?

How are you closing the form?

Thanks Allen however aftern placing some text in the Validation Text
property
(Properties box in table design) I still dont get a message displayed -
the
form closes and no date data is saved. If there was other data entered
into
other controls then that data is saved.

M

:

Whatever message you wish to display, you can place in the Validation
Text
property of the table (Properties box in table design view.)

Now, if the user enters a record that does not meet the rule, the bad
entry
is blocked, and your message is displayed. That happens regardless of
how
the save occurred (e.g. moving to another record, applying a filter,
changing the sort order, closing the form, closing Access, pressing
Shift+Enter, etc.)

If the user was closing the form by pressing the built-in Close button
(X
at
the right end of the form's Title bar), Access will give them a
suitable
message that they will lose the entry if they really close, and give
them
the option to back up and fix the issue.

If you Use the Close action in a macro or code to close the form,
Access
just silently loses the user's entry. To me, that's a pretty serious
bug,
so
we have it outlined in this article:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html
As the article suggests, once you know about the bug, you can add a
line
to
your macro or code to force the record to save before you ever issue a
Close
on a bound form.

Thanks Allen, works great - very smooth - however how do I alert the
user
the
dates are wrong and stop the form from closing - so they can make
the
necessary changes.

M

:

M, you might prefer to do this in the table itself rather than the
form.

1. Open your table in design view.
2. Open the Properties box (View menu.)
3. Beside the Validation Rule in the Properties box, enter:
[DischargeDate] >= [ReferralDate]
4. Save.

The Validation Rule in the Properties box is the one for the table
(record-level validation), so that's what you need to use for
comparing
fields.

In older versions of Access, that rule would have the effect of
making
both
fields required. You might want to use:
([DischargeDate] Is Null) OR
([DischargeDate] Is Null) OR
([DischargeDate] >= [ReferralDate])

message
I want to validate two date fields so one date cannot be later
than
the
other.

frmReferral accepts two date entries named txtReferralDate and
txtDischargeDate (underlying fields are Date/Time - Short Date
format).
A
pop-up calendar populates both txt boxes. I want to stop the
user
adding
a
date in txtDischargeDate that is earlier than txtReferralDate.
Perhaps
showing a message box.

I have searched this site - read all entries on Dates and looked
elsewhere!