Re: Validate Two Date Fields

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

"Allen Browne" wrote:

> 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.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "noodleBrain" <noodleBrain@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:25709BC8-8CD8-483F-893B-B499D6B63FD5@xxxxxxxxxxxxxxxx
> > 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
> >
> > "Allen Browne" wrote:
> >
> >> 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])
> >>
> >> "noodleBrain" <noodleBrain@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:A524B863-085E-48F2-AAE5-A1108A94C9C7@xxxxxxxxxxxxxxxx
> >> >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!
>
>
>
.

Loading