If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Validation Rules
Hi...need help again....
Access 97 database. I have a form for associate work hours. As associates work a trailer, they put down their name, start date, start time, end date and end time. All date fields are defined uning "Short Date". Time fields are in the 24 hour format. The fields are then calculated to show the amount of time it took for the associate to work the trailer. Unfortunately, associates on the floor do not always use 24 hour times when they are filling out their sheet. If the data entry clerk in the office does not catch this and convert the time, many times this will lead to negative work times. I used the following validation rule in the Start Date field to ensure that they were at least getting the right year: =#1/1/05# And #1/1/06# I would like to be able to use a validation rule in the End Date field that will show an error message if the date typed in is prior to the date typed in the Start Date field. I would also like to be able to use a validation rule for the Start Time and End Time field to do the same thing. Any help will be appreciated. Linda |
#2
|
|||
|
|||
Validation Rules
On Fri, 18 Nov 2005 09:09:16 -0800, Boz
wrote: I would like to be able to use a validation rule in the End Date field that will show an error message if the date typed in is prior to the date typed in the Start Date field. I would also like to be able to use a validation rule for the Start Time and End Time field to do the same thing. You can't use field validation rules in a Table to do this, since validation rules cannot refer to other table fields. I'd suggest using the Form's BeforeUpdate event instead: Private Sub Form_BeforeUpdate(Cancel as Integer) If Me![Start Date] Me![End Date] Then Cancel = True MsgBox("You can't end days before you start!", vbOKOnly) Me![End Date].SetFocus Exit Sub End If If Me![Start Time] Me![End Time] Then Cancel = True MsgBox("You can't end before you start!", vbOKOnly) Me![End Time].SetFocus End If ENd Sub If you're doing data entry directly into a Table rather than a Form... well, don't. Forms are MUCH more programmable and controllable, as well as easier for the user. Note also that Access Date/Time fields can - and usually do - contain both a date and time in the same field. Do you have some critical reason not to store StartTime and EndTime as date/time fields? Doing so will let you more easily calculate worktimes across midnight, and simplify your structure. John W. Vinson[MVP] |
#3
|
|||
|
|||
Validation Rules
Boz,
This would involve a Validation Rule for the table, not for a field. In the design view of your table, select 'Properties' from the View menu. There, you can enter like this in the Validation Rule property... ([End Date]+[End Time])([Start Date]+[Start Time]) .... and then in the Validation Text property you can enter the message that you want to be displayed if the rule is broken. -- Steve Schapel, Microsoft Access MVP Boz wrote: Hi...need help again.... Access 97 database. I have a form for associate work hours. As associates work a trailer, they put down their name, start date, start time, end date and end time. All date fields are defined uning "Short Date". Time fields are in the 24 hour format. The fields are then calculated to show the amount of time it took for the associate to work the trailer. Unfortunately, associates on the floor do not always use 24 hour times when they are filling out their sheet. If the data entry clerk in the office does not catch this and convert the time, many times this will lead to negative work times. I used the following validation rule in the Start Date field to ensure that they were at least getting the right year: =#1/1/05# And #1/1/06# I would like to be able to use a validation rule in the End Date field that will show an error message if the date typed in is prior to the date typed in the Start Date field. I would also like to be able to use a validation rule for the Start Time and End Time field to do the same thing. Any help will be appreciated. Linda |
#4
|
|||
|
|||
Validation Rules
John Vinson wrote: You can't use field validation rules in a Table to do this, since validation rules cannot refer to other table fields. This is surely a misstatement. Not only can a CHECK constraint (a.k.a. Validation Rule) refer to columns in the same row, since Jet 4.0 it can refer to columns/rows in other tables. The only limitation is referring to other rows in the same table; no great surprise because, although it's in the ANSI SQL-92 Standard, none of the commercially available SQLs support it. |
#5
|
|||
|
|||
Validation Rules
You can't do this using a *field-level* validation rule, but you can do it
using a *table-level* validation rule. To create a table-level validation rule, open your table in design view, and choose Properties from the View menu. In the Validation Rule text box in the Properties window, you can enter an expression such as ... [End Date] = [Start Date] If either of the dates may be Null, you need to allow for that in the validation rule ... [End Date] = [Start Date] Or [End Date] Is Null Or [Start Date] Is Null In the Validation Text text box, enter the text you want displayed to the user when data violates the rule. BTW: You write that "date fields are defined using 'Short Date'" and "time fields are in the 24 hour format". In an MDB, Date/Time fields are stored as floating point numbers, which contain a complete date and time, regardless of how the data is displayed to the user. Here's a link to an on-line article on the subject that you may find useful ... http://support.microsoft.com/default...b;en-us;130514 -- Brendan Reynolds "Boz" wrote in message news Hi...need help again.... Access 97 database. I have a form for associate work hours. As associates work a trailer, they put down their name, start date, start time, end date and end time. All date fields are defined uning "Short Date". Time fields are in the 24 hour format. The fields are then calculated to show the amount of time it took for the associate to work the trailer. Unfortunately, associates on the floor do not always use 24 hour times when they are filling out their sheet. If the data entry clerk in the office does not catch this and convert the time, many times this will lead to negative work times. I used the following validation rule in the Start Date field to ensure that they were at least getting the right year: =#1/1/05# And #1/1/06# I would like to be able to use a validation rule in the End Date field that will show an error message if the date typed in is prior to the date typed in the Start Date field. I would also like to be able to use a validation rule for the Start Time and End Time field to do the same thing. Any help will be appreciated. Linda |
#6
|
|||
|
|||
Validation Rules
One way I have handled looking for improper entries, is to use "conditional
formatting" in the form where data is entered. You can directly compare the entered date to the date in another field, and have the color change (red is a nice flag) to indicate that the time or date may have a problem. This works pretty well for simple typos like the one you are describing. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Validation Rules | Pierre de Beer | Using Forms | 1 | October 19th, 2005 02:17 AM |
Validation Rules??? | ERROR! | Database Design | 3 | June 29th, 2005 05:03 PM |
Outlook 2003 Rules and Alerts are completely screwed up... | Ken Colasuonno | General Discussion | 5 | May 12th, 2005 12:07 AM |
Retrieving Email settings, rules, etc. | XB | Installation & Setup | 3 | May 18th, 2004 11:44 PM |