Help with dsum

  • Thread starter Thread starter Spidey3721
  • Start date Start date
S

Spidey3721

From a previous post:

I have a form with two fields, NameID and VALUE.

I have a SUM textbox in the footer that currently has "=sum([VALUE])".

Question: If I want to include all VALUE's with the associated NameID of,
say, "Apple" - could I add a criteria to this - =sum([VALUE]) WHERE
forms!NameID Not like "apple".


I am trying to use DSUM to add all values in the VALUE textbox except ones
associated with the NameID "apple"





=DSum([name of VALUE textbox],Forms![name of current form],[name of NameID
textbox] Not Like "*apple")

I am getting a #Error in the SUM field whenever I change to form view - Any
idea what I'm doing wrong???
 
I am trying to use DSUM to add all values in the VALUE textbox except ones
associated with the NameID "apple"





=DSum([name of VALUE textbox],Forms![name of current form],[name of NameID
textbox] Not Like "*apple")

I am getting a #Error in the SUM field whenever I change to form view - Any
idea what I'm doing wrong???
Click to expand...

You're assuming that Forms and Tables are interchangable. THEY ARE
NOT!

A Form is just a window, a tool to let you see and work with data in a
table. DSum takes three *text string* arguments: the name of a field
in a Table or Query; the name of that table or query; and an optional
third argument, a valid SQL WHERE clause without the word WHERE.

That is... you can DSum values in a Table. You can't DSum textboxes in
a Form.
 
A big help once again -

Just to make sure I'm clear:

Can I use DSum as the control source of a textbox in a form or report, as
long as I only reference tables/queries in the actual DSum statement, or
must I do this inside a query and use that query as the control source of
the form/report ?


John Vinson said:
I am trying to use DSUM to add all values in the VALUE textbox except ones
associated with the NameID "apple"





=DSum([name of VALUE textbox],Forms![name of current form],[name of NameID
textbox] Not Like "*apple")

I am getting a #Error in the SUM field whenever I change to form view - Any
idea what I'm doing wrong???
Click to expand...

You're assuming that Forms and Tables are interchangable. THEY ARE
NOT!

A Form is just a window, a tool to let you see and work with data in a
table. DSum takes three *text string* arguments: the name of a field
in a Table or Query; the name of that table or query; and an optional
third argument, a valid SQL WHERE clause without the word WHERE.

That is... you can DSum values in a Table. You can't DSum textboxes in
a Form.
Click to expand...
 
Just to make sure I'm clear:

Can I use DSum as the control source of a textbox in a form or report, as
long as I only reference tables/queries in the actual DSum statement, or
must I do this inside a query and use that query as the control source of
the form/report ?
Click to expand...

Either way will work. If you put the DSum in the Query you can use
that field as a search criterion, to sort the results, in a filter,
and you can get a grand total by summing its value in the form/report
footer; if you don't need to do any of those things, just use it as
the control source of a textbox.
 
Back
Top