Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dcount Criteria Problemo (97)

    I did a search on Dlookup, but couldn't see anything that fit my problem..

    ok.. here goes.. <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

    I have 2 queries that interogate various tables. The first query just shows how many people are assigned to around 30+ various Workareas. and has fields NAME, AREA, and various other conditions. Lets call this query QDOCMATRIX1

    The second query is QDOCMATRIX2.Its fields are similar to the first, but the training column is called SO, and i added a Expr1: =Iif[SO]=TRUE,1,0 ----- This allows me to count properly. This table is similar to the first one, but it examines other training tables to decide if, in each of the instances of a training event, training has taken place.

    What i'm trying to do, in a report is show the stats for each of the Workareas. so the form will display all of the workareas, the number of training positions and then the number trained. then the percentage.. so it will look like this;-


    Basement 100.....80.....80%
    Cupboard 200.....50.....25%

    I have a table called WA . This shows each of the Workarea names. This table has two fields ID, (a number) and WA (text..ie: Basement)

    I have created a report based on the WA table. and down the left hand site it has all the Workarea text ... on the form its [Workarea]

    now, my problem, is that i wan't to add 2 dcounts in the detail section of the form, that will count the number of instances (I've read about the fact that its bad practice to add dcounts in loops, cos it slows things down. but in the absence of a better way of doing this, time is not really crucial here...)

    So for my first dcount, i placed a text box on the form, and got up to :- =Dcount("[NAME]","QDOCMATRIX1",[WORKAREA]=[AREA])

    Its the Criteria string at the end that i need to get right to make this thing work. I need to compare the workarea on the form, with the workarea in the query, its the syntax that i need to get right.
    I tried lots of various combinations of Me!Workarea=qdocmatrix.area, placing apostrophe's in there, adding quotes, adding both, taking some away, etc

    The second text field needs to do the same thing, compare the Workarea on the form with the area in the query, but count the number trained Expr1: (which is a positive [SO] field.

    So.... anyone know the secret access handshake ? <img src=/S/clown.gif border=0 alt=clown width=15 height=15>

    Regards

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Dcount Criteria Problemo (97)

    If you convert each of your queries into grouping queries, you can do all the counting there and avoid using dcount completely.

    You could have one query that count people by workarea, another that counts those who have had the training, then join them to the WA table for the report.

    If you really want to use dcount the syntax is:
    =Dcount("[NAME]","QDOCMATRIX1","[AREA]='" & me![WORKAREA] & "'")

    AREA the field name in the query, WORKAREA is the control name, there is an single quote after the =' and another one at the end between the two double quotes.

    The critera needs to be a string. The string is the concatenation of the "[AREA] ='" and the value of the control on the report. When you embed one string within another, you surround it with single quotes
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dcount Criteria Problemo (97)

    Thanks for the reply John....I tried that syntax and got an ERROR, in the field on the report.

    One of the things it keeps doing, is putting the 'me' in brackets [me]![wa] or bringing up an entry box for the value 'me'

    What i'm doing at the moment, is trying to figure out if my underlying fields match. I have checked them as far as i can, but i'm obviously missing something.

    Regards

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Dcount Criteria Problemo (97)

    Yes I have just tried my suggestion, and got exactly the same result as you!

    Here is an alternative, that I have tried and does work (for me at least)

    Make the control(s) that you want to display the count(s) unbound. ie. cut the dcount expression out of the control source , and instead use the on format event of the detail section of the report to put the count into the control.

    <pre>Me![peoplecount] = DCount("PersonName", "People", "[WorkArea] ='" & Me![WAName] & "'")</pre>


    I haven't tried to copy your names, but the syntax in the line above is correct.
    Regards
    John



  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dcount Criteria Problemo (97)

    Great John, thanks for your efforts in tackling this. i spent hours and hours today trying to figure out how to make it do it. The Access books that i have just give the basic syntax, all of which fail <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> I think there's a gap in the market for a book that shows obscure ways to make access do things......
    Well maybe there is one somewhere....

    One again, cheers! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Dcount Criteria Problemo (97)

    I have seen a book called something like "Access Cookbook", which is just a collection of code samples of how to do things. I think it is an O'Reilly book.
    Regards
    John



  7. #7
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dcount Criteria Problemo (97)

    Cool.. I will place it on my chrissy pressie list to Santa ASAP.

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Dcount Criteria Problemo (97)

    I just revisited because I was puzzled that my original suggestion did not work.

    Today I find that it does work if I take out the me.

    <pre>=Dcount("[NAME]","QDOCMATRIX1","[AREA]='" & [WORKAREA] & "'")</pre>


    I thought I tried this before, but I can't have. This avoids having to use the onformat event.
    Regards
    John



  9. #9
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dcount Criteria Problemo (97)

    Aha! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    I got it to work today John, on the works machine..... But... Boy does it cause problems.

    It takes around 10 minutes to produce the report. i have 2 dcounts in there. and around 60 work areas. each dcount counts anywhere from 200-1000 records<sigh>

    Even when it does work, lots and lots of times, it just crashes access !!! (I can cope with the speed. but not the crashing)

    I read on a thread in here somewhere, that putting a dcount in a 'detail' or loop of something sloooows things down.. boy.. what an understatement that was.

    On my home machine, im sure it would be ok.. but the works PC's are pretty naff (P3 -350mhz and 256megs of ram)

    So its back to the drawing board, I need to investigate how to do this at the query level.

    Thanks for your help tho. due to this i've found all sorts of things about dlookup, dcount, dsum etc, that i wasn't aware of. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Dcount Criteria Problemo (97)

    I think the query method would be much faster.
    I attach the db I have been testing with, which has two versions of the report. One uses dcount the other uses a grouping query.
    The situation is much simpler than yours I am sure, but it might help you think about how to use queries instead.
    Attached Files Attached Files
    Regards
    John



  11. #11
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dcount Criteria Problemo (97)

    Cheers John. your a <img src=/S/gent.gif border=0 alt=gent width=17 height=25>
    Will start on it right now <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Dcount Criteria Problemo (97)

    Here is another copy without security.
    Attached Files Attached Files
    Regards
    John



Posting Permissions

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