1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2003 Validation Rules

Discussion in 'Business Applications' started by jenhm, Jan 18, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. jenhm

    jenhm Thread Starter

    Joined:
    Mar 9, 2009
    Messages:
    25
    First Name:
    Jen
    I have just created a database in Access 2003 for student attendance for a school. I now have to calculate the number of hours the students have attended. If the student is absent, they have 0 hours for the day, present is 6.5, and late or left early is 3.25. I'm trying to create a validation rule in the field "Hours" to automatically populate with the correct number of hours, based on the text in the field "Presence." I've tried this:

    IIF([Presence]="Absent","0")

    But when I try to save it, I receive the error message, "Invalid SQL syntax - cannot use multiple columns in a column level CHECK constraint." I also tried creating a similar validation rule in the table properties:

    IIF([Presence]="Absent","0",[Hours])

    But I'm sure it's not correct, because nothing happened. I've also tried different variations with no results. How can I create this rule and make it work?

    Also, once I have the "Hours" field functioning, I would also need to create another field that calculates the total hours daily, but I have no idea how to do that. I'd really appreciate any and all help. Thanks!
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Jenhm, it is easier to use VBA code to what you want with the Hours.
    What type of form are you using?
    I assume by "I would also need to create another field that calculates the total hours daily, but I have no idea how to do that." that you mean the total for all the Class Students, or all Students?
     
  3. jenhm

    jenhm Thread Starter

    Joined:
    Mar 9, 2009
    Messages:
    25
    First Name:
    Jen
    I'm using a table, and it looks like this:

    Student Name ----- Class Name ----- Presence ----- Hours
    Jane Sample ----- Basic Nursing ----- Present ----- 6.5

    Ideally, I would have another column called "Total Hours," which would add the hours for each student, or be able to create a query for each student that looks up the hours and totals them. Feel free to make suggestions for the format. Thanks!
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The normal method is to use a Query to total the hours, this is done by using the Main Menu in design mode and View>Totals. You Group By Student and Sum the hours.
    Do you have a Form for data input?
     
  5. jenhm

    jenhm Thread Starter

    Joined:
    Mar 9, 2009
    Messages:
    25
    First Name:
    Jen
    That sounds easy enough. Now I just have to figure out how to make the hours column auto-populate. I don't have a form, I've just been entering everything into the table.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Data entry should be done in a form because you have much more control and Controls available on a form.
    On a form you can arrange to have the Hours auto-populate using VBA code.
     
  7. jenhm

    jenhm Thread Starter

    Joined:
    Mar 9, 2009
    Messages:
    25
    First Name:
    Jen
    OK, I've created a form out of the table. How would I go about entering the validation rule for the hours in the form?
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Open the form in Design View, click on the Presence field.
    Click on the Properties and then the Events Tab.
    Find the "After Update" event and click on it.
    Select "Event Procedure" form it's drop down list and then Click on the 3 small dots that appear on the righthand side.
    You should now see 2 Lines of code, paste the the following between those 2 lines.
    If me.Presence = "present" then me.Hours = 6.5
    If me.Presence = "Absent" then me.Hours = 0
    If me.Presence = "Early" or me.Presence = "Late" then me.Hours = 3.25

    Switch to the form view and save the form.

    note that the present has to be spelt as it is in the table including Capitals. Also that to get a number toaccept 6.5 it has to be a Single or Double precision type not the default Long Integer, Double is normally used.

    If you get stuck, post a zipped copy of your database with no data in the table and I can do this for you.
     
  9. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    Happy Early Birthday OBP. An easier option might just be to set the default value property for the Hours field to 0.
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Jim, thanks and I agree about the default value.
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/975406

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice