Date Formula

[Aurora]

I am using Access 2003
I created a preventative maintenance (pm) database that requires the
operator to enter the date when a pm operation is completed. The operators
doing this are not typist and I am finding a lot of error with date data
entry. Such as reversing month and day or putting in the wrong year. For
example: when the date should have been 2/4/08 - the employee is typing
4/2/08 or putting in the wrong year.

This date is an important field as future operations are keyed off of this
date. I want to make the date field limited to "NO date that is greater then
today" or has to be "within 1 month prior to today". Can any one suggest how
I could write a formula that could limit the date field in this way or at
least point to an web page that would be able to help me? I would appreciate
any help I can get.
Thank you - aurora

 

[Roger Carlson]

The Date() function will automatically return today's date. You can use
this in a number of ways. You could have a date field with =Date() as the
Default Value. That would automatically enter the correct date when a new
record is saved. Or you could assign the date with a button click event to
an existing field.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Aurora]

Roger:
Thank you for answering my question. I do not want to use the Date() for
today's date because the operator may wait a week or two before entering the
information required. That is why I wanted to keep the date - between 1
month prior to today's date and today's date. Is there a way to say "between
date()-30 and date()". I would appreciate any help you can give me. Again
"Thank You" for your help.
Aurora

 

[Stockwell43]

Hi Aurora,

Roger is correct with the best and simpliest way to do this. I realize you
are in a situation but it seems like you are going through an awful lot
because people are not able to enter a date. Believe me I work with folks
like this as well and work arounds are very common. )

Try this:

Next to your date field place a command button. In the On Click event place
this code: Me.yourtextboxname = Date()

All they have to do is click the button to prefill the date in your date
field automatically to the current date and if the date needs to be changed,
at lease they have a visual of how it is entered. In fact, on your date field
use the input mask to short date plus, use a label field and place it under
the date field and spell it out MM/DD/YYYY. This will take you about ten
minutes to set up and if it works fine if not then you may have to come back
and try asking the question again.

I hope this works out. Give it a try. For ten minutes it may save you from
waiting for a more on the money response.

 

[Bob Quintal]

I am using Access 2003
I created a preventative maintenance (pm) database that requires
the operator to enter the date when a pm operation is completed.
The operators doing this are not typist and I am finding a lot of
error with date data entry. Such as reversing month and day or
putting in the wrong year. For example: when the date should
have been 2/4/08 - the employee is typing 4/2/08 or putting in the
wrong year.

This date is an important field as future operations are keyed off
of this date. I want to make the date field limited to "NO date
that is greater then today" or has to be "within 1 month prior to
today". Can any one suggest how I could write a formula that
could limit the date field in this way or at least point to an web
page that would be able to help me? I would appreciate any help I
can get. Thank you - aurora

Set the date textbox's before update event to [Event Procedure] and
then click the edit(...) button
Say your textbox is PMDate
Access will open the Visual Basic Editor and insert the event
procedure's declaration and and lines.
In between you need to test for

IF datediff("d",me.PMDate,date()) < 0 then
cancel = true
me.PMDate.undo ' clear the date entered.
msgbox "Date cannot be future"
end if
IF datediff("d",me.PMDate,date()) > 30 then
cancel = true
me.PMDate.undo ' clear the date entered.
msgbox "Date too long ago"
end if

and so on, until you've covered all the problems you need to catch