Results 1 to 6 of 6
  1. #1
    Jester0001 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Location
    Indiana
    Posts
    69

    Dynamic Labels on Report

    In short, I am trying to have labels that are filled with dynamic content that will be based upon criteria in a field.



    More specifically, I have created a report which has a [DateReceived] field (Date/Time) on it. I also have other fields with labels Day2, Day3, Day4, etc.

    What I am trying to do is to have the labels 'Day2', 'Day3' express the date based upon the [DateReceived] field on the report.

    For example:

    If [DateReceived] = 4/2/2012 Then

    Day2_Label = 4/4 <<<(The date format needs to be m/d)
    Day3_Label = 4/5

    In reference to this article:
    https://www.accessforums.net/showthr...mes-and-labels
    I modified the code to arrive at:
    Code:
    Private Sub Report_Open(Cancel As Integer)
    
    Dim strDay2Caption As String
    Dim strDay3Caption As String
    
    strDay2Caption = Me.DateReceived.ControlSource
    strDay3Caption = Me.DateReceived.ControlSource
    
    Me.Day2_Label.Caption = strDay2Caption
    Me.Day3_Label.Caption = strDay3Caption
    
    End Sub
    When the report opens, it replaces the text string of the labels to "DateReceived". How can I get the code to replace the labels with the [DateReceived] value plus two days? Three days? The report is based upon a query whose parameters change, so the value in [DateReceived] can change also.

    I tried using the immediate window to arrive at a solution, but then I found out that Me. won't work in the immediate window. How then would I check the value of Me.DateReceived.ControlSource using the immediate window?

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I suspect you'd want to use the .Value property, not .ControlSource. You can use the DateAdd() function to add a day, or simply add 1. Personally, I'd use a textbox instead of the label and code:

    =DateReceived
    =DateReceived + 1
    =DateAdd(...)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Jester0001 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Location
    Indiana
    Posts
    69
    I can work with that. Thanks again pbaldy!

    Here is what I put in the control source of the text box I created:
    Code:
    =Format([DateReceived]+2,"m/d")
    However, I had tried to use the .Value property of the text box without success, which is why I was trying to use the immediate window. Even though I won't be using VBA for the rest of this problem, can you tell me how you would find the value of the [DateReceived] field on the report using the immediate window?

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can use the full form reference:

    ?Forms!FormName.TextboxName
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Jester0001 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Location
    Indiana
    Posts
    69
    Ahh...this worked:
    Code:
    Reports!ReportName.TextboxName
    Thanks again pbaldy!

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Ahh, forgot you were on a report. Glad you sorted it out.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dynamic Form, Dynamic labels/ captions?
    By JFo in forum Programming
    Replies: 15
    Last Post: 10-12-2011, 08:33 PM
  2. Dynamic Report Caption
    By Crypto in forum Reports
    Replies: 3
    Last Post: 11-17-2010, 03:17 PM
  3. Replies: 1
    Last Post: 11-05-2010, 03:48 PM
  4. Replies: 0
    Last Post: 03-11-2010, 09:06 AM
  5. Dynamic Report
    By vCallNSPF in forum Reports
    Replies: 0
    Last Post: 12-08-2009, 04:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums