Results 1 to 5 of 5
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    A Function Question (A2K SR1)

    Can you calculate the count of an item in a Function?

    How would I put the following into a function? The query returns a number?

    SELECT Count(*) AS cnt
    FROM MasterDataTable
    GROUP BY MasterDataTable.Session, MasterDataTable.Career
    HAVING (((MasterDataTable.Session)=[Forms]![MasterDataTable]![Session]) AND ((MasterDataTable.Career)=[Forms]![MasterDataTable]![Career]));
    Regards,

    Gary
    (It's been a while!)

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Function Question (A2K SR1)

    air code

    public function TheCount() as Long
    dim ssql as string
    dim rs as dao.recordset

    ssql = blah, blah, blah
    set rs = currentdb.openrecordset(ssql)

    thecount = rs!cnt
    end function
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A Function Question (A2K SR1)

    To get the number of records in a recordset, you need to use RecordCount, not Cnt. Also, when you open a DAO recordset, the RecordCount property is not accurate - it is 0 if the recordset is empty, non-zero (usually 1) if the recordset is non-empty. To get an accurate count, you must move to the last record. So you must modify the next to last line in the previous post as follows:

    If rs.EOF Then
    TheCount = 0
    Else
    rs.MoveLast
    TheCount = rs.RecordCount
    End If

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: A Function Question (A2K SR1)

    I will give this a try. In my prior attempts to do this, I get an error message indicating that the SQL statement is invalid.
    Regards,

    Gary
    (It's been a while!)

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Function Question (A2K SR1)

    A technique that I find useful is to define several variables in the function: e.g

    dim sSQL as string
    dim vParameter as variant

    etc

    Then retrieve the values from the form into the variables and build the sql statement from those variables.

    vParameter = forms!......
    ssql = "SELECT * from ..... WHERE ID = " & vParameter .....


    Finally, execute the sql statement. The advantage of this technique is you can pause the execution of the function when the sql statement is fully built, get its current value in the immediate window, and paste it into the query window. Any error in your syntax will become obvious in the query window.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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