Calucaltion for a product
- Thread starter shaz123
- Start date
apr pillai
AWF VIP
- Local time
- Today, 14:14
- Joined
- Jan 20, 2005
- Messages
- 737
Create a TextBox at the Footer of the Form. Write at the Control Source Property the following formula to take count of the Current Product Number on the Screen.
=DCount("*","TableName","ProdNo = " & [ProdNo])
TableName is the Table connected to your Form. To get the Total Value of the Product:
=DSum("Amount","TableName","ProdNo = " & [ProdNo])
Assuming that the [ProdNo] is numeric. Otherwise modify the last parameter enclosing in Single Quotes, i.e. "ProdNo = '" & [ProdNo] & "'"). Notice the single quote immediately after the equal sign followed by a double quote and the closing single quote within double quotes at the end before the closing parenthsis.
visit my site http://msaccessblog.blogspot.com
apr pillai
=DCount("*","TableName","ProdNo = " & [ProdNo])
TableName is the Table connected to your Form. To get the Total Value of the Product:
=DSum("Amount","TableName","ProdNo = " & [ProdNo])
Assuming that the [ProdNo] is numeric. Otherwise modify the last parameter enclosing in Single Quotes, i.e. "ProdNo = '" & [ProdNo] & "'"). Notice the single quote immediately after the equal sign followed by a double quote and the closing single quote within double quotes at the end before the closing parenthsis.
visit my site http://msaccessblog.blogspot.com
apr pillai
Calculation
Hi,
Thanxs for trying to trying to point me in the right direction, i am still getting the same error meesage even after trying your bit of code, as my report is based on a query, were table name is i have put the query name "CylinderBalance" is that right.
I have attached a copy of my form
As you can see Im trying to add the number of products displayed on the form.
I tried the following coding
=DCount("*","CylinderBalance","ProdNo = " & [ProdNo])
Hi,
Thanxs for trying to trying to point me in the right direction, i am still getting the same error meesage even after trying your bit of code, as my report is based on a query, were table name is i have put the query name "CylinderBalance" is that right.
I have attached a copy of my form
As you can see Im trying to add the number of products displayed on the form.
I tried the following coding
=DCount("*","CylinderBalance","ProdNo = " & [ProdNo])
apr pillai
AWF VIP
- Local time
- Today, 14:14
- Joined
- Jan 20, 2005
- Messages
- 737
calculation for a product
Your problem was on the form but the sample you have showed me is a Report.
I think you are using the Formula in the Page Header section of the Report in that case it will show error. You have to write the formula in any of the following areas:
1. Report Header or Footer Section.
2. Group Header or Footer Section (see Grouping and Sorting in Report Design) - an ideal place for Group Totals, there you can use Count(fieldName), Sum(FieldName) etc. not necessary to use DSum() or DCount() Functions.
DSum(), Dcount() Functions were suggested for the Form using the Current Record ProductNo as the Key for taking count of that Product from the underlying table. You don't have to use this method on the Report.
apr pillai
Your problem was on the form but the sample you have showed me is a Report.
I think you are using the Formula in the Page Header section of the Report in that case it will show error. You have to write the formula in any of the following areas:
1. Report Header or Footer Section.
2. Group Header or Footer Section (see Grouping and Sorting in Report Design) - an ideal place for Group Totals, there you can use Count(fieldName), Sum(FieldName) etc. not necessary to use DSum() or DCount() Functions.
DSum(), Dcount() Functions were suggested for the Form using the Current Record ProductNo as the Key for taking count of that Product from the underlying table. You don't have to use this method on the Report.
apr pillai
Last edited:
Calculation
Sowi my mistake, it was a report not a form, thanxs alot because that has
worked. I also needed to work out the total for each different product,
for e.g. if there is one 212110 product No showing for a customer there should be a label displayin the total of one for that particualar product, were as if der is also two 255510 product no, there should be a label underneath giving a total of two.
So even though i have the main total of all the prodcuts at the customer location, i need a total for each product.
Any Ideas
Sowi my mistake, it was a report not a form, thanxs alot because that has
worked. I also needed to work out the total for each different product,
for e.g. if there is one 212110 product No showing for a customer there should be a label displayin the total of one for that particualar product, were as if der is also two 255510 product no, there should be a label underneath giving a total of two.
So even though i have the main total of all the prodcuts at the customer location, i need a total for each product.
Any Ideas
apr pillai
AWF VIP
- Local time
- Today, 14:14
- Joined
- Jan 20, 2005
- Messages
- 737
calculation for a product
Your query is not so clear. Let me put it this way. Your Customer have several product group, let us assume - Bakery Items, Hardware, Plastic Items etc. under each group several products. For example hardware: Nails, Nuts, Bolts and each have a product Number. In this scenerio what you can do is to concentrate on Grouping and Sorting Method on Report. Select Customer as the Main Group, Product Group Below that. Set Yes for Group Headers and footers on the Property Sheet.
Cutomer Group Header
.......Product Group Header
............Product 1
............Product 2
.............
Product Group Footer - here you can write Sum(productValue) for totals in Text Boxes.
=[ProductName] & " Total Value = " & sum(ProductValue)
result : Bakery Items Total Value = 12345
Customer Group Footer
=[CustomerName] & " Total Value = " & sum(ProductValue)
If the Products are only a few items say 3 items and their Product Codes are known and you want to get the Total of each item separately one below the other try this formula at the Report Footer or Group Footer:
="Hardware Total = " & Sum(iif([ProdCode] = 212110,[ProdValue],0))
="Bakery Total = " & Sum(iif([ProdCode] = 255510,[ProdValue],0))
I hope this will do the job.
apr pillai
Your query is not so clear. Let me put it this way. Your Customer have several product group, let us assume - Bakery Items, Hardware, Plastic Items etc. under each group several products. For example hardware: Nails, Nuts, Bolts and each have a product Number. In this scenerio what you can do is to concentrate on Grouping and Sorting Method on Report. Select Customer as the Main Group, Product Group Below that. Set Yes for Group Headers and footers on the Property Sheet.
Cutomer Group Header
.......Product Group Header
............Product 1
............Product 2
.............
Product Group Footer - here you can write Sum(productValue) for totals in Text Boxes.
=[ProductName] & " Total Value = " & sum(ProductValue)
result : Bakery Items Total Value = 12345
Customer Group Footer
=[CustomerName] & " Total Value = " & sum(ProductValue)
If the Products are only a few items say 3 items and their Product Codes are known and you want to get the Total of each item separately one below the other try this formula at the Report Footer or Group Footer:
="Hardware Total = " & Sum(iif([ProdCode] = 212110,[ProdValue],0))
="Bakery Total = " & Sum(iif([ProdCode] = 255510,[ProdValue],0))
I hope this will do the job.
apr pillai
Last edited:
