Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    Unanswered: Return 0 if no records to count

    Hi all,

    In the control source of a text box i have the following code.

    =Nz(Count([Line]),0)

    it displays the number of Line entries. The problem i am having is if there are no lines to count the text box is blank. Instead of being blank i would like it to display 0 but the code above does not work.

    thanks for any help
    Marcus

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    The function is DCOUNT("*",query)

  3. #3
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    thanks ranman256 for the advice. I tried Dcount but have been struggling to get it to display 0 if the number of records is zero. The subform is based off an embedded query and i only need it to count records where a certain criteria is matched. I cant understand the syntax to be honest. I've tried looking online but still confused. Nz(Count([Line]),0) does work once there is more than one record to count, ideally i need it to display 0 if there are no records to count.

    would you be able to help with the correct syntax for DCOUNT ? What information do you need?
    thanks
    Marcus

  4. #4
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    hi all,

    just following up on this problem i had. i managed to get the dcount function working..eventually :-) It now displays zero if there are no records to count.

    here is the solution which works fine.

    Me.txtRecordCount = DCount("[Line]", "Qry_Records_in_PSA", "[fkeyPSA_ID] = [Forms]![Main_Sub_Items]![txtbox_SubItems_Ref_ID]")

    thanks
    marcus

Posting Permissions

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