SUB Report HELP!!!!! 1

[Jack58]

I am in need of creating a Sub Form or Query to append to my current report to show all the Divisions that the report contains as such,

For example, each division has it own sheet showing the total items shipping to that location. I have a Total Sum on the last page, but have no idea how to get the Division information onto a Sub Report. I have tried to work my query so that only the division and the Items are listed and Using "Count" in the Total field to produce a query, with no such luck.

I am pulling the information from a Query, which is getting data from an ODB Link (of which I know very little about).

I want to have an ending sheet showing all my divisions (on one sheet) and the total count for each division of the sum of items shipping.

Sure Hope Someone Can HELP!! me with this problem.

Thanks in Advance

 

[CosmoKramer]

Create a report, grouped by division using the same query. Add an unbound text box and other control(s) to identify the division to the group footer and set the unbound text box control source to this:

=Count(*)

Set the Visible property of all sections of this report except the group footer to No.

Insert this report as a subreport in the report footer of your main report. This should give you the division totals...

 

[Jack58]

So far so good, however I am having a hard tme understand this part of your reply,

Add an unbound text box and other control(s) to identify the division to the group footer and set the unbound text box control source to this:


If you could please give me an exampe or more information it would be VERY helpful.


Thanks



Jack

 

[CosmoKramer]

You would probably want to add two text boxes. One, a bound control that should be the division name field. The second would be an unbound text box that would contain the count of records for that division =Count (*)......

 

[Jack58]

GOT IT, So far I have made the Query to do what I want, I have placed the sub form in the footer of my report.

The problem is that only one record is showing.

HELP!!!!


Jack

 

[CosmoKramer]

If you run the subreport independently, does it work??

 

[Jack58]

I have another question for you that I am having a problem with on my Database.

When I enter a [Enter Date] criteria on my query and I enter my date, next I connect to our Mainframe through DB2 Version 7.1) I get a Error, ODBC Failed. When I enter the date as #05/16/2003#, my data is brought in correctly.
Any thoughts?

If you would like to contact me directly jack.jackelini@amerisourcebergen.com



THANKS!!!!

 

[CosmoKramer]

First, did the subreport approach work for you??

Second, thanks for the star

Third, try this as your date criteria:

Format([Enter Date],"mm/dd/yyyy")

Dealing with OBDC date formatting and local system settings can be fugly, so sometimes its necessary to enforce things......

 

[Jack58]

Yes it worked Very well, I made a query then linked it to a report and brought the report into the Sub Form and It works GREAT.

You VERY Welcome for the star

The date format you gave me did not work, only frooze up Access. Do you have any other ideas on how I can get the date field to pop up vers having to open the Query each day to change the date?


Thanks



Jack

 

[CosmoKramer]

The [Enter Date] type parameter has always worked for me, even with linked ODBC databases.

Have you been able to use parameters for other field data types against the database before (numbers, text, etc)??

Is the date entered always a defined date, such as today's date, yesterday's date, etc., or would you be potentially entering any date??

 

[Jack58]

That method has always worked for me as well. The date would be changed at random to pull data to the report for a date of records needed. If I enter the date as, 05-16-2003 the Query returns the said date.

Jack