Results 1 to 9 of 9
  1. #1
    Edwin1970 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    4

    Variable number of columns on subreport

    Hi,



    For my aircraft spotting hobby I compiled a list of aircraft I've seen or still need to see for certain Airlines. Now I want to print a report on special sized paper for aan Success Agenda. The report is based on this query:

    Click image for larger version. 

Name:	ReportQuery.JPG 
Views:	34 
Size:	32.6 KB 
ID:	38813

    The report is grouped on fldAirlineName (left red left in image below)and then on fldAircraftType (blue in left image below). As I don't want airlinename and aircraft type to be included in the colum I designed a subreport. I now base the number of columns (7) in the subreport on the longest registration. Like the ones from Belavia (on right image below). But most registrations have length like the one from Austrian and can fit in 8 columns and few even in 9 columns. Is there a way to make the number of colums variable in the subreport? E.g. default to 8 colums, but if airline name is 'Belavia' make it 7 or when name is 'Cathay Pacific' make it 9 columns? Because as it is now it takes about 10 more pages to print as when I had it in Word with 7, 8 and 9 registrations per row.

    Click image for larger version. 

Name:	ReportDesign.JPG 
Views:	33 
Size:	25.9 KB 
ID:	38814 Click image for larger version. 

Name:	ReportPrintPreview.JPG 
Views:	33 
Size:	41.9 KB 
ID:	38815

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    don't know if it can be done, but look at setting the number of columns in the Format event. Might also be easier if you didn't use a subreport

  3. #3
    Edwin1970 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    4
    I used the subreport, because else the Airlinename and AircraftType are also limited to the column width. I tried without subreport, but couldn't show it like it is now. And can you change in the format event for just one detail section or will it then be done for all airlines.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I don't know - it was a suggestion for you to try.

  5. #5
    Edwin1970 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    4
    Anyone has a solution in the mean time?

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    use a smaller font?

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    How many variations are needed? If a small number (e.g.3) then use 3 sub reports with a query for each.
    Or have 1 control that is 9 fields wide and concatenate the values into it.
    Or stop trying to layout Access forms and reports like spreadsheets and instead export to Excel.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Edwin1970 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    4
    Quote Originally Posted by Ajax View Post
    use a smaller font?
    It already is the smallest readable font. But problem is with some I have some unused space I want to use

    Quote Originally Posted by Micron View Post
    How many variations are needed? If a small number (e.g.3) then use 3 sub reports with a query for each.
    Or have 1 control that is 9 fields wide and concatenate the values into it.
    Or stop trying to layout Access forms and reports like spreadsheets and instead export to Excel.
    Excel is not easy to use for this, because when there is a new registration it needs to be alphbetically sorted. That would be hard with Excel. It's indeed 3 options. I'll try to see if I can work with your idea. Query can be the same. Only number of columns of the subreport has to be changed on opening. Thank you.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OK, not sure if number of columns can be varied, but if it can it will be in the subreport onformat event. You'll need to experiment

    Perhaps another method should be considered - use something like the concatrelated function to string the flight numbers together into a single string, perhaps separated with " | ". Then you can use a formula to insert a linefeed at | character for the width of your report. You can mimic conditional formatting for the different colours if you build the string as rich text. You probably want to use a fixed width font to maintain a consistent spacing

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

Similar Threads

  1. Replies: 1
    Last Post: 10-30-2018, 05:37 PM
  2. Replies: 2
    Last Post: 10-12-2016, 05:48 PM
  3. Replies: 7
    Last Post: 07-26-2012, 12:18 AM
  4. Getting data in 2 columns in subreport
    By opopanax666 in forum Reports
    Replies: 2
    Last Post: 01-27-2012, 01:56 AM
  5. Columns in a subreport
    By kcm4491 in forum Reports
    Replies: 1
    Last Post: 03-10-2011, 12:11 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