none
dlookup in unbound text box report

    Question

  • Hi I am trying to the following

    (DLookUp("([CountCatW1])","qrySurveilanceWeek-1","[Disease] = [Disease]" And "NotificationDetailsOid = NotificationDetailsOid"))

    I am adding this control to the Disease Header,

    I need to count diseases for week1 , week2 etc.

    My qrySurveilanceWeek-1 only shows 1 records, when i run this in the report it applies the 1 records next to all disease, how can i work around this

    example in hepB disease group week1 there are  x1 count

    in hepA disease group week1 there are 0count

    When i run the report i note a count of x1 in hepA disease group, i know what it is doing, i dont know how to stop it.

    any ideas?


    David
    Tuesday, May 17, 2011 6:38 AM

Answers

  • Hi David,

    the easiest way here is the following: since you have created a separate query for each date period, I believe they all have the structure like Disease | CountByPeriod. So, you have 5 separate queries. Now you can create a new query, add all these 5 into it, join them as 1-to-1 by Disease field, add a Disease field from any query and then add CountByPeriod field from every of these 5 queries. This new query will be the recordsource for you report.

    Don't use DLookUp, DCount and other Domain functions too much. If you want to add 1 field of unrelated information, that's ok. But if you begin to use these functions in a big amount instead of correct SQL, you may face a strong perfomance decrease. Yes, they work easy but not always suitable.


    Andrey V Artemyev | Saint-Petersburg, Russia
    • Marked as answer by KIWI DAVE Wednesday, May 18, 2011 6:23 AM
    Wednesday, May 18, 2011 5:40 AM

All replies

  • Hi,

    first of all, your WHERE clause is incorrect. If you want to compare a filed value in your query with report's one, you should use:

    DLookUp("[CountCatW1]","qrySurveilanceWeek-1","[Disease]=" & [Disease] & " And [NotificationDetailsOid]=" & [NotificationDetailsOid])

    Don't forget to add ' or "" if these values are text.

     


    Andrey V Artemyev | Saint-Petersburg, Russia
    Tuesday, May 17, 2011 6:57 AM
  • Thanks Andrey,

    of course,

    I will test this tomorrow at work,

    thanks again

    David


    David
    Tuesday, May 17, 2011 8:28 AM
  • Hi Andrey,

    Ok maybe my issue is larger than what i thought.

    1. i have to create a report

    I wish i could paste an image in to show you.

    the report is vertical in display

    Headings are

    Disease (Grouped heading) | Current week| -1 week|-2 week| 1 Jan - 1 May | Annual Totals

    Hepatits B                                2                  1

    Chlamydia                                5                  3

    Now i have a query that counts the Current Week and this works fine, now i need to count only disease that fall in -1Week and -2Week.

    What am i attempting to do with the -1Week and -2Week is create seperate queries that only show records in a date parameter, the query its self works, now i need to get the information from qrySurveilanceWeek-1 into the report in the above format.

    I have tried

    txtCountWk1 = DLookUp("[CountCatW1]","qrySurveilanceWeek-1","[Disease]=" & [Disease] & " And [NotificationDetailsOid]=" & [NotificationDetailsOid])

    This fails, i think it fails as this is in the Disease Header, i have tried it in the Details section.

    I am not sure maybe i should be doing this a different way.

    any ideas


    David
    Wednesday, May 18, 2011 5:08 AM
  • Hi David,

    the easiest way here is the following: since you have created a separate query for each date period, I believe they all have the structure like Disease | CountByPeriod. So, you have 5 separate queries. Now you can create a new query, add all these 5 into it, join them as 1-to-1 by Disease field, add a Disease field from any query and then add CountByPeriod field from every of these 5 queries. This new query will be the recordsource for you report.

    Don't use DLookUp, DCount and other Domain functions too much. If you want to add 1 field of unrelated information, that's ok. But if you begin to use these functions in a big amount instead of correct SQL, you may face a strong perfomance decrease. Yes, they work easy but not always suitable.


    Andrey V Artemyev | Saint-Petersburg, Russia
    • Marked as answer by KIWI DAVE Wednesday, May 18, 2011 6:23 AM
    Wednesday, May 18, 2011 5:40 AM
  • Ok tried this,

    Thanks so much, you have pointed me in the right direction.

    Here is what i have done.

    1. Added both these queries to a new querie.

    2. Joined them on Disease Category

    3. Added Disease | DiseaseCategory | CountCatW , from both of the following queries,

    I will use this as my record source and let you know.

    <tfoot></tfoot>
    qrySurveilanceWeek-1
    -1 Disease DiseaseCategory CountCatW1 NotificationDetailsOid DiseaseOid
    9/05/2011 Chlamydia Sexually Transmissible Infections 1 11976 11967
    <tfoot></tfoot>
    qrySurveilanceWeek-2
    Week2 Disease DiseaseCategory CountCatW2 NotificationDetailsOid
    17/05/2011 Gonococcal Sexually Transmissible Infections 1 11978

    David
    Wednesday, May 18, 2011 6:23 AM