module to print report data in columns

[smile]

I don't know any VBA so I need your help. I need a module that would output data to a report in columns instead of rows.

When you make a report data is output like this:

Title
------------------------------------------
record1
record2
record3
record4
record5

I need a module that would output data like this:

Title
-------------------------------------------

record1; record2; record3; record4;
record5; record6; record7; record8;

The amount of columns and rows should be definable.

Or perhaps there is a more simple way to do it?

 

[Moniker]

In a report, each report has a separate "page setup" that's stored with the report. Go to the report, open it in design mode, and then select File -> Page Setup in the drop-down menus. From there, click on the Columns tab, enter the number of columns you want, and then select "Across then Down" for the column format at the bottom of the Columns tab.

Save it and you're good to go. Note that if you want to do this for multiple reports, you'll have to set up each report separately. That's what I meant by each report having its own separate "page setup".

 

[smile]

In a report, each report has a separate "page setup" that's stored with the report. Go to the report, open it in design mode, and then select File -> Page Setup in the drop-down menus. From there, click on the Columns tab, enter the number of columns you want, and then select "Across then Down" for the column format at the bottom of the Columns tab.

Save it and you're good to go. Note that if you want to do this for multiple reports, you'll have to set up each report separately. That's what I meant by each report having its own separate "page setup".

Thanks for your suggestion, I found this http://support.microsoft.com/default.aspx?scid=kb;en-us;141624

It looks like not very complicated however I was unable to replicate this.

Your subreport method sounds fine too but works only for across then down, not down then across listing. I need to list some data one way and other "down then across listing" way.

 

[smile]

Can't I somehow pull data using query and them merge the results separated by "," or ";".

Then I would put unbound box on report page and some code and it would display data that I need?

 

[CyberLynx]

Is it just me? I'm a little confused here.

Moniker has provided you with basically the simple answer you need.

Your subreport method sounds fine too but works only for across then down, not down then across listing. I need to list some data one way and other "down then across listing" way.

Where does it say anything about a SubReport?

....not down then across listing......

Sure it does. It's the first Column Layout option.

I need a module that would output data like this:

Title
-------------------------------------------

record1; record2; record3; record4;
record5; record6; record7; record8;

The example in your first post you have shown that you want Across and Down which is the second Column Layout option.

Give Moniker's post a good read...and decide which way you want to go.

.

 

[smile]

Moniker has provided you with basically the simple answer you need.

Yes he did, but I asked for a VBA solution not subreport.

Where does it say anything about a SubReport?

In my post it doesn't because I don't want to use subreport to format the data in columns. If you did not know if you use "down then across listing" you must set "CanGrow" to "No" if you do this the report does not extent to the next page and all data that does not fit in it gets thuncated.
That is way I wan't to make it using VBA, and I found a link that shows how but no examples etc.

The example in your first post you have shown that you want Across and Down which is the second Column Layout option.

Sorry I should have posted that I need both options.

 

[smile]

While I tried the subreport column mode I ran into a problem with subreports headers being not shown.

I found a fix here http://209.85.135.104/search?q=cach...ess+subreport+header&hl=lt&ct=clnk&cd=2&gl=lt

Also another bug is that I use between to limit my results using query that feeds the data to subreports. The problem is I need to enter dates when I click print preview and when I click print button I need to enter them once more.


The fix is for simple subreport one that does not use columns. Any help?

 

[WayneRyan]

Smile,

Untested version of VBA method.

Make a Public Function in a module:

Add a new column to your query:

NewField: FormatFields("[YourField]", "[YourTable]", "[YourPrimaryKey] = " & [YourPrimaryKey], 3, 10)

Public Function FormatFields(FieldName As String, _
                             TableName As String, _
                             Condition As String, _
                             NumberOfClms As Integer, _
                             ColumnWidth As Integer) As String
Dim rst As DAO.Recordset
Dim clm As Integer
Dim Temp As String

clm = 1
Temp = ""

Set rst = CurrentDb.OpenRecordset("Select " & FieldName & " " & _
                                  "From " & TableName & " " & _
                                  "Where " & Condition)
While Not rst.EOF And Not rst.BOF
   Temp = Temp & RTrim(rst.Fields(FieldName)) & Space(ColumnWidth)
   clm = clm + 1
   If clm MOD NumberOfClms = 0 Then Temp = Temp & vbCrLf
   rst.MoveNext
   Wend

FormatFields = Temp
End Function

For this example results *should* look like:

Adam      Bob       Charles   
David     Edgar     Fred      
George

hth,
Wayne

 

[Moniker]

I'm not sure what the hell you are talking about with subreports and all this other stuff. (Excuse the French.) Take a look at the attached. It's a small DB with one table that has one field in it. The field has the numbers 1 to 500, sequentially. There are two reports, r_Across_then_Down and r_Down_then_Across. They do what you'd expect by their titles. There's not really a reason to do this in VBA as it's already built-in to Access's functionality.

If that's not right, then I don't get what you're after.

 

[WayneRyan]

Moniker,

I don't know if we have Smile still with us.

Granted, the subreport choice may well be appropriate here, but there are some reports
where the data might be "presented" like:

                             From A Child Table
                            ====================
Product1    Description     Customer1  Customer2    ReOrder Qty     Something Else
                            Customer3  Customer4
                            Customer5

Product2    Description     Customer1  Customer6    ReOrder Qty     Something Else
                            Customer12  
                            ====================

In those cases, I think you gotta use VBA.

I'm confused too. I'm not even sure what Smile needs in the first place.

See ya,
Wayne

 

[smile]

I'm still here

I need a code that would take records from a query like:

Record 1
Record 2
Record 3

and so on....

Than make that they would look in my first query across then down, in my second query down, then across.

I have 2 queries I must use in my report, I could use subreports but subreport has problems with colums and headers not being repeated and in somesituations can't be use at all.

I also tried to hide a subreport if it is empty and failed, the code that works fine forx a textbox does not work for subreport.

I taught that it should be simple universal VBA code like convert numbers to words etc. that you could place unbound box on report with the code and that is it. The code "module" takes data and makes records separated by ";" then outputs them down then across or across then down specified by some variable.

 

[smile]

Smile,

Untested version of VBA method.

Make a Public Function in a module:

Add a new column to your query:

NewField: FormatFields("[YourField]", "[YourTable]", "[YourPrimaryKey] = " & [YourPrimaryKey], 3, 10)

Public Function FormatFields(FieldName As String, _
                             TableName As String, _
                             Condition As String, _
                             NumberOfClms As Integer, _
                             ColumnWidth As Integer) As String
Dim rst As DAO.Recordset
Dim clm As Integer
Dim Temp As String

clm = 1
Temp = ""

Set rst = CurrentDb.OpenRecordset("Select " & FieldName & " " & _
                                  "From " & TableName & " " & _
                                  "Where " & Condition)
While Not rst.EOF And Not rst.BOF
   Temp = Temp & RTrim(rst.Fields(FieldName)) & Space(ColumnWidth)
   clm = clm + 1
   If clm MOD NumberOfClms = 0 Then Temp = Temp & vbCrLf
   rst.MoveNext
   Wend

FormatFields = Temp
End Function

For this example results *should* look like:

Adam      Bob       Charles   
David     Edgar     Fred      
George

hth,
Wayne

Tried your code but the line I place in my query:

NewField: FormatFields("[YourField]", "[YourTable]", "[YourPrimaryKey] = " & [YourPrimaryKey], 3, 10)

Gives me an error: expression contains invalid syntax.
Perhaps you omitted comman or something.