Results 1 to 2 of 2
Thread: Return Zero (Office 2003)
2004-10-06, 01:19 #1
- Join Date
- Jun 2004
- Thanked 0 Times in 0 Posts
Return Zero (Office 2003)
I'm wanting my query to return Zero if not found.
My first field, which is a date field. ...... Last_Calibration_Date
criteria ....Is Not Null And Between [Forms]![frArea Select]![Text25] And [Forms]![frArea Select]![Text27]
Text25 is 1/1/2004 and text27 is 12/31/2004
My second field is a quarter field ...Quarter: DatePart("q",[Last_Calibration_Date])
criteria .... like"4"
This return all last calibration dates that fall into the year and quarter.
If there isn't any last calibration dates for Dec I still need it to return a Zero for my bar chart update.
2004-10-06, 06:07 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 Posts
Re: Return Zero (Office 2003)
The "Is Not Null And " part in the criteria for Last_Calibration_Date is superfluous. Null values do not satisfy "Between ... And ...", so you don't need an extra check for "Is Not Null".
To restrict the dates to the 4th quarter, set the criteria for Quarter to 4 instead of Like "4". Like is meant for wildcard comparisons, with ? or * in the comparison value. You don't need quotes around 4, because DatePart returns a numeric value.
You haven't told us what the query returns, but I assume your query is a Totals query that calculates the sum of a field. If so, you can use a calculated column like this:
With the correct field name substituted. The Total option for this column should be set to Expression.