Access report VBA

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote

Thanks for pointing me in the right direction. Most appreciated!

Reply | Quote

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.

Reply | Quote