Validation Rule on Field in a Table

[mariaw]

Hi

I have a form which people fill in (made up of fields from Table1) to record when sick days.

On the form, they have to enter the "week commencing" (which is set at Medium Date format); then they have to enter in a first day (which is set at Short Date format).

The First Day should be no more than 7 days from the date that they enter in the Week commencing field on the form.

I want an error message to pop up if they do this, for example:

Week commencing: 1st April 2007
First Day: 10th April 2007

(because the 10th April 2007 is more than 7 days from the 1st April). But it would be ok if they did this:

Week commencing: 1st April 2007
First Day: 8th April 2007

Help???

thanks!

Maria

 

[RuralGuy]

Hi Maria,
You posted this in the tables area but this type of validation belongs in the BeforeUpdate event of the control on your form. I would also recomment using a popup calendar to enter your dates. MVP Jeff Conrad has a ton of links on his web site. http://www.accessmvp.com/JConrad/accessjunkie/calendars.html

 

[mariaw]

Validation rule

Hi

Thanks for this, but even with a pop-up calendar I am not convinced people will get it right....

How do I "write" the validation rule please?

thanks

Maria

 

[KeithG]

Are the users entering the data via form? If so you need to use the controls before update event

 

[RuralGuy]

I'm a little confused because you say that April 8, 2007 is in the week commencing on April 1, 2007 but they are both a Sunday! How about letting the user fill in the Sick Day and you calculate the Week Commencing for them? Do your weeks start on Sunday or Monday?

 

[Michael J Ross]

Hi Maria

Maria,

Take a look at form1 in this mdb and the code in the Before Update event of the second text box, is this what you want to happen?

I have used the DateDiff function to work out the difference in days and pop up a message if over 7 days, enter 2 dates and press tab and you should see a message appearing if over 7 days difference.

 

[mariaw]

Validation Rule

I'm a little confused because you say that April 8, 2007 is in the week commencing on April 1, 2007 but they are both a Sunday! How about letting the user fill in the Sick Day and you calculate the Week Commencing for them? Do your weeks start on Sunday or Monday?

Hi

Our weeks start on a Sunday, so sorry, the 1st to the 7th April 2007 inclusive will be the week commencing 1st April

How would I get it to calculate the week commencing for them?

Thanks

Maria

 

[mariaw]

For Michael J Ross

Sorry but it says the database zip file is corrupted; Also, I am on Microsoft Access 97?

thanks

Maria

 

[RuralGuy]

Maybe this function will help:

Public Function ToSunday(StartDate As Date) As Date
'-- convert the StartDate to the previous Sunday's Date
ToSunday = StartDate - (DatePart("w", StartDate, vbSunday) - 1)

End Function

 

[Michael J Ross]

Maria,

Access 97 version attached for you, hopefully not corrupted (although the other was ok for me)

Rural Guy's looks the better solution.