Problem with null/blank/zeros

[chartee]

I have a query which basically says<br><br>if end date &lt;= a date and &gt;= a date then subtract the start date from the end date and the start time from the end time to calculate the days and fractional days in that particular year. The false part of the statement needs to be no value in the cell.<br><br>The query works if I put the false value = 0.&nbsp;&nbsp;If I use &quot; &quot; or null for the value, I get an error when I call the report that was written based on the query.&nbsp;&nbsp;I don't want the zeros in the report because it makes it too busy and too difficult to read.&nbsp;&nbsp;How do I get rid of them?<br><br>

 

[JimHorton]

What field is the 'false part' a criteria for?&nbsp;&nbsp;It's not clear from your message...You have a date field where you select the range---that looks fine, but where are you using the '0' as criteria/&nbsp;&nbsp;Make sure that the data type of that field is boolean or at least numeric.&nbsp;&nbsp;Using ' ' as criteria will cause error, since that's a string, but if you use IS NULL (instead of &quot;= Null&quot you should be ok with any type.<br>--Jim

 

[chartee]

The false part is criteria for the same field as the date.&nbsp;&nbsp;I am trying to put activity into columns by year. The field is a number field with 4 decimals.&nbsp;&nbsp;When I used Null or &quot; &quot;, I could not change the field properties to standard number with 4 decimals.&nbsp;&nbsp;The format properties changed to standard. I tried putting IS NULL where the 0 is at the end of the statement, but that didn't work....I got a syntax error, probably because I didn't put it in correctly.<br><br><br>1993: IIf([calcdaysqry]![End Date]&lt;=#12/31/93#,+[calcdaysqry]![End Date]-[calcdaysqry]![Start Date]+[calcdaysqry]![End Time]-[calcdaysqry]![Start Time],0)<br><br>

 

[JimHorton]

chartee,<br>First, it looks like you have an extra + in the syntax, just after the #12/31/93#,<br>Then, you can just leave the last arg in the iif totally blank--just have the closing paren follow the comma--that will be null as the result.&nbsp;&nbsp;<br>--Jim

 

[chartee]

That worked.&nbsp;&nbsp;Thank you very much.&nbsp;&nbsp;It really improves the look of my report to not have the extraneous zeros all over it!&nbsp;&nbsp;I am so glad that I found this website and this forum!