VBA Help file has information about DSum and the other domain functions.
Easiest way to find that info is to open help from VBE, go to Table of
contents, go to visual basic references, find the functions heading, and
look for the specific function name.
In order for the expression to work, it must be the control source of a
textbox in the detail section of the continuous forms view form; and you
must have fields name AR_Ref and Shell Reference in the form's record
source, or you need controls named those names.
Also, is either one of the AR_Ref and Shell Reference fields a text field
in
the table? If yes, you must delimit the criterion value with '
characters.
--
Ken Snell
<MS ACCESS MVP>
samwardill said:
I am using
DSum("[# Batches Confirmed]","Q_mnt_Production Req Status"," [AR_Ref]
="
&
[AR_Ref] And "[Shell Reference]=" & [Shell Reference])
It seems to sum over all records and ignores the criteria. I don't
understand how the criteria are intended work in the expression. Is
there
anywhere I can read more on this type of criteria?
:
Compare what you posted to what I posted.... you'll see some "
characters
in
my post. Put those " characters into your posted example.
--
Ken Snell
<MS ACCESS MVP>
I think we are nearly there. Thanks for all your support. I just
can't
get
the exact syntax of the Dsum expression (and the use of &.
The exact control I am using is:
=DSum([# Batches Confirmed],[Q_mnt_Production Req Status], [AR_Ref]
=&
[AR_Ref] & and [Shell Reference]=&[Shell Reference])
I'm also not quite sure I need or want the [AR_Ref] =& [AR_Ref] but
I
can
sort that out if I can get the expression to work.
Do you know what I am doing wrong?
:
Perhaps use a textbox whose control source is something like this:
=DSum("[# Batches Confirmed]", "NameOfQuery", "[AR REF]=" & [AF
REF] &
"
And
[Shell Ref]=" & [Shell Ref])
This assumes that the form has fields named AR REF and Shell Ref in
its
record source.
--
Ken Snell
<MS ACCESS MVP>
message
I have a table with fields: AR REF; # Batches Confirmed.
I have a table with fields AR REF; Shell Ref.
Many AR REFs map to One Shell Ref.
I have a query which links AR REF; Shell Ref & # Batches
Confirmed.
This
query is used in a form to maintain the value of # Batches
Confirmed
for
each
AR REF (also displays Shell Ref).
On the form I would like to display against each record the sum
of #
Batches
Confirmed for the Shell Ref associated with that AR REF
:
Well, if you would like a different suggestion, then tell me
more
details
about your exact setup and from where you get the data you want
to
use
as
the criterion and what is the form's setup, etc.. It's quite
doable,
but
we'll need details....
--
Ken Snell
<MS ACCESS MVP>
message
Thanks for the reply. I am tring to do this in a form. I'm
afraid
the
generic
way you suggested does not work as Current Input Product Code
is
not
a
field.
I am trying to sum a value over all records where the value of
a
field
is
equal to the value of the same field in the current record.
:
In which object do you want to do this: a form? a report?
A generic way of doing a conditional sum would be an
expression
like
this:
=Sum(IIf([Input Product Code]=[Current Input Product
Code],
[Quantity Produced], 0)
--
Ken Snell
<MS ACCESS MVP>
message
I have a continuous form which has the fields:
- Product Code
- Quantity Produced
- Input Product Code
I wish to add another field on each record which shows the
total
number
of
the Input Product Code used over all records (i.e. SUM
Quantity
Produced
IF Input Product Code = Current Input Product Code). I
don't
want
to
use
subtotals as I need the sort order to be different from
grouped
by
Input
Product Code. I can't create the sum in the query that the
form
is
based
on
as I need to update the value of Quantity Produced (the
form
locks
the
data
in this case - I think because the sum is not a one to one
relationship
with
the record). Any ideas?
