Date field

[Karyn]

As we go into a new year, is there a formula I can enter on my subform in the date field that will tell me if I enter the wrong year? Example: You entered 1/1/00 and it should be 1/1/01.

Any help in advance is greatly appreciated.

 

[Atomic Shrimp]

You could set up validation so that, for example, if the user enters a date in the past, a prompt would ask for confirmation, but if the need arises to enter lots of data with genuinely historical dates, it's going to get annoying.

(So I need to know what sort of dates is the user entering; i.e. are they always in the future, if so, how far in the future?, do they include the possibility of dates in the recent past, if so, how recent?)

Mike

 

[Jack Cowley]

This should work for you. Put it in the Before Update event of your Date Field.

If DatePart("yyyy", Me![DateFieldName]) <> "2001" Then
MsgBox "You entered the wrong year! Please try again."
Cancel = True
End If

[This message has been edited by Jack Cowley (edited 12-20-2000).]

[This message has been edited by Jack Cowley (edited 12-20-2000).]

 

[Karyn]

Mike,

In response to your question, as we enter the new year, I want to make sure that the users don't enter the year 2000. If they enter the year 2000 and they run a report based on data for the year 2001, the data won't show up.

 

[Atomic Shrimp]

Probably the safest and easiest way (I think) would be to prompt users if they enter a date before the current system date, so as per Jack's instructions above, but I would change it to :

If Me.dateFieldName < Now() Then
MsgBox "That date is in the past! Please try again."
Cancel = True
End If


HTH

Mike


[This message has been edited by Mike Gurman (edited 12-22-2000).]

 

[Jack Cowley]

Mike -

Won't your code always be triggered if they select any date that is earlier than todays date? What if they want to enter data using yesterdays date? Also since you are use Now() it will reject today's date unless the date entered includes a time that is earlier than the system time. This would only allow the user to enter dates in the future, or have I missed something (which is entirely possible!)?


[This message has been edited by Jack Cowley (edited 12-22-2000).]

 

[SomeGuy]

I don't know if this will work but I would modify Jack's code so it could be used far in the future without needing updating

If DatePart("yyyy", Me![DateFieldName])<> Year(Now()) Then
MsgBox "You entered the wrong year! Please try again."
Cancel = True
End If

However, if you anticipate enter dates far into the future (like for next year) you could change the inequality to < Year(Now())

 

[Jack Cowley]

SomeGuy - Nice improvement on my bit of code. I like it and am only upset that I did not think of it!

 

[Karyn]

Thanks for all your help. The last code from "SomeGuy" works great.