A
Andy
Hi;
This is rather lenghty but I have been working on this project for months;
and I felt that to move on, this post needed to be detailed.
Steve Schapel has been nice enough to help me overcome several major
obstacles over the last week or so and I don't want to occupy too much of
his time so I thought I would post the question anew.
I've been working on report that has a mandatory layout that cannot vary.
The one page report is 8.5"X14", with 7/10" margins all the way around,
(T/B/L/R). The controls are .15" in height and they occupy the entire
length of the page w/o any header of footer.
The layout of the report is:
Categories:
Hats:
Stetson, (fixed, (Label's caption Shows
"Stetson"))......(ProdBuyUnitPrice*ProdBuyUnitsBought)
Other, (variable name generated by bound control)
Other, (variable name generated by bound control)......(Total of all
"Others" ProdBuyUnitPrice*QtyPurchased)
Sub-Total Hats ..................Amt=[Stetson]+[Other]
There may be 2,3, or 4 Stetson hats but they all prices must total on that
one line.
The "Others" lines in this instance need to be limited to the first 2
entries and after the word "Other" a bound control must generate the name of
each product purchased.
Shirts:
Arrow, (fixed, (Label's
caption))...........................................(ProdBuyUnitPrice*ProdBu
yUnitsBought)
Van Hussein, (fixed, (Label's
caption))...................................(ProdBuyUnitPrice*ProdBuyUnitsBo
ught)
Other, (variable name that is generated by bound
control)......(ProdBuyUnitPrice*ProdBuyUnitsBought)
Other, (variable name that is generated by bound
control)......(ProdBuyUnitPrice*ProdBuyUnitsBought)
Other, (variable name that is generated by bound
control)......(ProdBuyUnitPrice*ProdBuyUnitsBought)
Other, (variable name that is generated by bound
control)......(ProdBuyUnitPrice*ProdBuyUnitsBought)
Sub-Total
Shirts..................Amt=[Arrow]+[VanHussen]+[Other1]+[Other2]+[Other3]+[
Other4]+[Other5]
Again there may be 2,3, or 4 Arrow shirts, (short sleeve, long sleeve, pull
over), but they all must total on that one line.
And for this Category after the word "Other" a bound control must generate
the name of the product purchased with an individual amount for each line.
With a limit of 5 others and 5 individual amounts, may be none but must be
limited to 5
Total of all Categories..................Amt.
This is what has been accomplished so far:
There is one table named "tblProductsBought" currently containing 48
records.
The fields are:
ProdBuyCatagoryName, ProdBuySubCategoryName, ProdBuyName,
ProdBuyUnitsBought, and ProdBuyUnitPrice.
The data is:
ProdBuyCatagoryName: Clothing, Stationary Etc. (5 Total Categories)
ProdBuySubCategoryName: Shirts, Hats, Shoes, Pens, Pencils Paper Etc. or
"Other", (from 3 to 15 Sub Categories)
ProdBuyName: Arrow, Van Hussen, Bottany Etc. or "Other" (limited only by the
customers pocket book)
I had created a query that contained seperate expressions such as: (one for
each "possibility"):
ClothingPrc: IIf([qryProdBuyTbl]![ProdBuyCatagoryName]="Clothing" And
[qryProdBuyTbl]![ProdBuySubCategoryName]="Shirt",[qryProdBuyTbl]![ProdBuyUni
tsBought]*[qryProdBuyTbl]![ProdBuyUnitPrice],"")
This generated the proper answers for the report but placed one answer on
each page for a total of 48 pages.
Steve corrected me and the query was changed to contain only:
ProdBuyCatagoryName and ProdBuySubCategoryName ProdBuyName fields and the
expression: Prc: [ProdBuyUnitsBought]*[ProdBuyUnitPrice]
Using Sorting/Grouping in the report, (we used ProdBuySubCategoryName), with
only a Footer and the controls placed in the Footer w/the exception of [Prc]
which was changed to TotalPrice=Sum([Prc]).
This displayed all of the answers on one page like it should. with the
proper amounts.
And now the obstacle:
So far I have been unable to get those answers to the proper positions.
As stated before the report has a mandatory layout that I cannot vary. It
isn't in any particular Alphabetically nor Numeric layout. "This item must
be displayed here and that is that!"
I have tried creating an unbound control similar to this:
ShirtPrc: =IIf([ProdBuyCatagoryName]="Clothing" And
[ProdBuySubCategoryName]="Shirt",[TotalPrice],"")
It generated the proper answer but it displayed it down the page at the same
point that the TotalPrice for that item was displayed in the footer,
(ShirtsArrowPrc top position was set to zero yet display at 5"), tried other
items also and they displayed in the same position paage as the price in the
footer.
Tried it in the details section, the report header/footer, page
header/footer, group header/footer, and tried Visible=False for each one at
a time, Hide duplicates for the control and a few dozen others, such as half
a dozen or so Sort/Groups.
Anybody have any ideas?
Andy
This is rather lenghty but I have been working on this project for months;
and I felt that to move on, this post needed to be detailed.
Steve Schapel has been nice enough to help me overcome several major
obstacles over the last week or so and I don't want to occupy too much of
his time so I thought I would post the question anew.
I've been working on report that has a mandatory layout that cannot vary.
The one page report is 8.5"X14", with 7/10" margins all the way around,
(T/B/L/R). The controls are .15" in height and they occupy the entire
length of the page w/o any header of footer.
The layout of the report is:
Categories:
Hats:
Stetson, (fixed, (Label's caption Shows
"Stetson"))......(ProdBuyUnitPrice*ProdBuyUnitsBought)
Other, (variable name generated by bound control)
Other, (variable name generated by bound control)......(Total of all
"Others" ProdBuyUnitPrice*QtyPurchased)
Sub-Total Hats ..................Amt=[Stetson]+[Other]
There may be 2,3, or 4 Stetson hats but they all prices must total on that
one line.
The "Others" lines in this instance need to be limited to the first 2
entries and after the word "Other" a bound control must generate the name of
each product purchased.
Shirts:
Arrow, (fixed, (Label's
caption))...........................................(ProdBuyUnitPrice*ProdBu
yUnitsBought)
Van Hussein, (fixed, (Label's
caption))...................................(ProdBuyUnitPrice*ProdBuyUnitsBo
ught)
Other, (variable name that is generated by bound
control)......(ProdBuyUnitPrice*ProdBuyUnitsBought)
Other, (variable name that is generated by bound
control)......(ProdBuyUnitPrice*ProdBuyUnitsBought)
Other, (variable name that is generated by bound
control)......(ProdBuyUnitPrice*ProdBuyUnitsBought)
Other, (variable name that is generated by bound
control)......(ProdBuyUnitPrice*ProdBuyUnitsBought)
Sub-Total
Shirts..................Amt=[Arrow]+[VanHussen]+[Other1]+[Other2]+[Other3]+[
Other4]+[Other5]
Again there may be 2,3, or 4 Arrow shirts, (short sleeve, long sleeve, pull
over), but they all must total on that one line.
And for this Category after the word "Other" a bound control must generate
the name of the product purchased with an individual amount for each line.
With a limit of 5 others and 5 individual amounts, may be none but must be
limited to 5
Total of all Categories..................Amt.
This is what has been accomplished so far:
There is one table named "tblProductsBought" currently containing 48
records.
The fields are:
ProdBuyCatagoryName, ProdBuySubCategoryName, ProdBuyName,
ProdBuyUnitsBought, and ProdBuyUnitPrice.
The data is:
ProdBuyCatagoryName: Clothing, Stationary Etc. (5 Total Categories)
ProdBuySubCategoryName: Shirts, Hats, Shoes, Pens, Pencils Paper Etc. or
"Other", (from 3 to 15 Sub Categories)
ProdBuyName: Arrow, Van Hussen, Bottany Etc. or "Other" (limited only by the
customers pocket book)
I had created a query that contained seperate expressions such as: (one for
each "possibility"):
ClothingPrc: IIf([qryProdBuyTbl]![ProdBuyCatagoryName]="Clothing" And
[qryProdBuyTbl]![ProdBuySubCategoryName]="Shirt",[qryProdBuyTbl]![ProdBuyUni
tsBought]*[qryProdBuyTbl]![ProdBuyUnitPrice],"")
This generated the proper answers for the report but placed one answer on
each page for a total of 48 pages.
Steve corrected me and the query was changed to contain only:
ProdBuyCatagoryName and ProdBuySubCategoryName ProdBuyName fields and the
expression: Prc: [ProdBuyUnitsBought]*[ProdBuyUnitPrice]
Using Sorting/Grouping in the report, (we used ProdBuySubCategoryName), with
only a Footer and the controls placed in the Footer w/the exception of [Prc]
which was changed to TotalPrice=Sum([Prc]).
This displayed all of the answers on one page like it should. with the
proper amounts.
And now the obstacle:
So far I have been unable to get those answers to the proper positions.
As stated before the report has a mandatory layout that I cannot vary. It
isn't in any particular Alphabetically nor Numeric layout. "This item must
be displayed here and that is that!"
I have tried creating an unbound control similar to this:
ShirtPrc: =IIf([ProdBuyCatagoryName]="Clothing" And
[ProdBuySubCategoryName]="Shirt",[TotalPrice],"")
It generated the proper answer but it displayed it down the page at the same
point that the TotalPrice for that item was displayed in the footer,
(ShirtsArrowPrc top position was set to zero yet display at 5"), tried other
items also and they displayed in the same position paage as the price in the
footer.
Tried it in the details section, the report header/footer, page
header/footer, group header/footer, and tried Visible=False for each one at
a time, Hide duplicates for the control and a few dozen others, such as half
a dozen or so Sort/Groups.
Anybody have any ideas?
Andy