Problems with Dsum function

[walduxas]

Hey,

I need to sum values of currencies in one column (Expr2) considering to which currency I choose (Currency is written in column Expr8). I added a text box In Microsoft Access Form. I entered this formula:

=DSum("[Expr2]";"Query1";"[Expr8] = 'USD'")

However when I go to the 'Form View' window I receive an error in this field value. When I change this formula like this:

=DSum("[Expr2]";"Query1")

then everything is ok and Access sums all the values of all the currencies. But I need to use a criteria to set which currencies I want to sum. So does anybody know what is wrong?

 

[ByteMyzer]

You might try changing:

=DSum("[Expr2]";"Query1";"[Expr8] = 'USD'")

...to:

=DSum("[Expr2]";"Query1";"[Expr8] = ""USD""")

 

[walduxas]

Does not help,

I really don't understand where is the problem. Maybe Access does not allow to set criterias for expression fields?...

However, when I try to make a report all the calculations are OK and look like this:

AUD 4500

AUD 2
AUD 100
..
..

USD 200000

USD 500
USD 3000
..
..

I mean, that the report shows all the currencies with their individual values and then sums those currencies. Would it be possible in the report not to show those detailed currencies (like USD 500, 3000 etc.) and to show only the total values for the different currencies (like AUD 4500, USD 200000 etc.)

thanks in advance.

 

[Ron_dK]

=DSum("[Expr2]";"Query1";"[Expr8] = 'USD'")

Or try :

=DSum("[Expr2]";"Query1";"[Expr8] like 'USD*'")

 

[walduxas]

Or try :

=DSum("[Expr2]";"Query1";"[Expr8] like 'USD*'")

Thanks, it helped. Now I'm wondering is there an opposite function for "like"? Let's say I would like to sum all the currencies except USD. If I write a function
=DSum("[Expr2]";"Query1";"[Expr8] not like 'USD*'")[/quote], then I receive an error. it looks like there is no "not" function in the Access forms. Do you have any ideas what I could use instead of "not like"?

 

[DCrake]

The Not operator is a valid expression, however you could try

DSum("[Expr2]","Query1","[Exp8] No In('USD')")

or

DSum("[Expr2]","Query1","[Exp8] In('USD')")

or

DSum("[Expr2]","Query1","[Exp8] In('" & strCurrencyType & "')")




David

 

[walduxas]

It's getting more and more interesting
The function "In" also works as the function "Like". Operators "Not" and "No" do not work. I receive an error message if I use these operators.

In my query there are two columns [Expr2] and [Expr8]. First one contains the values of transactions. Second one caontains types of currencies (like USD, EUR etc.). So in my opinion it looks like Accsess both these columns considers as 'text' type, so in DSum function I can't use operators like "=", ">" etc. If I use functions "In" or "Like", then everything works fine.

So now I have another problem. I want to sum those values that are less than 10 000. In VBA I wrote this function:

If Me.Check38.Value = True Then
Me.Text37.Value = DSum("[Expr2]", "Query1", "[Expr2] < 10000")
End If

However this function gives an error. I believe it's because of "<" sign. When I change this formula to this one:

If Me.Check38.Value = True Then
Me.Text37.Value = DSum("[Expr2]", "Query1", "[Expr2] In('10000,00')")
End If

In this case function is working, but it adds only values that are equal to 10000, and I need to add values taht are less than 10000. Is there any way to solve this problem?

 

[jal]

You might need a conversion such as
Clng (Long number)
CDBL (double)
CDec (Decimal)
CCur (currency)

For example try this:
DSum("[Expr2]", "Query1", "CDec(Expr2) < 10000")