Results 1 to 10 of 10

Thread: Dynamic Report

  1. #1
    Join Date
    Sep 2014
    Posts
    10

    Unanswered: Dynamic Report

    Hi to everyone

    I have a report based on a crosstab query, which lists staff daily attendance for a month. The problem is that each month has different number of days (i.e. 30 or 31 feb 28) does each month I have to edit the report to adjust the number of days manually. Is there a way to set the report to dynamically adjust the number of days per month?

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what you could do is use unbound controls in your report. use the same query, but allocate values to controls using VBA in the report detail on format event. the attached PDF isn't what you are looking for, but gives a flavour of what can be done
    Attached Files Attached Files
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2014
    Posts
    10
    Hi thanks for the reply

    I’ve done a similar approach in a subform using query definition to assign the SQL, but in a report would I be able to maintain the same formatting and size?
    I try it out but I’m still very green when it comes to vba

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the easiest approach if you are nervous about using VBA would (in my opinion) be:-
    create numbers table (1 to 31)
    LEFT join the DAY of the month to this table (so you alway get days 1 - 31, irrespective of whether there are 31 days in that month)
    create your crosstba query based on that JOIN so you have days 1 to 31
    use that query in the report
    then depending on how creative you feel
    either hide the trailing dates using code int he on format event that don't exist (eg February 30 & 31; April, June, September & November 31st; not forgettign non leap year February's
    OR
    resize the report so its not immediately obvious when days have been chopped off the report
    ..right now I would strongly advise against trying to resize controls. not becuase it cant be doen. just its tricky, and not really required. its an elaboration /refinement of the report that looks nice but is a developmental blind ally.. could soak up lots of time, be a cause of frustration / annoyance and stopping you delivering a working report

    start slowly and build towards where you want your report to end
    ..so first step have a standard 31 days per month
    ..then start tinkering with the 'true' days in the month, by hiding columns.
    ..then tinker with the overall look and feel of the report


    as a first pass no one is going to care that you have 31 days in Feb, they may if they are anal and later on. but right now you've got two main hurdles
    1) getting the data
    2) gettignthe report format
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2014
    Posts
    10
    Thanks for the advice but in the meanwhile I tried to assign the sql to the detail format event as suggested earlier , but the result was the same, because (please see attached) I need to get rid of the extra “boxes” to work. While the crosstab query calculates the month period I input - the report does not grow or shrink accordingly.
    But you made me think, which report event would be best to enter an if statement to disable the extra boxes according to the month.

    Thanks for your advice and patience
    Attached Thumbnails Attached Thumbnails SampleReport.png  

  6. #6
    Join Date
    Sep 2014
    Posts
    10
    Thanks for the advice but in the meanwhile I tried to assign the sql to the detail format event, but the result was the same, because (please see attached) I need to get rid of the extra “boxes” to work. While the crosstab query calculates the month period I input the report does not grow or shrink accordingly. But you made me think, which report event would be best to enter an if statement to disable the extra boxes according to the month.
    Thanks for your advice and patience
    Attached Thumbnails Attached Thumbnails SampleReport.png  

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    on detail format event


    something like

    Code:
    select case month(mydatevalue)
    day29label.visible  = true
    day29control.visible  = true
    day30label.visible  = true
    day30control.visible  = true
    case 1,3,5,7,8,10,12 
       day31label.visible  = true
       day31control.visible  = true
    case 4,6,9,11
       day31label.visible  = false
       day31control.visible  = false
    case 2
      'insert rule for calucating if its a leap year
      'for now assume its NOT a leap year
       day29label.visible  = false
       day29control.visible  = false
      '
       day30label.visible  = false
       day30control.visible  = false
       day31label.visible  = false
       day31control.visible  = false
    end select
    assumes the month you are reporting on is defined as a date variable called mydatecolumn
    you have labels and a control for each day called dayxx....
    ...if thats not the case then replace those elements with the real names
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2014
    Posts
    10
    Thanks once more for your help:
    I’ve run a quick test based on your suggestion

    i.e.:
    Me.Label69.Visible = False
    Me.Ctl31.Visible = False

    And it didn’t work (always if I did it right) the same dialog box pop up saying “ The Microsoft Office Access Database engine does not recognize “ as a valid field name or expression .” which I’m not really sure what it means. The only way to have it work is to literally delete the label69 and ctl31l from the report.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Perhaps there is no visible property
    it may be you need to show/hide the label in the group header section

    access database engine message suggests you are referring to acolumn in Sql tgat the sql engine foesnt know about, not vba.
    when you get the message ehat line of code is yhe error reported on
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Sep 2014
    Posts
    10
    Dear healdem

    Thanks for your kind assistance, the easiest solution that I came with was to make four reports formatted to one for a 31 day month, another for a 30 day, 28 day for February and finally 29 day report in case of a leap year. the the on click event of the button I used a select case statement (similar to the one you proposed me) that evaluates the inputted month and thus load the appropriate report. It would have been more productive to have a single report to adjust according the month, but it was getting too complicated for my skills.

    once again thanks very much for our help.

Posting Permissions

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