Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Posts
    13
    Thanks
    0
    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.
    thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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:

    Nz(Sum([NameOfField]),0)

    With the correct field name substituted. The Total option for this column should be set to Expression.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •