Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004

    Unanswered: Show Record Count in Form Footer or Zero if No Records

    I have a subform, which is a continuous form.
    In the footer I am counting the number of records, as well as the number of records matching a certain criteria.

    [DetailID] is the primary key. - [Status] is a field that may be "Scheduled", "cancelled" or "Billed"

    I have the control source of the textboxes set as follows:

    total jobs =Count([DetailID])
    scheduled =Sum(IIf([Status]='Scheduled',1,0))
    cancelled =Sum(IIf([Status]='Cancelled',1,0))
    billed =Sum(IIf([Status]='Billed',1,0))

    I am using a calendar control to select records scheduled on a given date.

    My calculations in the form footer work perfect - as long as there are records in the subform. When there are no records for a date,the textboxes in the footer are all blank...

    What I would like to do is show a 0 in all boxes when no records are in the subform.

    I tried NZ and it will not work

    Any ideas are appreciated.

    Thanks - AB

  2. #2
    Join Date
    Apr 2006
    Huddersfield, UK


    Just a thought, have you tried setting default value to 0? Your formula still will work as the control source, and this should display a 0 when the formula isn't run?

    Maybe worth a try?

    sometimes simple is best.... and i'm just a simple fellow.

  3. #3
    Join Date
    Jan 2004
    Norwich UK
    Have you tried an IIF? You could replace the Nulls with "0"

Posting Permissions

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