Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    9

    Unanswered: Calculations in Forms ACCESS 2003

    We use our database to enter in data from a research study. Certain questionnaires that subjects fill out have totals that need to calculated and entered. (I.e. someone's total score on a Depression inventory form). Anyway, I know how to make it so the individual items automatically add up and total in the "total field" on the form, however a lot of times subjects may miss an item and so we have missing data. To code for a missing value we enter in "99". (and we know that it wasn;t a data entry error).

    Is there anway to code it so that if we enter in 99, it will not get calcualated into the final total?

    Thanks for your help in advance!

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you're not summing on the same field as the data entry, I might add in an expression into the query something like:

    MyExpression: iif([Myfield]=99,0,[MyField])

    Otherwise, there may be an equation you could use to somehow work it so that it divides/multiples/etc.. in the summing to account for the 99 values. (which someone more advanced with these type of equations could probably advise you on.)

    If you need to do it on the data form itself, you could also (although others would probably frown at this method) but possibly have another field which when 99 is entered on the data form, puts in 0 to this other field, otherwise the actual value and then sum on this field.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by JennyD View Post
    We use our database to enter in data from a research study. Certain questionnaires that subjects fill out have totals that need to calculated and entered. (I.e. someone's total score on a Depression inventory form). Anyway, I know how to make it so the individual items automatically add up and total in the "total field" on the form, however a lot of times subjects may miss an item and so we have missing data. To code for a missing value we enter in "99". (and we know that it wasn;t a data entry error).

    Is there anway to code it so that if we enter in 99, it will not get calcualated into the final total?

    Thanks for your help in advance!
    well one way would be to use NULL in place of 99, after all that is one of the reasons NULL was invented/implemented

    another is to use a clunky IF statement
    depending on how you designed your db that should be easy enough to implement, unless of course you designed your table to have 100 columns.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2009
    Posts
    9
    thanks so much for everyone's help.

    Since I am definately no expert coder/programmer, the "Null" idea seems like the best thing to do for now!

    in order for the fields to calculate the "data type" for each field is set to "Number." So do I create another column in the drop down box of the field that has "null", or do I change the "data type" and will it still be able to calculate?"

    If I were to go the Code route, what I use to calculate is an "After Update" SetValue Macro in each field.

    I.e.:

    Item: [Forms]![MyForm]![Text61]
    Expression: [Field1]+[Field2]+[Field3]+[Field4]+[Field5]+[Field6]+[lField7]+[Field8]+[Field9]

    Where would the 1f/ then statements fall into place??

Posting Permissions

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