ignore -99 in query

[BUmed]

Hi all,
This should be easy for oh so wise ones. So here it is I'm trying to add up some data in a query which is no big deal. But in this data base some N/A data is represented by -99. I would like to ignore all -99 when adding up this data.

Example
Expr1: [csbQ01]+[csbQ02]+[csbQ03]+[csbQ04]+[csbQ05]+[csbQ06]

Thanx for the help

[jeffstl]

Hi all,
This should be easy for oh so wise ones. So here it is I'm trying to add up some data in a query which is no big deal. But in this data base some N/A data is represented by -99. I would like to ignore all -99 when adding up this data.

Example
Expr1: [csbQ01]+[csbQ02]+[csbQ03]+[csbQ04]+[csbQ05]+[csbQ06]

Thanx for the help

You need to have criteria that says WHERE csbQ04 <> -99

So in the criteria <> -99 or "-99" if its a string in the table.

Is this what you mean?

You could do an SQL View on your query and post the SQL here and I can modify it to exclude -99 if this doesnt help you.

[BUmed]

Yes this is what I want thank you. Here is the code and I have bold the areas that have -99

SELECT [Telephone Screen].famID, [Telephone Screen].infantID, [Telephone Screen].prbandID, [Telephone Screen].infID, [Telephone Screen].mID, [Telephone Screen].fID, [Telephone Screen].studygrp, [Telephone Screen].ifFName, [Telephone Screen].ifLName, [Telephone Screen].mFName, [Telephone Screen].mLName, [Telephone Screen].fFName, [Telephone Screen].fLName, [Telephone Screen].prFName, [Telephone Screen].prLName, [Telephone Screen].ifdob, [Telephone Screen].prdob, [PB CSBS Caregiver Questionnaire Items 1-41 - 06M].*, [PB CSBS Caregiver Questionnaire Items 42-45 - 06M].*, [csbQ06]+[csbQ05] AS Expr2
FROM ([Telephone Screen] LEFT JOIN [PB CSBS Caregiver Questionnaire Items 1-41 - 06M] ON [Telephone Screen].famID = [PB CSBS Caregiver Questionnaire Items 1-41 - 06M].famID) LEFT JOIN [PB CSBS Caregiver Questionnaire Items 42-45 - 06M] ON [PB CSBS Caregiver Questionnaire Items 1-41 - 06M].famID = [PB CSBS Caregiver Questionnaire Items 42-45 - 06M].famID
ORDER BY [Telephone Screen].famID;

[jeffstl]

Hmm. Not sure if this will be right but try it out. The only reason it wouldnt work is if you need to specify the table that csbQ05 and csbQ06 come from like MyTable.csbQ05.

SELECT [Telephone Screen].famID, [Telephone Screen].infantID, [Telephone Screen].prbandID, [Telephone Screen].infID, [Telephone Screen].mID, [Telephone Screen].fID, [Telephone Screen].studygrp, [Telephone Screen].ifFName, [Telephone Screen].ifLName, [Telephone Screen].mFName, [Telephone Screen].mLName, [Telephone Screen].fFName, [Telephone Screen].fLName, [Telephone Screen].prFName, [Telephone Screen].prLName, [Telephone Screen].ifdob, [Telephone Screen].prdob, [PB CSBS Caregiver Questionnaire Items 1-41 - 06M].*, [PB CSBS Caregiver Questionnaire Items 42-45 - 06M].*, [csbQ06]+[csbQ05] AS Expr2
FROM ([Telephone Screen] LEFT JOIN [PB CSBS Caregiver Questionnaire Items 1-41 - 06M] ON [Telephone Screen].famID = [PB CSBS Caregiver Questionnaire Items 1-41 - 06M].famID) LEFT JOIN [PB CSBS Caregiver Questionnaire Items 42-45 - 06M] ON [PB CSBS Caregiver Questionnaire Items 1-41 - 06M].famID = [PB CSBS Caregiver Questionnaire Items 42-45 - 06M].famID
WHERE csbQ06 <> -99 AND csbQ05 <> -99 ORDER BY [Telephone Screen].famID;
 

[BUmed]

Is there a was to add or null to this statement. Because some of the data is missing. Thanks for the help.
WHERE csbQ06 <> -99 AND csbQ05 <> -99

[jeffstl]

 
WHERE csbQ06 <> -99 AND csbQ05 <> -99 AND csbQ06 NOT NULL AND csbQ05 NOT NULL ORDER BY [Telephone Screen].famID;
 

I believe that will do it.

[BUmed]

Thanks so much worked like a charm