Calculating with Null Fields in a Query

[gdougher]

My training database requires me to identify each training record in the tblEmpTrainHist table as either "Compliant" or "Delinquent". I thought a simple calculation in my query:
RecordStatus: IIF([DateReceived]<=[DateDue],"Compliant","Delinquent") would do the trick. However, I did not consider the records where the employee has not yet completed the training and the field [DateReceived] is Null. There are two considerations: those employees who have no [DateReceived], but have not yet reached the DateDue (Compliant); and those employees who have exceeded the DateDue (Delinquent). I assume I am missing something very basic here and would appreciate your instruction.

 

[MangoFruit]

I think I have a very similar problem so I hope you don't mind if I explain mine in the same topic. I have a database that keeps track outgoing money per category, incoming money per category, and budget per category per year.
I want to calculate remaining budget by calculating budget - (sum of outgoing money - sum of incoming money) for each year and category. This works perfectly, except when there has been no incoming money for a particular category and year. Then I get no results because it can't be calculated. The only solution I can think of is to enter a record with 0 incoming money for each year and category, but that's not really a good solution at all in my opinion.

 

[Frothingslosh]

Well, in the queries, you can use Is Null and Is Not Null as conditions in the query builder (or in the WHERE clause if you're working directly with SQL) to limit the records that get pulled. For example, for the Compliant dates, you could use this query:

SELECT TableName.* FROM TableName WHERE TableName.DateReceived Is Not Null AND TableName.DateRecieved<=TableName.DueDate;

For non-complient, you do similar, but pull both records with DateReceived greater than DueDate, and all records with DateReceived being Null and DueDate being < today.

 

[MangoFruit]

But wouldn't that only work for missing values in a field, and not for 'missing' records? Or am I mistaken?

 

[MangoFruit]

I haven't had time to really look into it yet, but I just found out there's a ' WHERE EXISTS' function in access that I didn't know about. I think this may hold the key to solving our problems, but like I said I haven't really looked into it yet.

 

[Frothingslosh]

WHERE EXISTS involves running a subquery.

So are you saying that you want to pull all the records into one recordset and then display a field showing or determining if each is compliant or non-compliant? In that case, I'd recommend creating and calling a function that returns the two results. You can call the function either in a query or an unbound field in a form or report.

It's POSSIBLE to do the determination inside a query, but you're going to be talking about a fairly complex set of stacked IIF functions.