Data Validation date field

[Jonathan Brown]

I don't understand why this isn't working. I just want to make sure that
they don't put a future date in the polydate field. Is me.polydate > now()
an invalid expression?

code:
_________________________________________________________
Private Sub Form_BeforeInsert(Cancel As Integer)

If Me.PolyDate > Now() Then
Cancel = True
Me.PolyDate.SetFocus
MsgBox "Please enter a date that falls prior to today's date"
Exit Sub
End If

End Sub

 

[Klatuu]

It does not appear to be invalid. It is always helpful to post the error you
are getting and if it is a runtime error, point out the error number and
description and the line on which the error occurs.

 

[Linq Adams via AccessMonster.com]

As Klatuu said, the code's valid. You do understand that the messagebox won't
appear until Access tries to save the record, don't you? If you want it to
appear immediately after entering the date, you need to move the code to
another event, like the BeforeUpdate event of the Polydate control itself. If
you do this, you'll need to drop the line:

Me.PolyDate.SetFocus

which you won't need anyway.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com

 

[Jonathan Brown]

I wasn't getting an error message at all. It was allowing me to save the
record no matter what date I put in there; past or future.

I decided to just go ahead and use the following:

if datediff("d", me.polydate, now()) < 0 then
etc, etc, etc.

It works if I do it that way.

 

[John W. Vinson]

I don't understand why this isn't working. I just want to make sure that
they don't put a future date in the polydate field. Is me.polydate > now()
an invalid expression?

Use the BeforeUpdate event of either polydate or of the form, instead of the
Form's BeforeInsert event. BeforeInsert executes the moment you dirty the
form, with the very first keystroke; polydate will not have any value at that
point.

John W. Vinson [MVP]

 

[Allan Murphy]

Jonathan

It will never work because you are referring to Now() this is in the format
date + time.

I fell into the same trap.

If you are testing for Me.PolyDate is greater than the current date it
should be

If Me.PolyDate > Date() Then
Cancel = True
Me.PolyDate.SetFocus
MsgBox "Please enter a date that falls prior to today's date"
Exit Sub
End If


Allan

 

[Linq Adams via AccessMonster.com]

Not true, Allan, at least in ACC2000-2003! The code works fine for me, using
a date vs Now(). When entering data that only contains a "date" Access adds
the "time" component of 00:00:00. So the code is esentially comparing

polydate 00:00:00

to

Now()

 

[Jonathan Brown]

Another question on Date validation:

What if I want to make sure they don't put in a date prior to 1/1/1900?

I tried the following but I got a data type mismatch error.

if me.polydate < datevalue(1/1/1900) then
cancel = true
....etc.
end if

Is datevalue the wrong function too? I'm apparently not very good with
these date functions. The PolyDate field in my clearances table is of the
short date data type.

 

[John W. Vinson]

Another question on Date validation:

What if I want to make sure they don't put in a date prior to 1/1/1900?

I tried the following but I got a data type mismatch error.

if me.polydate < datevalue(1/1/1900) then
cancel = true
...etc.
end if

Is datevalue the wrong function too? I'm apparently not very good with
these date functions. The PolyDate field in my clearances table is of the
short date data type.

No. It's a Date/Time datatype, not a "Short Date" datatype. A date value -
regardless of format! - is stored as a Double Float number, a count of days
and fractions of a day since midnight, December 30, 1899. The format merely
controls how that number value is displayed; you could have the same value
displayed many different ways in different parts of your application if you
wish.

The DateValue function accepts a Text String as a value. You're feeding it
what looks to you like a date, but to Access it looks like a calculation - 1
divided by 1 divided by 1900. That will give you some very small number, not a
text string - hence the error message!

For a date constant, use # as the delimiter:

If Me.polydate < #1/1/1900# Then

The # character tells Access "I'm giving you a Date/Time value, translate it
to your wierd number depiction so you can use it".

John W. Vinson [MVP]