Sum values in form - help please!

  • Thread starter Thread starter gjoneshtfc
  • Start date Start date
G

gjoneshtfc

Hello

I have a form that sums the total [Balance Due] for all records which
works fine. However i want a seperate total at the bottom which just
totals records from one of the customers ( [Customer ID] field). Can
anyone give me any pointers on how to get this to work please? I have
tried using IIF but it is not working!


Thanks for your help!
Gareth Jones
 
Use an unbound text field to display the customer's balance due. I would
suggest updating the value in the form's Current event.

If the Customer ID field in your table is numeric:
Me.txtCustomerBalanceDue = Nz(DSum("[Balance Due]", "SomeTableName", _
"[Customer ID] = " & Me.txtCustomerID

If the Customer ID field in your table is text:
Me.txtCustomerBalanceDue = Nz(DSum("[Balance Due]", "SomeTableName", _
"[Customer ID] = '" & Me.txtCustomerID & "'"


SomeTableName is the name of the table where you keep the data.
Me.txtCustomerId is the name of the control on your form that has the
Customer ID.
 
Hello,

Thanks for the reply. I am trying your solution but have become
confused! I need to get the data from a query not a table so am
assuming this works the same by substituting the table name with the
query name?

This is then when i get confused: What should the next bit read? I use
text for the customer ID and the name i want to calculate on is "M J
Motors". In your example there is no place for the customer - or is
there? I am also confused at the speech marks in your example - copying
to notepad makes it look as follows:

"[Customer ID] = ' " & Me.txtCustomerID & " ' "

Where you have put a ' is that where i am meant to put M J Motors?

Thanks again for your help,
Gareth
 
See comments below:

[email protected] said:
Hello,

Thanks for the reply. I am trying your solution but have become
confused! I need to get the data from a query not a table so am
assuming this works the same by substituting the table name with the
query name?
Click to expand...
Not a problem. You can use a query instead of a table.
This is then when i get confused: What should the next bit read? I use
text for the customer ID and the name i want to calculate on is "M J
Motors". In your example there is no place for the customer - or is
there? I am also confused at the speech marks in your example - copying
to notepad makes it look as follows: This is correct.

"[Customer ID] = ' " & Me.txtCustomerID & " ' "
Click to expand...

You would not put "M J Motors" anywhere in your code. The control on your
form that I call txtCustomerID would contain that value or the name of
whatever customer you are subtotaling for.
Where you have put a ' is that where i am meant to put M J Motors?
Click to expand...
No, the ' is required because you are summing based on a text value. What
the SQL that performs the sum will actually see is [Customer ID] = 'M J
Motors'
 
Hello

Thanks again. However it is not working and i'm getting even more
confused. I am very new to access hence quite a few posts recently from
me! On the form i want a complete total for all records as well as one
just for M J Motors. Wouldnt changing the control source of the form to
Customer ID = M J Motors filter out all other records? What confuses me
is the Me.txtCustomerID - what is this? Will this just be [Customer
ID]?

This post probably doesnt make any sense to you - nor to me really!

Thanks again,
Gareth
 
No, it makes perfect sense. I understood you want to have two totals. Once
for all and one for the selected customer. If your total that includes all
customers is working, leave it alone. Here is what you need.

You need a control on your form that contains the customer ID, whether it be
M J Motors or any other customer. This will be used in the DSum function so
it knows which customer to include in the sum it returns.

You need a control on your form to display the results of the the sum, which
is your Customer Balance Due. This is the control that will be updated in
the Current event of the form with the code I posted earlier.

Perhaps it would be helpful to you if you open your VB Editor and look in
Help for the DSum function. It may give you an idea of how this works.

Please post back if you have more questions.
 
Hello

That makes sense to me - maybe it is just the terminology?! When you
say i need "a control on my form" do you just mean a textbox? I will
look up help for the DSUM function as well to see if that helps me in
any way.

Thanks for the reply - and your patience!
Gareth
 
Hello

I have to go now but i will be back on later - thanks for your help. If
i have any other questions can i post them on here later please?

Thanks again,
Gareth
 
A text box is one type of control. Another is a combo box, for example. I
use the generic term because I don't know what types of controls you are
using. It is most typical to use a combo box when you want to look up a
specific value in a table, but I don't want to confuse you any more at this
point.
 
Hello

Thanks for the help - the expression you wrote makes a bit more sense
now. I will have another go at it later today. Something always comes
up when you want to get on with a different task! I will let you know
how i get on.

Thanks again, Gareth
 
Back
Top