would you mind posting a generic set of results, say, two records?
Use the
[CODE/] button to enclose your table and spaces to format the columns (not the tab key, the tabs won't stay :) )
My first thought here is a
UNION
clause pulling
DISTINCT 0,null,null
; however, I'm not sure of the formatting and what you would most likely see in the query for a zero return is
- [CountOfRejection][Department][ValueStream]
-
[ 0 ][ ? ][ ? ]
Which is not what I suspect you are after.
The vba function
NZ()
in the SQL can slow things down on large records... you might consider:
iif([Forms]![fKaizenTurns]![tStartDate] Is Null,#4/1/2015#,[Forms]![fKaizenTurns]![tStartDate])
another consideration is the use of the
Between
, you can miss some data depending how the data set is entered. you should consider using the inequality:
>=[startdate] < [enddate]+1[
This has cause me some grief in the past because our lab is a 7d/24h operation and the lab results have a date+time stamp and the form I was using at the time to pull reports didn't take that into consideration... really, one would think that the
Between
would pull from midnight to midnight for the indicated dates :( . (MVP Allen Browne shows an example of such a construct in the first half here:
Limiting a Report to a Date Range I don't see any reason to use the huge function in the second 1/2 of the post for your query.)