Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2008
    Posts
    4

    Unanswered: Calculated Control to display counted rows of 0 OR > 0

    Ok, here is what I have. A Query that prompts the user for search criteria. Form displays the the result in 'Continous Form' result (in other words, rows). I want a control in the Form Header to display the number of rows returned. No problem =Count(*) - That does a fine job IF there are rows returned. BUT if nothing is returned I want to display 0 (zero) in the text box calculated control. I have tried...
    =IIf(IsNull(Count([Movie_Title])),0,NZ(Count([Movie_Title])))
    =IIf(IsNull([Movie_Title]),"0",Count([Movie_Title]))

    I know most of the Access gurus will slap a expression out is about .5 seconds that will work but me being a complete noob to Access I need all the help I can get right now . I'm at a loss and need some help. Thanks in advance...

    Tazzin
    Last edited by Tazzin; 01-10-08 at 23:18.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Tried

    =NZ(Count(*),0)

    or similar?

    Maybe

    =IIf(IsNull(Count(*)),0,Count(*))

    HTH
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2008
    Posts
    4
    Well I tried both of those you replied with, although the expressions was accepted in the Source Control box of the properties, it still displays <null> in the text box for form. Am I, by chance, missing a different property setting on the control to 'allow' zero to display? Just trying to think of what else might cause <null> and not 0. Thx again for your help.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    What about trying a custom format for the text box's Format property?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jan 2008
    Posts
    4
    What I have tried every number format that was not a date and still no 0 . , I'm still researching though. I'm sure there is a way to code it but I don't know VBA (unfortunately). Again, thanks for the suggestion. StarTrekker.

  6. #6
    Join Date
    Jan 2008
    Posts
    4
    OK, after some digging around and asking around I finally got something to work. I had to enter the Form Properties/Event tab/On Load... Select Code Builder and enter the following code linked to the textbox..
    Private Sub Form_Load()

    With Me.RecordsetClone
    '(checks to see if there are records or not – if there are records move the form to the last record to get the complete count or if no records give a value of 0)
    If .RecordCount > 0 Then
    .MoveLast
    TotalSearchResults.Value = .RecordCount
    Else
    TotalSearchResults.Value = 0
    End If
    End With
    End Sub


    Of, course I had to have a VBA guy who works with access to tell me how to get to the location to enter the code 'correctly' . But any how, I hope others find this as helpful as I did.

Posting Permissions

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