How to prevent entering older dates

  • Thread starter Thread starter RJS76 via AccessMonster.com
  • Start date Start date
R

RJS76 via AccessMonster.com

Hi,

I have a query in which three dates have to be entered. Field names are:

[Datum klantverzending]
[Datum ontvangst]
[Datum afhandeling]

[Datum ontvangst] can be equal to [Datum klantverzending] but it can never be
older than [Datum klantverzending].
[Datum afhandeling] can be equal to [Datum ontvangst] but it can never be
older than [Datum ontvangst].

At this time users can enter dates that are older which messes up my
calculations. Is there a way to prevent this?

Thanks for your help.
 
If you enter the data in a form that is bound to the query, you can use the
After Update event of the text box bound to [Datum ontvangst]:

If [Datum ontvangst] < [Datum klantverzending] Then
msgbox "You can't do that"
' Whatever needs to happen
End If

You could similarly use the After Update event of the text box bound to
[Datum afhandeling] to check that date against [Datum ontvangst]. You could
probably use one event to check both, if there is a convenient place for it,
but I would think that the After Update event of each text box is a good
place for the code. The point is that the checking is done in the form, not
directly in the query. Users shouldn't be entering data directly into
tables or queries. You can use the datasheet view of a form to simulate
that look.
 
Are your users entering directly into the query or are they using forms to
do this?
You can set up a Table Level Validation rule in the Table design mode. This
would always be in effect and would not let the users ever enter a record
that did not comply with the rules. However, it is a bit unfriendly, since
the warning message doesn't appear until Access attempts to save the record.

In a form you can use the after update event of each control to do the
necessary comparison and tell the users what the problem is.

You could combine the two methods if you wish. I don't know how you want to
handle null (blank) dates. Assuming that your date fields are required the
table level rule would look like:

Table Level Validation Rule:
[Datum ontvangst] >=[Datum klantverzending] And [Datum afhandeling] >=
[Datum ontvangst]
 
Hello Bruce,

I have tried what you suggested and it works perfectly. Thank you very much
for your help.

Best regards,

RJS
If you enter the data in a form that is bound to the query, you can use the
After Update event of the text box bound to [Datum ontvangst]:

If [Datum ontvangst] < [Datum klantverzending] Then
msgbox "You can't do that"
' Whatever needs to happen
End If

You could similarly use the After Update event of the text box bound to
[Datum afhandeling] to check that date against [Datum ontvangst]. You could
probably use one event to check both, if there is a convenient place for it,
but I would think that the After Update event of each text box is a good
place for the code. The point is that the checking is done in the form, not
directly in the query. Users shouldn't be entering data directly into
tables or queries. You can use the datasheet view of a form to simulate
that look.
Hi,
Click to expand...
[quoted text clipped - 14 lines]
Thanks for your help.
Click to expand...
Click to expand...
 
Thank you for your reply John. I will also take a look at this.

John said:
Are your users entering directly into the query or are they using forms to
do this?
You can set up a Table Level Validation rule in the Table design mode. This
would always be in effect and would not let the users ever enter a record
that did not comply with the rules. However, it is a bit unfriendly, since
the warning message doesn't appear until Access attempts to save the record.

In a form you can use the after update event of each control to do the
necessary comparison and tell the users what the problem is.

You could combine the two methods if you wish. I don't know how you want to
handle null (blank) dates. Assuming that your date fields are required the
table level rule would look like:

Table Level Validation Rule:
[Datum ontvangst] >=[Datum klantverzending] And [Datum afhandeling] >=
[Datum ontvangst]
Hi,
Click to expand...
[quoted text clipped - 14 lines]
Thanks for your help.
Click to expand...
Click to expand...
 
Back
Top