# Thread: Calculations in Forms ACCESS 2003

1. Registered User
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?

2. Moderator
Join Date
Dec 2004
Location
Posts
3,928
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.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Originally Posted by JennyD
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?

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.

4. Registered User
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
•