Calculation don't work as I expect (1 Viewer)

Mihail · Jan 16, 2014

The DB from this thread post #13 has a single report.
In that report you will see a calculation (red controls) that don't work as I expect.
Are you so kind to make me understand what is wrong here ?

Thank you !

namliam · Jan 16, 2014

That is totaly strange, executing the same DSum in the immediate window does return the proper value

I am absolutely clueless as to why it isnt displaying the proper amount?

Mihail · Jan 16, 2014

Thank you for your time, namliam.

plog · Jan 20, 2014

I posted this on the other thread:

I found the fix. I can't explain why your DSUM doesn't work, but I did get the right answer when I enclosed the field you were summing with quote marks:

=DSum("[Pharm]","qryEvlCharges","[early]=" & [early])

When I put quote marks around [Pharm] it returned the correct value.

Galaxiom · Jan 20, 2014

plog said:
I can't explain why your DSUM doesn't work, but I did get the right answer when I enclosed the field you were summing with quote marks:

=DSum("[Pharm]","qryEvlCharges","[early]=" & [early])

It is important to realise that the first argument of the DOmain functions is not a fieldname but an expression.

Without the quotes, the DSum is fed the value from the Pharm control in the current record. Hence the function returns that value multiplied by the number of records.

Mihail · Jan 20, 2014

Thank you (all of you) for yours time and help.
While now I know how to fix in the future errors like this, still I don't understand Galaxiom's explanation.
So, Galaxiom, can you try, for me, to be a little bit more explicit ? Thank you.

Galaxiom · Jan 20, 2014

DSum(Expr As String, Domain As String, [criteria])

You can put anything in that first argument. Though it is mostly used to return a fieldname it doesn't have to be. It can be useful for returning multiple fields formatted to your heart's content. For example, using a DLookup for a full name from multiple fields in a single command ControlSource.

Meanwhile any expression in the context of the form is tried against the controls and fields collections of that form. So when the literal was fed to the DSum instead of a string, the value of the control by the same name was read.

The DSum dutifully summed the integer fed to its Expr argument, just as if you had written:
SELECT SUM(2345) AS A FROM whatever

You get the number of records times the integer.

Mihail · Jan 20, 2014

@Galaxiom
Thank you !!!!
This open new ways in using this functions.
Indeed. Until now I have think that the "Expression" argument require a field name.

Calculation don't work as I expect (1 Viewer)

Mihail

Registered User.

namliam

The Mailman - AWF VIP

Mihail

Registered User.

plog

Banishment Pending

Galaxiom

Super Moderator

Mihail

Registered User.

Galaxiom

Super Moderator

Mihail

Registered User.

Similar threads

Users who are viewing this thread