SUMIF Function with Textbox Value

[adrian94]

Hello, I am currently working on a form. I used textbox to set the value of currency and sumif to total nett price for all of data with that specified currency.

How can I get the value of the textbox?

I use formula :
=Sum(IIf([Currency]="USD",[NettPrice],0))
and it's worked. It sums up all net price with USD currency

However, the currency types are so many and it's impossible for me to list them down one by one, so I use this formula :
=Sum(IIf([Currency]=[txtCurrenct],[NetPrice],0))

But it keep showing 0 result.

Can anybody help me? I would highly appreciate if you can give solutions for my problem.

Thank you very much.

 

[bob fitz]

Perhaps you could use the DSum() function. See: http://www.techonthenet.com/access/functions/domain/dsum.php

 

[JHB]

Maybe because of a typo, I suppose it should be txtCurrency

=Sum(IIf([Currency]=[txtCurrenct],[NetPrice],0))

 

[adrian94]

sorry, I typo when copy that in this thread. but, in my program, I already use txtCurrency

 

[bob fitz]

Have you tried something like:
= DSum("NettPrice", "[YourTableName]", "Currency = '" & Me.txtCurrency & "'")

 

[pr2-eugin]

Use Bob's suggestion.

 

[adrian94]

Thanks Bob for the answer,but i don't know it shows error result: #Name?

Even if I use
=DSum("NettPrice", "[YourTableName]", "Currency = '"USD"'")
It still shows #Name?

hmmm... I confused....

 

[vbaInet]

You could also
1. Create a Totals query to sum up the net price Grouped By currency.
2. Use this query to do a right join to your form's query
4. Include the summed field in your form.

 

[adrian94]

You could also
1. Create a Totals query to sum up the net price Grouped By currency.
2. Use this query to do a right join to your form's query
4. Include the summed field in your form.

Can you show me how to create that query? Thanks

 

[bob fitz]

Thanks Bob for the answer,but i don't know it shows error result: #Name?

Even if I use
=DSum("NettPrice", "[YourTableName]", "Currency = '"USD"'")
It still shows #Name?

hmmm... I confused....

Did you replace the text in red with the actual name of your table/query.

 

[vbaInet]

Can you show me how to create that query? Thanks

Here's a link:
http://office.microsoft.com/en-gb/access-help/sum-data-by-using-a-query-HA010096310.aspx