Warning if date is in the future

C

CW

I'm trying to prevent users entering dates in the future in various controls
on our Orders form.
So that the date could not be any greater than the current date, in the
underlying table I set the Validation Rule as <Date()+1, with a suitable
message being fired.
However, I noiw get an error message saying that the table cannot be saved
due to an improper use of Date.
What have I got wrong?
Thanks
CW
 
J

John W. Vinson

I'm trying to prevent users entering dates in the future in various controls
on our Orders form.
So that the date could not be any greater than the current date, in the
underlying table I set the Validation Rule as <Date()+1, with a suitable
message being fired.
However, I noiw get an error message saying that the table cannot be saved
due to an improper use of Date.
What have I got wrong?
Thanks
CW
Click to expand...

Do you perhaps have a field named Date? If so it's conflicting with your use
of the Date() function.

I'd actually recommend doing this check in the form's or date control's
beforeupdate event.
 
C

CW

John -
Thanks for the response -
No, I don't have a field called simply Date as such in any of my tables - I
read a number of cautionary posts about it causing problems, so have avoided
it. I do have numerous fields whose names CONTAIN the word Date, but always
within a contiguous field name e.g. QuoteDate, BookedDate, ShippedDate, and
so on. I always assumed - perhaps wrongly - that this would be OK. No doubt
you will advise me if I was wrong on this. (That would be nasty - I must have
100s of fields and controls whose names contain ...date... somewhere within
them!).

If I place this check in the control's BeforeUpdate event, would the syntax
be something like this:

If [BookedDate]>Date() Then
MsgBox "This date cannot be in the future"
End If

Would that be right?
Thanks as always for your assistance
CW
 
G

Glint

I have been using <=Date() in the Validation Rule of the date field in the
original table. What is the disadvantage of doing this?
 
J

John W. Vinson

John -
Thanks for the response -
No, I don't have a field called simply Date as such in any of my tables - I
read a number of cautionary posts about it causing problems, so have avoided
it. I do have numerous fields whose names CONTAIN the word Date, but always
within a contiguous field name e.g. QuoteDate, BookedDate, ShippedDate, and
so on. I always assumed - perhaps wrongly - that this would be OK. No doubt
you will advise me if I was wrong on this. (That would be nasty - I must have
100s of fields and controls whose names contain ...date... somewhere within
them!).
Click to expand...

No, that should be fine. It's just the exact reserved word that would be a
problem.
If I place this check in the control's BeforeUpdate event, would the syntax
be something like this:

If [BookedDate]>Date() Then
MsgBox "This date cannot be in the future"
End If

Would that be right?
Click to expand...

That's a start - that would give the warning but just leave the value. Use the
Code Builder and put in something like:

Private Sub BookedDate_BeforeUpdate(Cancel as Integer)
If Me!BookedDate > Date() Then
MsgBox "This date cannot be in the future"
Cancel = True ' cancel the entry
Me!BookedDate.Undo ' and erase the user input
End If
End Sub
 
J

John W. Vinson

I have been using <=Date() in the Validation Rule of the date field in the
original table. What is the disadvantage of doing this?
Click to expand...

It's just that the table validation rule error messages can be confusing. You
may well want to do BOTH - put a friendly message in the Form or Control
BeforeUpdate event, and *also* validate the data in the table, just to be sure
the user doesn't go around the Form and enter data directly.
 
C

CW

Perfect - just what I needed
Thanks so much
CW

John W. Vinson said:
John -
Thanks for the response -
No, I don't have a field called simply Date as such in any of my tables - I
read a number of cautionary posts about it causing problems, so have avoided
it. I do have numerous fields whose names CONTAIN the word Date, but always
within a contiguous field name e.g. QuoteDate, BookedDate, ShippedDate, and
so on. I always assumed - perhaps wrongly - that this would be OK. No doubt
you will advise me if I was wrong on this. (That would be nasty - I must have
100s of fields and controls whose names contain ...date... somewhere within
them!).
Click to expand...

No, that should be fine. It's just the exact reserved word that would be a
problem.
If I place this check in the control's BeforeUpdate event, would the syntax
be something like this:

If [BookedDate]>Date() Then
MsgBox "This date cannot be in the future"
End If

Would that be right?
Click to expand...

That's a start - that would give the warning but just leave the value. Use the
Code Builder and put in something like:

Private Sub BookedDate_BeforeUpdate(Cancel as Integer)
If Me!BookedDate > Date() Then
MsgBox "This date cannot be in the future"
Cancel = True ' cancel the entry
Me!BookedDate.Undo ' and erase the user input
End If
End Sub
Click to expand...
 
Top