Format of field produces error in query calculation

[ptaylor-west]

I have a query that add up the numeric values in a value list assinged in a combo box in response to each question, the row source for question 15 for exmaple is as follows:

ROW SOURCE: 0;"I have no idea";5;"I indicated that I wouldn’t have time today";0;"Was mentioned early on and then not offered again";5;"The salesperson said the vehicle wasn’t available"

I then run a query that adds together the responses to 3 questions, including question 15, the field in the query appearing as follows:

LS5: ([q14]+[q15]+[q16])

It was working fine but has stopped working, the fault lies with q15, if I take it out it works again. So I looked at the table as I am sure it must be the way it is set up, why it worked before I don't know and I attach a screen shot of how the field is set up in the table, which is no different to q14 and q16.

Anyone got any ideas?

One last thing is that it makes no difference if the fileds contain a number (including zero) or are blank

 

[ptaylor-west]

Thanks for the guidance - I have also found out that the current problem lies in the query, I previously said it didn't matter if the data was a number (including zero) or blank and this is incorrect, simply if any of fields for q14, 15 & 16 are left blank then the query will not add them up - I cannot set the default value to zero as this has a value in itself for one of the questions responses.

However it appears that the Nz function would resolve the problem, I just cannot see how to apply it to the query I posted. Advice would be appreciated.

 

[ptaylor-west]

Found the solution, it was the Nz function that was required, the way to apply it to the query is as follows:

LS5: (Nz([q14],0))+(Nz([q15],0))+(Nz([q16],0))

The only problem I have is that it returns a zero if there are no values to any of the 3 questions and as I said before zero does have a value - is there anything that I can include in the equation that would leave the field blank if there are no entries at all?

 

[ptaylor-west]

Thanks Pat, that has resolved the problem - the problem with the structure of the database is that it has been added to over a period of time - peoples wish lists etc but I agree it does need sorting out.

I did try using sum but couldn't get it to work.

Thanks for your solution.

 

[ptaylor-west]

Thanks Pat, your help as usual is invaluable.