Results 1 to 7 of 7
  1. #1
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185

    Report Header Dates

    So I have created a cross tab report that based on the information in the query takes any dates found in the query and places the units in that column based on the rows department. I am thinking that report header will not change based on how I have it set up right now. My question is, what is the best way to go about based on today's date have the report change the headers dynamically based on today's date and the future. I want to forecast out based today's date what units are coming on dates and what department they are in. Any help would be appreciated.



    Click image for larger version. 

Name:	Access209Report.png 
Views:	20 
Size:	69.2 KB 
ID:	30783Click image for larger version. 

Name:	QueryDesign.png 
Views:	19 
Size:	32.6 KB 
ID:	30784

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    If its based on cancelby date, in the criteria box on that column, put: >= Date()

  3. #3
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Quote Originally Posted by Bulzie View Post
    If its based on cancelby date, in the criteria box on that column, put: >= Date()
    Thank you for the reply, This has helped me. At least now I only have future dates but I am still looking into, say Monday comes around and I want to run the report again. I want the dates to change on the report automatically. Would this require using vba to change the report Headers and re-query the the report based on the new dates?

    Thanks,
    Nick

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Since you are using that formula in the criteria, when you open the report it should always start with the current date. You should not need to requery.

  5. #5
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Thank You,

    I also have a question on making totals for each date in the report footer? What is the best possible way to go about this? Is this possible without going through a lot of trouble?

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Create a new query based on the crosstab and make it a totals query, summing all the columns. Then make a subreport and put it in the footer.

  7. #7
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    So I have converted all of my dates to numbers so that in theory I can reference to the report columns. I have used this article to convert over below but it works by months. I would like to change this example to weeks. Will this work for weeks? I continue to get the wrong date of 12/31 for the start Date instead of the inputted date.

    Click image for larger version. 

Name:	reportdesign2.png 
Views:	8 
Size:	21.4 KB 
ID:	30928
    http://www.fmsinc.com/microsoftacces...ort/index.html

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

Similar Threads

  1. Replies: 2
    Last Post: 09-19-2017, 11:31 AM
  2. Replies: 1
    Last Post: 06-15-2016, 08:45 AM
  3. Dates as Column Header
    By afvillodres in forum Queries
    Replies: 1
    Last Post: 07-24-2012, 06:04 PM
  4. Replies: 2
    Last Post: 06-15-2012, 07:32 AM
  5. Report Header
    By kwooten in forum Reports
    Replies: 2
    Last Post: 08-03-2011, 11:31 AM

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