check date if it falls within a month

[Associates]

Hi,

I was wondering if anyone might be able to help me here.

What i am trying to do here is to be able to tell if the entered date by a
user is valid in a sense that it still falls within the range of a month.

For example, users might by accident enter in 29/02/2010 when there was no
such date in the calendar. I want to be able to write a code to check the
date before proceeding any further. The other problem I am having is since
the date field is of type date/time, everytime trying to put in 29/02/2010 in
the textbox, Access will change it into 2029/02/10 automatically. I can not
do anything if it keeps doing that.

Wonder if anyone might be able to share some thoughts here.

Thank you in advance

 

[Daniel Pineault]

My original post on this subject seems to have vanished into thin air, so
here is take 2.

I learnt many years ago not to leat users enter dates manually (ie:
keyboard) for a number of reasons, amongst other:
-format problems (dd/mm/yy or mm/dd/yy or some other format)
-invalid date entries (2010-Feb-29, 2010-Jun-31,...)
-typos

This is why, and I cannot urge this enough, you need to implement a pop-up
calendar date picker. They are faster (only require a click or two of the
mouse) and will validate and ensure proper formating of the entries.

For more on the subject check out

http://www.devhut.net/index.php?lang=en&pid=0000000004#datepick
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[vanderghast]

You probably meant "29/02/10" being converted to a date in year 2029, since
"29/02/2010" produces an error, when you try to convert it to a date:

? IsDate("29/02/2010")
false



While "29/02/10" behave as you said (at least, here too, may be different
for other computer settings, mine is expecting yyyy-mm-dd by default).


There is no much you can do to remove any error of typing, but something
which can help is to FORMAT the date in Long Date format. So, even if a edit
text control would accept "29/02/10" as date, it will display it back as
Saturday, 10 February, 2029 (with the help of Regional Setting set to
display that kind of information, with a long date format, by default), and
thus, the end user can immediately see that what has been entered is wrong.
NO coding required, just to specify the Format property to Long Date (and
eventually set the Regional Setting appropriately).

Access 2007 and later has a smart tag which allows your end user to select a
date from a calendar if the edit text control has a date format. Again, no
coding required.


Vanderghast, Access MVP

 

[CJ Ardash]

You could als build a validation routine with a range of acceptable dates in
it. Here's an example of a function which will return True if the value
passed is a date which is less than 5 years from today's date and False if
it's anything else:

Public Function Valid_Date(varDate As Variant) As Boolean
Dim dtmDate As Date

If IsDate(varDate) Then
dtmDate = CDate(varDate)
If dtmDate > DateAdd("y", 5, Date) Then
Valid_Date = False
Else
Valid_Date = True
End If
Else
Valid_Date = False
End If
End Function