How to get a total of a particular product

[Scldb]

I have a textbox called Engines Manufacturer. This texbox gives me the type of engine that is on the product. I also have a textbox called Annual Qty Est. This textbox give the number of engines that is on that particular product. I have 2 questions.

1. I want to know the code to put in my textbox control source to see only the Annual Qty Est for a particular Engines Manufacturer. Example: In the Engines Manufacturer column in the report I have an engine called "BS". I want to know only the Annual Qty Est for "BS" engines.

2. To further go on this, I also want to the the percentage for only "BS" engines. Example: Lets say that on the report the sum of all Annual Qty Est is 100,000. Out of that, "BS" engines is 10,000 out of that 100,000. I know this is 10%, but I don't know the code to put into my control source for that textbox.

Can I please get some help on this matter.

[Rabbit]

You're most likely looking for:

DSum("[Annual Qty Est]", "Table Name", "[Engines Manufacturer] = '" & Me.[Engines Manufacturer])

And:

100*Me.[Name of Prior Control]/DSum("[Annual Qty Est]", "Table Name")

[Scldb]

You're most likely looking for:

DSum("[Annual Qty Est]", "Table Name", "[Engines Manufacturer] = '" & Me.[Engines Manufacturer])

And:

100*Me.[Name of Prior Control]/DSum("[Annual Qty Est]", "Table Name")

I tried this code: DSum("[Annual Qty Est]", "Area_1_Products", "[Engine Manufacturer] = "Kohler" & Me.[Engine Manufacturer])

And all I get is a error stating: Syntax error (missing operator) in query expression "First ([DSum("[Annual Qty Est]", "Area_1_Products", "[Engine Manufacturer] = "Kohler" & Me].[[Engine Manufacturer])])'.

So what am I doing wrong? Can you please help?
The Area_1_Products is the query in which this report is trying to generate.
And Engine Manufacturer is correct instead of Engines Manufacturer.

[Rabbit]

I tried this code: DSum("[Annual Qty Est]", "Area_1_Products", "[Engine Manufacturer] = "Kohler" & Me.[Engine Manufacturer])

And all I get is a error stating: Syntax error (missing operator) in query expression "First ([DSum("[Annual Qty Est]", "Area_1_Products", "[Engine Manufacturer] = "Kohler" & Me].[[Engine Manufacturer])])'.

So what am I doing wrong? Can you please help?
The Area_1_Products is the query in which this report is trying to generate.
And Engine Manufacturer is correct instead of Engines Manufacturer.

If you know Engine Manufacturer will always be Kohler, then it should be:

[Name of Textbox Control] = DSum("[Annual Qty Est]", "Area_1_Products", "[Engine Manufacturer] =  'Kohler'")

Otherwise, if you have a control on your form named Engine Manufacturer and that this can vary, it will be:

[Name of Textbox Control] = DSum("[Annual Qty Est]", "Area_1_Products", "[Engine Manufacturer] =  '" & Me.[Engine Manufacturer] & "'")

Also, this code needs to be in the after update event of the Engine Manufacturer textbox.

[Scldb]

If you know Engine Manufacturer will always be Kohler, then it should be:

[Name of Textbox Control] = DSum("[Annual Qty Est]", "Area_1_Products", "[Engine Manufacturer] =  'Kohler'")

Otherwise, if you have a control on your form named Engine Manufacturer and that this can vary, it will be:

[Name of Textbox Control] = DSum("[Annual Qty Est]", "Area_1_Products", "[Engine Manufacturer] =  '" & Me.[Engine Manufacturer] & "'")

Also, this code needs to be in the after update event of the Engine Manufacturer textbox.

What did/do you mean by your last statement "Also, this code needs to be in the after update event of the Engine Manufacturer textbox". I don't quite understand what you mean by this? Can you please elaborate?

[Scldb]

What did/do you mean by your last statement "Also, this code needs to be in the after update event of the Engine Manufacturer textbox". I don't quite understand what you mean by this? Can you please elaborate?

Now can you get me the code for the % of Kohler engines. I got the number 5,561 just for Kohler engines(which is correct). But the total (sum of all Annual Qty Est is 29,543. I want to know the code to put in a textbox that will give the the % of Kohler engines.

[Rabbit]

In the properties of the control, in the Events tab, there are certain events that can trigger code or a macro. You click the triple dotted button next to the event that you want to enter code and you select Code Builder from the pop up box. This will bring you to the Visual Basic Editor. From here you enter in the code you want to run whenever the event triggers.

[Scldb]

In the properties of the control, in the Events tab, there are certain events that can trigger code or a macro. You click the triple dotted button next to the event that you want to enter code and you select Code Builder from the pop up box. This will bring you to the Visual Basic Editor. From here you enter in the code you want to run whenever the event triggers.

I'll give that a try. How about the % code that I want to put in the control source of another texbox. I know that there is 29543 (sum of all Annual Qty Est) and 5,561 of that is Kohler. Which is about 18.82%, but I don't know the code to put in my control source.

[Rabbit]

If you look at post #2, the first code box is for the number the second code box is for the percentage.

[Scldb]

If you look at post #2, the first code box is for the number the second code box is for the percentage.

Thank you for helping me out. Hope to get more help from you in the future.

[Rabbit]

Not a problem, glad to help.