Results 1 to 11 of 11

Thread: Counter dilema

  1. #1
    Join Date
    Mar 2004
    Posts
    118

    Red face Unanswered: Counter dilema

    I am making a report that displays a set of records that have been filtered for certain attributes.

    On this report I have multiple counters, 2 of which are causing a big problem.

    You see, one of the filters has to be on the "revision" ("p*") of a document and the report is only supposed to display a certain type, but the "total documents" counter has to display the number of records that is returned without that filter.

    So if i have 3 documents type "TQ"...
    If 2 of them are revision "p0"
    And 1 of them is revision "d0"

    The total is 3
    The total revision P is 2
    And the report must only display the 2....

    There is a way of doing this by setting Rowsource of a listbox to select count(*) from query; and setting the filter in the report not the query. Unfortunatly I dont want to display a listbox on the report.

    So does anyone know of a way to solve this? Perhaps using a listbox as invisible and somhow using the data in it (i havent found a way yet)?

    See this post for more details: http://www.dbforums.com/t993961.html

    Thanks for all your help.

  2. #2
    Join Date
    Apr 2004
    Posts
    64
    Have you tried writing a function which would call a query that returns to count of records matching the document type? You could then set the result of the function to an invisible textbox.

  3. #3
    Join Date
    Mar 2004
    Posts
    118
    not yet, I'm trying to avoid coding.

    That and im not sure how it will need to look like.

    I dont want the number itself invisible, its just that when i display a listbox with a transparent border it doesent like to be center alined and when you print the report it still prints the cell box (dont know how to get rid of that).

    If i can get the total in a texbox as raw data (ie a number) that would be ideal.

  4. #4
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404

    Counter Dilemma

    I may be getting the wrong end of this, but have you thought about setting a query based on the table that the form is base on, filtering the query so that it only allows the "p0" , then basing the report on the query?

    ;-)

  5. #5
    Join Date
    Apr 2004
    Posts
    64
    Yev

    You could use a function like this:

    Function GetDocTypeCount() As Long
    Dim qDf As QueryDef, rs As Recordset
    Set qDf = CurrentDb.QueryDefs("qCountDocType")
    qDf.Parameters("DocType") = "TQ"
    Set rs = qDf.OpenRecordset
    rs.MoveFirst
    GetDocTypeCount = rs.Fields("CountOfDocs")
    rs.Close
    Set rs = Nothing
    End Function


    The query "qCountDocType" refered to above would be something like this:
    PARAMETERS [DocType] String;
    SELECT Count(*) AS CountOfDocs
    FROM YourTableName
    WHERE (((YourTableName.YourDocTypeFieldName)=[DocType]));

    Then insert a text box in your report. Set the Control Source to "= GetDocTypeCount()"


    So basically, base your report on the query that provides the data you need, and use functions to get any extra info not readily available in the main recordsource.
    Last edited by TechnicalAli; 04-16-04 at 07:33.

  6. #6
    Join Date
    Mar 2004
    Posts
    118
    I think that almost works, expect when i open the report i get a User-defined type not defined error on the function name.

    I will try and figure out the error, but im a newbie in code so it will help a lot if you could suggest something

    Ryker: yea thats not what i meant , i did that and it does thesame thing because texboxes calculate from the report not the query, and if you set the filter on report all it does allow the query to be unfiltered.

  7. #7
    Join Date
    Apr 2004
    Posts
    64
    Yev

    Inside the query, change the datatype of the parameter from String to Text.

    That's the only thing I can think of. What version of Access are you using? It is worth checking the help to see if the data types used in the code are compliant with your version.

  8. #8
    Join Date
    Mar 2004
    Posts
    118
    I just spent an hour with an experienced access programmer, and hes just as confused as i am lol.


    Ok


    All functions are returning #error.

    Ive tried using other functions with recordset, this is just not working. I feel like im arm wrestling with Bill Gates here.

    The syntax is correct, it just doesent want to display the value in this report.

    Infact it doesent want to do anything other than manipulate the data its showing.

    The ONLY thing thats wrking is a separate SQL statement in a listbox. But when you print that it shows an indent effect around the cell, not to mention the fact that it doesent want to justify left right or center.

    Now I'm thinking of 2 solutions:

    1: more complex. is by setting the list column name to the value in the cell and then resizing the listbox to just show the column title (so it doesent print the cell border)

    2: creating 4 ceparate white text boxes and physically covering the borders.

    ......


    3: loose my sanity.

    Edit: im using 2000

  9. #9
    Join Date
    Apr 2004
    Posts
    64
    If you are getting #error it means Access cannot evaluate the Control Source setting. Make sure the function is globally available. If you just dump the function inside the report's code it will only be visible within the report itself and cannot be evaluated for Control Source purposes. Dump it into a new or existing module. To confirm that it is globally visible press Ctrl-G to load the immediate window, and type in ?GetDocTypeCount(). If it returns a value you should have no problems using it as a control source.

  10. #10
    Join Date
    Mar 2004
    Posts
    118
    Thats not it... I still get the error.

    Well I think this is happening because its trying to do a circular query...

  11. #11
    Join Date
    Mar 2004
    Posts
    118
    DCount is the answer...

    Cant belive it was so simple after all

Posting Permissions

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