sub report multi column print 1

[GShen]

Is there a way to print a sub report multi column, down and then across but not filling out the entire page? I have about 4 inches on the report. If I go across and then then down, no problem as it creates the 4 columns a populates fine. However, I am printing and index by alpha description and I want to print down and across. When you do this, the 1st column gets printed and since the sub report is only 4 inches, I only get 1 column and it gets chopped off after the 4 inches. The only thing I can think of is to create 4 seperate reports but that would be crazy.

Remember when... everything worked and there was a reason for it?

 

[dhookom]

One method is to generate a column in your report's record source that can be used to sort across then down but appear to be down then across.
For instance a three column report from the Products table in the Northwind sample database could have a record source of:

SELECT Products.ProductName, 
DCount("*","Products","ProductName <""" & [ProductName] & """") Mod (DCount("*","Products")\3+1)+
DCount("*","Products","ProductName <""" & [ProductName] & """")*0.001 AS AtD2DtA
FROM Products
ORDER BY Products.ProductName;

Then in the report, set the Sorting to the AtD2DtA column. Even though the report columns are set to Across, then Down, they will appear alphabetical by ProductName Down, then Across.

Duane
Hook'D on Access
MS Access MVP

 

[GShen]

Hey Duane,
I tried retrofitting with my fields names and I got syntax problems all over the place. Trying to decifer what you are doing. Obviously if I have 4 columns I guess I set it to 4+1 instead of 3+1. I tried breaking it into pieces and still getting way too many syntax errors. What are you trying to count? You are looking for the productname < a null value? And then what is mult. by .001 doing? I get the concept that I sort on this number which will have the proper columng associated with each record but not sure how to fix the syntax because I don' understand the """ and """" fields.
Thanks,
Gary


Remember when... everything worked and there was a reason for it?

 

[GShen]

Also, I was trying to run this straight from SQL and it states DCount is not a function. So how can I populate and SQL record source with a VB function?

Remember when... everything worked and there was a reason for it?

 

[dhookom]

Could you share your SQL as well as a minimal amount of significant information about your data? All of the double-quotes are because the ProductName is a text field.

What do you mean by "straight from SQL"?

Why do you want to use a VB function to populate?

It seems you haven't nearly described your environment.

Duane
Hook'D on Access
MS Access MVP

 

[GShen]

Ok. This is an Access MDB. Running SQL server backend. The table is a local table from the MDB. table name is tblbook4columnIndex. Field name is tblbidx_Description. It is an a text field. There is another field in there I need to print for the page number(tblbidx_PageNumber) but it is irrelevant. So I am in essance printing 2 fields, the description and the page number. The current record source looks like the following:
SELECT tblbidx_Description, tblbidx_PageNumber FROM tblBook4ColumnIndex ORDER BY tblbidx_Description;
So I tried replacing with your code replacing the field names to no avail. I attached 2 copies. One with the obviouls problem an the other the way I need it. The way I got it to print correctly was making the bottom border 7.5 inches. I thought I could get cute but when I put the sub report in the main report, it tried stretching it again to a full page.

If you need anything else, let me know. I think I answered what you wanted. thx.



Remember when... everything worked and there was a reason for it?

 

[GShen]

Not sure if 1st one got attached. Did it again. And when I say I ran it from SQL. I took the code from the record source and ran it on enterprize manager. It is where you develope all the SQL for ADP's. You obviously can't use DCOUNT in native sql. As far as the VB comment goes, I have only used Dcount in VB. Never used it anywhere else.

Remember when... everything worked and there was a reason for it?

 

[dhookom]

Can we assume this is an MDB as you stated since you also mentioned ADP?

Can you provide answers to my questions? Seeing the output of your report doesn't tell me anything I didn't already know.

Duane
Hook'D on Access
MS Access MVP

 

[GShen]

mdb. I thought I answered ever question on the 1st reply back. Not sure what else you want me answer. I will paste again here.
-------------------------------------------------------
Ok. This is an Access MDB. Running SQL server backend. The table is a local table from the MDB. table name is tblbook4columnIndex. Field name is tblbidx_Description. It is an a text field. There is another field in there I need to print for the page number(tblbidx_PageNumber) but it is irrelevant. So I am in essance printing 2 fields, the description and the page number. The current record source looks like the following:
SELECT tblbidx_Description, tblbidx_PageNumber FROM tblBook4ColumnIndex ORDER BY tblbidx_Description;
So I tried replacing with your code replacing the field names to no avail. I attached 2 copies. One with the obviouls problem an the other the way I need it. The way I got it to print correctly was making the bottom border 7.5 inches. I thought I could get cute but when I put the sub report in the main report, it tried stretching it again to a full page.

If you need anything else, let me know. I think I answered what you wanted. thx.
-------------------------------
still trying to figure out that select you wrote in the meantime.

Remember when... everything worked and there was a reason for it?

 

[dhookom]

I would have liked to have seen what you tried when you substituted your table and field names in the Record Source SQL. Try:

SELECT tblbidx_Description, tblbidx_PageNumber,
DCount("*","tblBook4ColumnIndex","tblbidx_Description <""" & [tblbidx_Description] & """") Mod (DCount("*","tblBook4ColumnIndex")\4+1)+
DCount("*","tblBook4ColumnIndex","tblbidx_Description <""" & [tblbidx_Description] & """")*0.001 AS AtD2DtA
FROM tblBook4ColumnIndex

Don't waste your time placing and ORDER BY in the query. Set the Sorting in the report design to AtD2DtA.

Duane
Hook'D on Access
MS Access MVP

 

[GShen]

Duane,
I thought I posted but apparently did not . My problem was the double quotes. I had them after the description field. This the code I had.
see below:
--------------
SELECT tblbidx_Description, tblbidx_PageNumber,
DCount("*","tblBook4ColumnIndex","tblbidx_Description" <""" & [tblbidx_Description] & """") Mod (DCount("*","tblBook4ColumnIndex")\4+1)+
DCount("*","tblBook4ColumnIndex","tblbidx_Description" <""" & [tblbidx_Description] & """")*0.001 AS AtD2DtA
FROM tblBook4ColumnIndex
-----------------

That being said, it all sort of works but as you can see it is getting screwed up on the bottom line. I print tthe ATD2DTA field. There are 78 entries. IF I make 80 entries it works fine but I then have to replace the 4+1 to just 4. I am looking for 20,20,20 and 18 entries. My only solution now is to add records to always have the table divisible by 4. I would then suppress the records from printing. A little stupid but I still trying to decifer why routine is not working properly.

THanks for all your help.

Remember when... everything worked and there was a reason for it?

 

[dhookom]

Going across then down, you will not get 20,20,20 and 18. You might get 20,20,19 and 19. I'm not quite sure how to get this to flow properly. It would take more time than I have at the moment.

Duane
Hook'D on Access
MS Access MVP

 

[GShen]

no problem. You gave me a great start. Thaxnks!

Remember when... everything worked and there was a reason for it?