Count() and Null Values

[jpvonhemel]

Hello,

I have a simple table that includes a text field with patient complaints (fldRFV). My need is to evaluate the fldRFV field for string includes some key text, IE RASH, LESION, OR SORE, and count each record when true, grouped by the visit date, ie:

9/1/11 | 5
9/2/11 | 6


9/3/11 | 2

I have progressed to where it mostly works, but my issue is when a fldRFV record does not have any of these responses. The Count() function returns a null value and skips this date in the count. Here is my code:

SELECT tblEDPatients.fldVisitDate, Count([tblEDPatients].[fldRFV]) AS CountEDWRashRFV, "EDWRash" as SurvType
FROM tblEDPatients
WHERE (((tblEDPatients.fldRFV) Like "*RASH*" Or (tblEDPatients.fldRFV) Like "*LESION*" Or (tblEDPatients.fldRFV) Like "*SORE*"))
GROUP BY tblEDPatients.fldVisitDate, tblEDPatients.fldEDLoc
HAVING (((tblEDPatients.fldEDLoc)="EDW"))
ORDER BY tblEDPatients.fldVisitDate;

I have tried including Nz(Count([tblEDPatients].[fldRFV]),0), but it would not place a zero where null records are encountered. I am stuck and would be grateful for some ideas.

Thank you very much,

Jerold

[rpeare]

You are not picking up any null values in your fldRFV so they aren't accounted for. If you want to include all null value reasons add

or is null

to the criteria of the fldRFV along with your existing criteria for that field.

[jpvonhemel]

Rpeare,

Thanks for reading and responding. Either I don't understand or I wasn't clear in my explanation?

The field will always have a response in it, so the field will never be null. There will be records where the strings do not match my conditions. It is these times when I want it to output zero when fldRFV strings do contain any of my keywords.

Thanks,
Jerold

[rpeare]

Well again, with your criteria of:

Like "*RASH*" Or Like "*LESION*" Or Like "*SORE*"

You are only counting records with these possibilities. If you want to count all records regardless of what type but have a 0 sum if it doesn't mach these criteria you'd have to have something more like:

SELECT tblEDPatients.fldVisitDate, Sum(IIf([fldrfv] Like "*rash*" Or [fldrfv] Like "*lesion*" Or [fldrfv] Like "*sore*",1,0)) AS CountEDWRashRFV, "EDWRash" AS SurvType
FROM tblEDPatients
GROUP BY tblEDPatients.fldVisitDate, tblEDPatients.fldEDLoc
HAVING (((tblEDPatients.fldEDLoc)="EDW"))
ORDER BY tblEDPatients.fldVisitDate;

Realistically you should have a table that stores the possible values of RFV with a unique identifier (autonumber is fine) and store the unique key in the fldRFV field. Then in your table that stores the RFV values you can also store a category or categories that would eliminate the need to 'hard code' for specific reason descriptions.

[jpvonhemel]

@rpeare

Thank you very much. This looks like it is working and has saved me hours of racking my brain with trial and error. I greatly appreciate your help!

Jerold