how do i validate date input

[Guest]

Hi. I'm an ICT Student from the UK doing a piece of coursework for an
A-Level course (taken at the age of 18.) I will not go through the whole
explanation of the project, but a validation rule i am trying to invent is
proving to be a menace. A quick explanation on what I want it to do is as
follows (using the current validation as an example.)

Not Between #19/12/2005# And #02/01/2006#

So basically, the current validation rule is that it is not between the 19th
of December 2005 and the 2nd of Jan 2006. That is fine month and day wise,
but there is a limitation on the date. I need it so the date represents an x
value of sorts, but have not found a way of doing it. I will probably have
to change the whole formula but any help will be much appreciated. Thank you.

 

[Ken Snell [MVP]]

What do you mean by "I need it so the date represents an x value of sorts"?

I have no idea what that will be?

 

[Guest]

Basically, it needs to be set so the date can be any date, so regardless of
the year the validation rule disallows those dates. At the moment the
validation rule is limited to the year 2005-2006, whereas it needs to be
constant. (the x value refers to the way an x is used in mathamatical
formulae, it can be any number.)

 

[Ken Snell [MVP]]

So what you want is to disallow the month-day range regardless of the year?

Easiest way would be to use the Month and Day functions, or the Format
function, to convert the field date into a format that has just the month
and day in a numerical sequence, such as mmdd format (so your range would be
0103 to 1228 for acceptable dates). Then test to see if the mmdd value of
your field's value is between this range or not. This should get you
started....

 

[Guest]

I have tried changing the date format to "dd/mm", however all this does is
change the way in which the dates are displayed - it does not make any
difference to the validation rule which still adds the year to the end of the
month and day. I am also finding the Month and Date functions difficult to
use, as i do not have much experience writing the syntax for them.

Thanks for the help so far, but could you possibly give me a hand on how to
write the syntax to disallow the day/month regardless of the year, if you
have any idea?

 

[Ken Snell [MVP]]

No, my post did not say to change the date format of the field.

I referred to the Format function (a VBA function within ACCESS) to extract
a portion of a date/time value that you want. For example:
MonthNumber = Format(Date(), "mm")
DayNumber = Format(Date(), "dd")

For today's date (February 1), the above MonthNumber would get 02, and
DayNumber would get 01.

Similarly,
MonthNumber = Month(Date())
DayNumber = Day(Date())

You need to build a calculated field in the query that gives you a mmdd
result from a date value.and then write a criterion expression against this
calculated field.