Date Formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Aurora - 11 Mar 2008 19:17 GMT

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

Reply to this Message

Roger Carlson - 11 Mar 2008 19:28 GMT

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.

Signature

--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

>I am using Access 2003
> I created a preventative maintenance (pm) database that requires the
[quoted text clipped - 15 lines]
> any help I can get.
> Thank you - aurora

Reply to this Message

Aurora - 11 Mar 2008 20:49 GMT

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

> 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
[quoted text clipped - 21 lines]
> > any help I can get.
> > Thank you - aurora

Reply to this Message

Stockwell43 - 11 Mar 2008 21:15 GMT

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. :o)

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.

> Roger:
> Thank you for answering my question. I do not want to use the Date() for
[quoted text clipped - 30 lines]
> > > any help I can get.
> > > Thank you - aurora

Reply to this Message

Bob Quintal - 12 Mar 2008 00:10 GMT

> I am using Access 2003
> I created a preventative maintenance (pm) database that requires
[quoted text clipped - 12 lines]
> 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

Signature

Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Reply to this Message