Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: adding a field from a qry that isnt the reports record source

    i have a rpt employees, the record source is qryEmployees, i have another query that has nothing to do with employees and need to add a field from that qry to the employee report. I added a txtbox went to the data tab and added =[Query220]![NetProceeds] but when i go to view the report i get prompted to enter the parameter value for Query220

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try:

    =DLookup("NetProceeds", "Query220")
    Paul

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or failing that why not add the element you want intot he query.. dlookups' can be a real performance hog. if you have only a few dlookups its probably ok..., if you have many the report may well have severe problems.

    likewise if you use a lot of aggregate functions in report footers (page, group etc) it may be better for performance reasons to do the aggregation yourself than issue a series of aggregation commands in SQL,

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    What do you mean by "do the aggregation yourself"?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Oct 2003
    Posts
    233
    Quote Originally Posted by pbaldy
    Try:

    =DLookup("NetProceeds", "Query220")

    that didnt work

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Try

    =DFirst("NetProceeds", "Query220")

    And I notice you said that it asks for a parameter for "Query220". Are there any parameters in this query? You may need to supply them in the Query - Parameters menu command.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by StarTrekker
    What do you mean by "do the aggregation yourself"?
    if you use a lot of sums/totals in footers Ive found it can take a long time to let the report mechanism do the footers itself.. each column requires a separate SQL command... talking to a large data set with lots of groups can make the report very slow.

    doing simple aggregation (by summing the totals yourself in the reports on detail event can save a significant amount of time). natch it won't work if you are using complex statistical functions. but it will work for simple arithmetical operators and basic stat functions. If you are using dLookups in reports, especially more than one per row then you will have a real performance hog, occasional use of domain functions is great, but in my books the occasional should be in bold type. Usually you can get to what the domain functions give you via another route and usually more cheaply


    just my 2 1/2d

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by mikezx10
    that didnt work
    Error? Wrong value? Etc?
    Paul

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Thanks healdem
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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