How do I make a select query return even if some values are null? 2

[n2jesus]

Hi all,

I've got a big select statement that is filling a form in access 2000. The problem is, not all of the fields in the database have to be filled in. What is happening is that when one or more fields in the database are null, the whole statement fails and nothing is returned.

How do I make the statement return whatever it finds? My only search criteria is an ID number, but if any field that is to be returned is null, the whole thing fails.

Thanks for any help,

Jim

 

[dhaveedh]

There is an access function that allows you to return a value you specify if the value of the field you are returning is null;

It is Nz(YourValue, ValueIfNull)

So if you want the IDs to return a value if null you should have
Nz(ID, 0)

Or you can try using the IIF function also

IIF(IsNull(YourValue), theValueShouldBeIfNull, ElseMyValue)

IIF - Immediate If

The statement means if my value is null, then it should return "theValueShouldBeIfNull" otherwise it should return another value.

This should help you

 

[n2jesus]

I'm aware of NZ(). Let me clarify what is going on.

say I have a table of fields A, B, C, and D called tbl.

If I select * from tbl where A = "Hi"; even though I find matches for Hi, if B, C or D are empty, the query returns nothing.

It's like if any part of the query finds nothing, the whole query fails.

 

[TonyJollans]

Hi n2jesus,

There must be more to it than that. You say it's a big Select statement - have you got Joins on any of the possibly Null fields? What about posting the full SQL to give us a fighting chance.

Enjoy,
Tony

 

[n2jesus]

Here ya go...

SELECT DISTINCT employ_info.employee_name, call_records.extension, [inbound per extension].SumOfduration, [inbound per extension].AvgOfduration, [inbound per extension].MaxOfduration, [outbound per extension].CountOfduration, [outbound per extension].SumOfduration, [outbound per extension].AvgOfduration, [outbound per extension].MaxOfduration, [totals per extension].CountOfduration, [totals per extension].SumOfduration, [totals per extension].AvgOfduration, [totals per extension].MaxOfduration, [inbound per extension].CountOfduration, [outbound minute per extension].CountOfduration, [outbound minute per extension].SumOfduration
FROM [outbound minute per extension], (((call_records INNER JOIN employ_info ON call_records.extension = employ_info.extension) INNER JOIN [outbound per extension] ON employ_info.employee_name = [outbound per extension].employee_name) INNER JOIN [totals per extension] ON employ_info.employee_name = [totals per extension].employee_name) INNER JOIN [inbound per extension] ON employ_info.employee_name = [inbound per extension].employee_name
GROUP BY employ_info.employee_name, call_records.extension, [inbound per extension].SumOfduration, [inbound per extension].AvgOfduration, [inbound per extension].MaxOfduration, [outbound per extension].CountOfduration, [outbound per extension].SumOfduration, [outbound per extension].AvgOfduration, [outbound per extension].MaxOfduration, [totals per extension].CountOfduration, [totals per extension].SumOfduration, [totals per extension].AvgOfduration, [totals per extension].MaxOfduration, [inbound per extension].CountOfduration, [outbound minute per extension].CountOfduration, [outbound minute per extension].SumOfduration;


Yes, it's full of joins.

 

[cchristi]

Have you tried putting the following in your critera:

Is Null Or "*" ?

 

[TonyJollans]

Hi n2jesus,

Many, many apologies. I have just been prompted to look at this after cchristi's post and I realise I started looking at your SQL and I didn't reply.

Your SQL is a little unusual in that table [outbound minute per extension] is not joined to anything in the query, but there is nothing else particularly odd that I can see. Assuming that records exist for the names and that [outbound minute per extension] is not empty you should get data. Which fields might be null?

Please come back if you still have a problem?

Enjoy,
Tony

 

[n2jesus]

what this database does is log phone calls from our phone switch. most of the time when it returns nothings is when an employee has no inbound calls. some of our people mostly make outbound calls. when I run this query on a day that they have no inbound calls, it fails and returns nothing.

thanks for replying,

jim

 

[TonyJollans]

Hi Jim,

So if they have no inbound calls there won't be a record on the inbound per extension table? If so, because you have an INNER join, no record will be returned for that person; the problem isn't really missing fields but missing records.

You probably need to use OUTER joins so that what records there are will still be returned.

Enjoy,
Tony

 

[cchristi]

Hi n2jesus,

Here is some code that works.

SELECT CROSSCHECK_RESULT.COCODE, CROSSCHECK_RESULT.RESULT_TYPE, VALIDATION_RULE.DESCRIPTION
FROM VALIDATION_RULE INNER JOIN CROSSCHECK_RESULT ON (VALIDATION_RULE.VALIDATION_RULE_ID = CROSSCHECK_RESULT.VALIDATION_RULE_ID) AND (VALIDATION_RULE.YEAR = CROSSCHECK_RESULT.YEAR)
WHERE (((CROSSCHECK_RESULT.COCODE)=20486) AND ((CROSSCHECK_RESULT.RESULT_TYPE)='C') AND ((VALIDATION_RULE.DESCRIPTION) Like "Risk" & "*&quot AND ((CROSSCHECK_RESULT.QUARTER) Is Null Or (CROSSCHECK_RESULT.QUARTER) Like "*&quot AND ((CROSSCHECK_RESULT.RESULT_STATUS_ID)=3 Or (CROSSCHECK_RESULT.RESULT_STATUS_ID)=4));

Hope this helps.

 

[n2jesus]

thanks for all your help. I think I missed the outer join day in class. ;-} I'll try that.