Looking for code to eliminate Nul results

[Jamescdawson]

I'm looking for a little help on producing a report which excludes NUL results.



I have Drivers out on the road 6 days/week. I've generated a report which lists each client and which driver attends on which-ever day of the week. (sometimes the clients gets more than one call per week. The drivers name appears in a column in the report under the day of the week he attends the customer. There's a load of customers that don't get a call on any day of the week and I'd like to exclude these records from the report.

So my report looks like this

Customer City/Town PostCode Monday Tuesday Wednesday Thursday Friday Sunday
Brad Pitt Stafford ST19 5PL David
D Beckham Newport NP22 4TR Mike
Johhny Depp Blackwood GH78 9YT Garry Peter
Mike Douglas Bristol BR78 3ER
Helen Mirren London SW12 5TG Ken


I want to eliminate all the records which do not have a driver name in any of the day columns. (in my example above, I'd like to remove Mike Douglas from the report.

Can anyone point me in the right direction. I've tried nested IIFs in a query but I can't make it work - then again, I might not be doing it right.

If there a slick simple solution ?

Best Regards to you all

Jimbo

[June7]

Create a field with calculation that tests each of the other fields and apply criteria to this constructed field. One expression could be:

CheckDays: Nz([Monday],Nz([Tuesday],Nz([Wednesday],Nz([Thursday],Nz([Friday],[Sunday])))))

No Saturday?

Criteria would be: Not Is Null

Assumes no empty strings permitted in fields.

[Jamescdawson]

June7

I'll give a go. Thanks.

drivers don't work on Saturday

I'm fairly new to Access.... Just some guidance on the syntax... Not is Null .. Is this what I put in the criteria in the query ?

[June7]

Yes, that is the exact criteria.

[Jamescdawson]

June7,

I've been trying to maek tyour suggestion work.. No luck...

This is what I've put in the expression builder....

CheckDays: Nz([DriverMonday],Nz([DriverTuesday],Nz([DriverWednesday],Nz([DriverThursday],Nz([DriverFriday],[DriverSunday])))))

Noted that "Nz" missing before Sunday... and only 5 parenthasis at the end ?

Is this nearer the mark ?

CheckDays: Nz([DriverMonday],Nz([DriverTuesday],Nz([DriverWednesday],Nz([DriverThursday],Nz([DriverFriday],Nz[DriverSunday]))))))

BTW: This doesn't work either.... I'm advised that I have a dot or ! where it shouldn't be !

=Jimbo=

[June7]

Parens must be paired. Nz has argument for alternate value. My structure is correct. I did test it. If the first 5 are null, then DriverSunday value should return.

Your structure should be:
CheckDays: Nz([DriverMonday],Nz([DriverTuesday],Nz([DriverWednesday],Nz([DriverThursday],Nz([DriverFriday],Nz([DriverSunday],"None"))))))

Then criteria would be <> "None"

Neither will work if your table has empty strings in the fields. That will mean a more complex conditional expression.

Show the complete SQL statement of the query.

[Jamescdawson]

June7

Thanks for you assistance. Got it in the end

Jimbo