Calculation query in Access produces null result all the time

I am trying to create a community database in Access (2003 preferably). Part of the main table has several fields relating to personal relationships, with field names such as "Partner", Child_LT_16", Child_GT_16", "Parent", "Sibling" etc. On the entry form each of these fieldu can have a number entered for the number of each kind of relation. Each field is defined as "Integer". The entry and normal display via a form is OK for this basic operation. However I now want to have an additional field to be ised on Forms and Reports that show the total number of relations for each record.

I have constructed a query, with a calculated field of the form

Total_Relations:[Partner]+[Child_LT_16]+[Child_GT_16]+[Parent]+[Sibling]

This query doesn't throw any errors and runs without problems, BUT it doesn't return any value for any record. In all cases the Total_Relations column stays blank (empty).

It is as though the query engine is seeing all the field values as EITHER empty (some are empty others have values usually between 1 and 3, virtually all have a numeriacl value somewhere in the various relations fields) OR taking the field value as text and giving it a NULL value (but the fields are defined as开发者_开发技巧 Integers.

I have tried various things including using VAL([Partner]) etc. in case the fields were being taken as Text. In this case any record that had any NULL field threw-up an error message, only records where all relevent fiels had numeric values did not throw the error, but they still did't show a total (or any) value either.

I have tried recreating the query gain, from scratch, several time, but with the same result each time

I have done this type of calculation loads of time before without problem, but this one has me climbing upm the walls.

Any suggestions from anyone?

PLEASE............

In MS Access, if you add two (or more) values together and any one of them is null, then the result is null.

You need to check is each value null, or make the fields not null-able, so there will be zero values in there if nothing is entered.

Have a look at this for working with Nulls in MSAccess, Tip #5 is the one you want