Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2004
    Posts
    118

    Question Unanswered: Is there any way to filter data in the report rather than the query?

    I am running a query that shows all documents with type "TQ".

    Is there any way to have the report show only those documents with revision "like "p%""? normaly i would stick this in the query, but i need to have a counter of total number of "TQ" documents on thesame report and that 'like' clause is going to shorten that list.

    At the moment i have these fields in the report


    DocTag
    Sheets
    RevInt(normaly set to "p%")
    DocType (set to TQ)
    total: iif(doctag is not null, 1, 0)

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    how are you opening the report?

    If you are opening it through code behind a command button, a filter is the last variable in the Docmd.OpenReport command.

    If you are opening it through the regular DB window, go into design mode and view the report's properties. Under the data tab, set you filter and change FilterOn to Yes.

    This will still affect your count as written since the filter is applied FIRST, then the data is tabulated, then the pases laid out.

    As an alternative, to count something independant of the report's diplayed recordset, add a text box and use a DCount function as the control's recordsource. This will allow you to count whatever you want without being affected by the reports narowed set of records.

    Have fun

  3. #3
    Join Date
    Mar 2004
    Posts
    118
    I am using both methods:

    Case conCmdOpenReport
    DoCmd.OpenReport rs![Argument], acPreview

    For the user, because I'm using a switchboard. Because of the switchboard i cant edit this for this report as it will reflect on all other reports.

    Not sure what syntax to use for the data tab filter on the report.

    Do I just set "Filter on" to "YES" and type in a select statement in the "Filter"?

  4. #4
    Join Date
    Mar 2004
    Posts
    118
    NM,

    Figured it out


    Thanks a lot! so simple...

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Basically - the filter is part of the Select statement

    In general, the SQL wouild read:

    SELECT field1, field2 FROM table WHERE field3 = 7 ORDER BY field 1

    The filter is between WHERE and ORDER

  6. #6
    Join Date
    Mar 2004
    Posts
    118
    Ah, ok another problem


    Now i have 2 counters

    total: iif(DwgRevInt like "p*",1,0)
    totalopen: iif(DwgTag is not null,1,0)

    Now the report has a filter: DwgRevInt like "p*"
    BUT the Query does not.

    I have 2 counters:

    =sum(total)
    =sum(totalopen)

    Unfortunatly.... The filter in the report is acting thesame as the filter in the query.

    Meaning total and totalopen counters show thesame value if the filter is active on either query or report

    Is there any way (providing i cannot alter VB code on the account of the switchboard) to have the counter for total showing all records?

    example data

    Document Number||Sheet||Rev||Issue Date||Days Open

    AK.A.BS.0001|| 000 ||D01 ||17/02/2003||423
    AK.A.LD.0001|| 000 ||P02 ||03/03/2003 ||409
    AK.A.LS.0002|| 000 ||P00 ||02/07/2002 ||653

    in this case the total counter should show 3 while totalopen counter should show 2... Because 2 docs have rev p02,p00 and 1 has d01.

    I deleted the counter info so it fits on 1 line.
    Last edited by YevSnow; 04-15-04 at 12:24.

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    yes.

    refer to my previous post.

    Have the query give you all the records.
    Use the filter in the report.
    Put fields on the report that use the DCount and/or DSum functions (pick the appropiate ones) that figure against the query.

    It looks like you want DCount; the help file (or expression builder) can assist in writing the function.

    This way, the report filters the data the way you want it to come it and the functions work against the query, which has all the records.

    Good luck

  8. #8
    Join Date
    Mar 2004
    Posts
    118
    i wish it would work that way....


    But as soon as i put a filter on the report (in the report data tab) the only thing the total counter sums is whats on the report (which would be just the P*).

    Maybe thers another way to do a counter that would ignore the filter?

    I can do "select count(*) from ke0g_r3;" (ke0g_r3 is my query) as rowsource in a list box. That works, BUT ...

    With a list box, even if you set the border to transparent, it will still show the effect of an indented cell, which acts like a border. Thers no way to turn it off, its been driving me crazy.

    Note: This elusive border will only appear if you print the report - Go figure...
    Last edited by YevSnow; 04-16-04 at 03:35.

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    int the text box, use a DCount function against the query, using fileds from report in the filter.

    you may also want to use a "sub report" - which you can set the border to transparent (this will be less efficient, however, meaning it will slow the report dorn).

  10. #10
    Join Date
    Mar 2004
    Posts
    118
    You do realise I'm trying to count records that are not in the report...

  11. #11
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Quote:
    You do realise I'm trying to count records that are not in the report...

    Yes I do. The "domain functions" are independant of the recordsource of the contain from which their called. In English, that means that it doesn't matter what the recordsource for the report is.

    Check out DCount in the online help. The first part is the field you want to count. The second part is the datasource from which you are performing the count - which can be either a table or a query (ANY table or query in your database). The third part is the filter criteria (the WHERE clasue of a SELECT statement, without the word WHERE)

    You can use fields from your reports in the WHERE clause, making the domain count responsive to the current record in the report.

    For example, let's say I have a table of customers. I also have a table of customer orders. In my report of customers, which has only the customer table as the recordsource, I can have a DCount that counts the number of orders in the customer orders table, using the customer's ID field from the report in the WHERE clause of the DCount.

    Not the most efficient way to do it, but it works just the same.

  12. #12
    Join Date
    Mar 2004
    Posts
    118
    do you think this would work in a text box?

    help file
    =DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")

    What i got
    =DCount("ke0g_drawings","dwgtag","dwgtype = 'TQ'")

    gives me an #error

  13. #13
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Try these:

    Make sure everything is spelled correctly.

    Is ke0g_drawings the filed name or the table name?

    In the function, the field name comes first. The second piece is the table name.

    Finally, is dwgtag's datatype Text in the table?

    If I were to guess, you have the filed and table names reversed.

  14. #14
    Join Date
    Mar 2004
    Posts
    118
    Make sure everything is spelled correctly.

    -- did that

    Is ke0g_drawings the filed name or the table name?

    --table

    In the function, the field name comes first. The second piece is the table name.

    -- Do I need to do a function or can i just bang it in a text box on the report?

    Finally, is dwgtag's datatype Text in the table?

    -- Its Char(2)

    Basically im telling it to count every record of type "TQ"

  15. #15
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    DCount is a function, which you can use in a text box.

    I don't follow the "Char(2)" statement. If the data in that field is 2 chartacters of text, it should be ok.

    In the function, the first argument is the field name, so your function should read:

    =DCount("[dwgtag]","ke0g_drawings","dwgtype = 'TQ'")

    the structure is: DCount("field","table","criteria")

    To use a field from your report, the text box's recordsource would be:

    =DCount("[dwgtag]","ke0g_drawings","dwgtype = '" & [RptFieldName] & "'")

    where RptFieldName is the name of the text box that has the drawing type you want to count.

Posting Permissions

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