Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Dec 2000
    Location
    Hellertown, Pennsylvania, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access report VBA

    I have a report with values in the detail section. I want to show each day value from dates in a text box in a footer section. I'm stumped as to how to create some kind of loop. (See attached)

    Thanks for any response,
    Allan Bach
    Attached Images Attached Images

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,853
    Thanks
    4
    Thanked 259 Times in 239 Posts
    I moved this post to the Databases forum as it is more likely to get a resolution here. Access VBA is a somewhat special case so questions like this are best responded to in the Databases forum.

    Can you explain a bit more about what you want? Which numbers do you want to display there? Are you trying to concatenate the contents of a field into a summary field?
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    Star Lounger
    Join Date
    Dec 2000
    Location
    Hellertown, Pennsylvania, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have various dates in the detail section of a report. I want to concatenate each day part of the date into a field in the footer section. For example, if a client has meetings on 7/4/2014, 7/6/2014, and 7/20/2014, I want the days (4, 6, 20) to appear in a text box. I can get the day part of the dates, but I'm puzzled about placing the values. I thought about using a recordset and loop - not certain if that is the correct approach.

  4. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,853
    Thanks
    4
    Thanked 259 Times in 239 Posts
    You can use a custom vba function to prepare the string and then place the result into the report. The following page shows how this string could be constructed and included as part of the query
    http://allenbrowne.com/func-concat.html

    You could choose to just use the function and put this result into a text box without including it in the query. You might also want to modify the code so it uses the recordset on the report rather than recreating the query.

    Be aware that you may need your concat code to ignore multiple hits for the same day (if that is what you want)
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  5. #5
    Star Lounger
    Join Date
    Dec 2000
    Location
    Hellertown, Pennsylvania, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for pointing me in the right direction. Most appreciated!

  6. #6
    New Lounger
    Join Date
    Apr 2014
    Posts
    2
    Thanks
    0
    Thanked 1 Time in 1 Post

    The simplest approach is:

    Create a label in the Group Footer -- lDays in this example

    Click on the Group Header, add an Event Procedure for On Format with this code (Access adds the Sub and End Sub lines:
    Me!lDays.Caption = ""

    Click on Detail, add an Event Procedure for On Format with this code:
    Me!lDays.Caption = Me!lDays.Caption & " " & [Day] (where [Day] is your data field name or text box displaying it)

    That's all!
    Each time the group header changes, the label is cleared.
    Each time a new detail is prepared for display, the Day is appended to the label.
    The label automatically displays as part of the Group Footer.

    If you want to use a text field rather than a label, you can accumulate the days in a variable, then set the field = variable in the Group Footer On Format.

Posting Permissions

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