Field Data Validation: Date Range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stuart - 06 Jan 2005 16:49 GMT

Hi All,

I've been trying to figure out the syntax to stick in a Validation Rule for
a date field. The hardcoded dates work ( >= #01/01/2005# and <= #01/31/2005#
), but I need a floating 30 day buffer. Like ">= date and <= date+30".

I've been trying different ways for an hour now, and I know it can't be as
difficult as I'm making it.

Thanks for any help!

Reply to this Message

Stuart - 06 Jan 2005 18:29 GMT

Found it:

"Between date() And date() + 30"

> Hi All,
>
[quoted text clipped - 6 lines]
>
> Thanks for any help!

Reply to this Message

John Vinson - 06 Jan 2005 18:46 GMT

>Hi All,
>
[quoted text clipped - 6 lines]
>
>Thanks for any help!

Try

BETWEEN Date() AND DateAdd("d", 30, Date())

or

>= Date() AND <= Date() + 30

I think the parentheses are necessary or Access will think you're
comparing to a text string "date".

John W. Vinson[MVP]

Reply to this Message

Tim Ferguson - 07 Jan 2005 17:52 GMT

> >= Date() AND <= Date() + 30
>
> I think the parentheses are necessary or Access will think you're
> comparing to a text string "date".

This works for me. One thing strikes me, though: what will Access do when
the clock ticks over and the record becomes invalid without being updated?

I just have a feeling that this rule should be implemented as either a
constraint on a data-entry control (rather than a table field), or else as
a query.

Just a thought

Tim F

Reply to this Message