Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2008
    Posts
    163

    Unanswered: dcount in textbox

    how do I display the result of this SQL in a textbox?

    SELECT Count([List - Students].SID) AS CountOfSID FROM [List - Students];
    I tried
    TotalStudents = Count([List - Students].SID)
    Thanks!

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    I've never had any luck using Dxxx functions in forms and reports. I tend to get around this by writing a custom function that prompts for the same arguments, and then invokes the relevant function before returning the value. I then use the custom function in the form or report.
    For example:
    Code:
    Function CountStudents() As Integer
    
       CountStudents = Nz(DCount("[SID]", "[List - Students]"), 0)
    
    Exit Function
    In this case, there aren't any arguments in the custom function because they can be hard-coded into it. The Nz function is a precaution in casae someone has accidentally cleared down [List - Students], and prevents the function trying to assign NULL to an integer.
    To display the result in your text box, type:
    Code:
    =CountStudents()
    Hope this helps!

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'd say you should try again. Dfunctions work fine in forms and reports, but I still try my best not to use them in the detail section... especially if there are large numbers of records.

    =Nz(DCount("[SID]", "[List - Students]"), 0)

    That should work fine in the Control Source of a Textbox control.
    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

  4. #4
    Join Date
    Jun 2008
    Posts
    163
    I've never used nz before. I can imagine huge records slowing this down. Any, it got the result I needed. Thanks for the help guys! =D
    Last edited by coffeecat; 08-26-08 at 22:55.

  5. #5
    Join Date
    Jun 2008
    Posts
    163
    BTW, how do I add wildcards in criteria? another textbox is supposed to show the number of records with sectionCODE that start with "ANC"

    = Nz(DCount("[studentID]", "[SectStd]", "[sectionCODE] = 'ANC' & '*' "), 0)
    Thanks!

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    = Nz(DCount("[studentID]", "[SectStd]", "[sectionCODE] Like 'ANC' & '*' "), 0)
    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

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by coffeecat
    I've never used nz before. I can imagine huge records slowing this down...
    Yup... I try my best not to use any Dfunctions at the record level because it can really kill performance.
    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

  8. #8
    Join Date
    Jun 2008
    Posts
    163
    oh. "LIKE". Thanks again!

Posting Permissions

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