Results 1 to 7 of 7
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Reports - Adding data from a Query to a header (A2K - SR1)

    Is there a way to add data to a report from an independent query that is not tied to the data used to create the report. I want to add some particular data to a header in a report. The data is not in the query used to create the report, nor can it be added to the query. I set an event on the report open that launches a separate query that only provides one field as an answer. How do I get this field into the header of the report. I inserted a subreport, (which works), however, it messed up all of the report formatting. Is there a way to have a text box link to this independent query in the report header?
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports - Adding data from a Query to a header (A2K - SR1)

    You can use DLookup if it is a value in a saved query or table.
    You could also write a user function to return what you want.
    Francois

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Reports - Adding data from a Query to a header (A2K - SR1)

    So what you are saying is to have the report launch the function. How, then, would I get the data into the report?
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports - Adding data from a Query to a header (A2K - SR1)

    Lets say that you have a function in your report to return a number like this:
    <pre>Private Function faktnr() As Long
    Dim db As Database, rs As Recordset
    Set db = DBEngine.Workspaces(0)(0)
    Set rs = db.OpenRecordset("qryfaktuurnrmax", DB_OPEN_DYNASET)
    rs.MoveFirst
    faktnr = rs![maxoffaktuurnr] + 1
    End Function</pre>

    All you have to do is adding a unbound textbox with control source =faktnr() (include the =)
    Francois

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Reports - Adding data from a Query to a header (A2K - SR1)

    Thanks Francois.

    I actually went with the DLookUp idea to get the data (shown below) that gets the Description field from the query named QUERY - CPRF1 REPORT GET DESCRIPTION to get the answer.

    =DLookUp("[Description]","QUERY - CPRF1 REPORT GET DESCRIPTION")

    Thanks for the help. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,

    Gary
    (It's been a while!)

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports - Adding data from a Query to a header (A2K - SR1)

    In DLookup you have to add a condition. As you say in you pevious mail that the query contains only one row, you could use:
    <pre>=DLookUp("[Description]","QUERY - CPRF1 REPORT GET DESCRIPTION","[Description] <>''")</pre>

    Francois

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Reports - Adding data from a Query to a header (A2K - SR1)

    Thanks Franois,

    The criteria option, as I read in access, is optional. Since my query will only ever return one value, it is not necessary.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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