Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Apr 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Entering Data based on Criteria list (Access 2000)

    Can anyone tell me if I can do this? Let's say I have a form with two fields: score and grade. I want to fill in the grade field based on data in the score field, for example if I enter a value between 90 and 100 in the score field, "A" would automatically be entered in the grade field. If I enter a value between 80 and 89 in the score field, "B" would automatically be entered in the grade field etc. Thanks for any advice.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Entering Data based on Criteria list (Access 2000)

    Although it is possible to do this using a complicated expression in the control source of Grade, it is probably easier to use a bit of VBA here.
    <UL><LI>Click on Modules in the database window.
    <LI>Click New
    <LI>Type or paste the following function into the module window. Of course, you can adapt it if you use a different scoring system than the one I used as example

    Function GradeFromScore(iScore)
    ' Treat null (empty) values apart from the rest
    If IsNull(iScore) Then
    GradeFromScore = Null
    Exit Function
    End If

    Select Case iScore
    Case Is >= 90
    GradeFromScore = "A"
    Case 80 To 89
    GradeFromScore = "B"
    Case 65 To 79
    GradeFromScore = "C"
    Case 50 To 64
    GradeFromScore = "D"
    Case Else
    GradeFromScore = "F"
    End Select
    End Function

    <LI>Save the module (name it basGrade or something like that)
    <LI>Close the Visual Basic Editor
    <LI>In the Record Source of the grade text box, enter the following expression

    =GradeFromScore([Score])

    where Score is the name of the score text box.[/list]HTH

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Entering Data based on Criteria list (Access 2000)

    If these gradings are static then Hans solution is cool.

    If not, then you could setup a table of gradings tblGradings which would have the following fields:
    LowScore
    HiScore
    Grading

    You could then use a Dlookup to get the grading:
    txtGrading = Dlookup("Grading", "tblGrading", iScore & " Between LoScore AND HiScore")

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •