Hello,
Any help would be appreciated!!!
I need to sum AMOUNTTEND on a receipts subform that meet a certain criteria.
It has to be something like.
=sum([amounttend]) where [description]="tour installment"
in other words, I want to total only the amounts when the description field
= "tour installment"
Can anyone help with the correct syntax?
Thanks,
Dave
On Sat, 03 Nov 2007 01:54:45 GMT, "David Grist"
wrote:
Check out DSum in the help file.
-Tom.
>Hello,
>
>Any help would be appreciated!!!
>
>I need to sum AMOUNTTEND on a receipts subform that meet a certain criteria.
>
>It has to be something like.
>
>=sum([amounttend]) where [description]="tour installment"
>
>in other words, I want to total only the amounts when the description field
>= "tour installment"
>
>Can anyone help with the correct syntax?
>
>Thanks,
>
>Dave
>
I checked out dsum but that is summing the whole table.
"Tom van Stiphout"
news:r8tni358cav5tj35bdqt6cg2floemhhgie@4ax.com...
> On Sat, 03 Nov 2007 01:54:45 GMT, "David Grist"
> wrote:
>
> Check out DSum in the help file.
> -Tom.
>
>
>
>>Hello,
>>
>>Any help would be appreciated!!!
>>
>>I need to sum AMOUNTTEND on a receipts subform that meet a certain
>>criteria.
>>
>>It has to be something like.
>>
>>=sum([amounttend]) where [description]="tour installment"
>>
>>in other words, I want to total only the amounts when the description
>>field
>>= "tour installment"
>>
>>Can anyone help with the correct syntax?
>>
>>Thanks,
>>
>>Dave
>>
try
=Sum(IIf([description]="tour installment",[amounttend],0)
hth
"David Grist"
news:FxQWi.71$Cc.56@trndny09...
> Hello,
>
> Any help would be appreciated!!!
>
> I need to sum AMOUNTTEND on a receipts subform that meet a certain
criteria.
>
> It has to be something like.
>
> =sum([amounttend]) where [description]="tour installment"
>
> in other words, I want to total only the amounts when the description
field
> = "tour installment"
>
> Can anyone help with the correct syntax?
>
> Thanks,
>
> Dave
>
>
On Sat, 03 Nov 2007 04:43:01 GMT, "David Grist"
wrote:
Not if you put in a where clause as the 3rd argument:
=dsum("AmountEnd", "SomeTable", "Description='tour installment'")
-Tom.
>I checked out dsum but that is summing the whole table.
>
>
>"Tom van Stiphout"
>news:r8tni358cav5tj35bdqt6cg2floemhhgie@4ax.com...
>> On Sat, 03 Nov 2007 01:54:45 GMT, "David Grist"
>> wrote:
>>
>> Check out DSum in the help file.
>> -Tom.
>>
>>
>>
>>>Hello,
>>>
>>>Any help would be appreciated!!!
>>>
>>>I need to sum AMOUNTTEND on a receipts subform that meet a certain
>>>criteria.
>>>
>>>It has to be something like.
>>>
>>>=sum([amounttend]) where [description]="tour installment"
>>>
>>>in other words, I want to total only the amounts when the description
>>>field
>>>= "tour installment"
>>>
>>>Can anyone help with the correct syntax?
>>>
>>>Thanks,
>>>
>>>Dave
>>>
>
Unfortunately, none of the offered solutions are working for me
I tried, on the subform, ....
=dsum("[amounttend]","[receipts]","[description]=2")
this sums ALL records meeting the criteria in the receipts table and not
just the membership record I'm working on. If there was a way to put an
"AND" argument after the description qualifier, that might work? For
instance...
=dsum("[amounttend]","[receipts]","[description]=2" AND
"[studentid]=forms![student account data].studentid)
that might work.... is there a way?
"David Grist"
news:FxQWi.71$Cc.56@trndny09...
> Hello,
>
> Any help would be appreciated!!!
>
> I need to sum AMOUNTTEND on a receipts subform that meet a certain
> criteria.
>
> It has to be something like.
>
> =sum([amounttend]) where [description]="tour installment"
>
> in other words, I want to total only the amounts when the description
> field = "tour installment"
>
> Can anyone help with the correct syntax?
>
> Thanks,
>
> Dave
>
>
On Sat, 03 Nov 2007 19:31:52 GMT, "David Grist"
wrote:
Of course you can have an AND condition. Any legal WHERE clause is
allowed.
Curious that earlier "Description" was a text field, whereas now it
appears a numeric field.
=dsum("[amounttend]","[receipts]","[description]=2 AND
[studentid]=" & forms![student account data].studentid)
-Tom.
>Unfortunately, none of the offered solutions are working for me
>
>I tried, on the subform, ....
>
>=dsum("[amounttend]","[receipts]","[description]=2")
>
>this sums ALL records meeting the criteria in the receipts table and not
>just the membership record I'm working on. If there was a way to put an
>"AND" argument after the description qualifier, that might work? For
>instance...
>
>=dsum("[amounttend]","[receipts]","[description]=2" AND
>"[studentid]=forms![student account data].studentid)
>
>that might work.... is there a way?
>
>
>
>"David Grist"
>news:FxQWi.71$Cc.56@trndny09...
>> Hello,
>>
>> Any help would be appreciated!!!
>>
>> I need to sum AMOUNTTEND on a receipts subform that meet a certain
>> criteria.
>>
>> It has to be something like.
>>
>> =sum([amounttend]) where [description]="tour installment"
>>
>> in other words, I want to total only the amounts when the description
>> field = "tour installment"
>>
>> Can anyone help with the correct syntax?
>>
>> Thanks,
>>
>> Dave
>>
>>
>
>
Yes, the description was a number not text. Sorry for the bit of confusion.
"Tom van Stiphout"
news:8ejpi31vn4bj42dudp9pnlr2cd7s0nu9dc@4ax.com...
> On Sat, 03 Nov 2007 19:31:52 GMT, "David Grist"
> wrote:
>
> Of course you can have an AND condition. Any legal WHERE clause is
> allowed.
> Curious that earlier "Description" was a text field, whereas now it
> appears a numeric field.
> =dsum("[amounttend]","[receipts]","[description]=2 AND
> [studentid]=" & forms![student account data].studentid)
>
> -Tom.
>
>
>>Unfortunately, none of the offered solutions are working for me
>>
>>I tried, on the subform, ....
>>
>>=dsum("[amounttend]","[receipts]","[description]=2")
>>
>>this sums ALL records meeting the criteria in the receipts table and not
>>just the membership record I'm working on. If there was a way to put an
>>"AND" argument after the description qualifier, that might work? For
>>instance...
>>
>>=dsum("[amounttend]","[receipts]","[description]=2" AND
>>"[studentid]=forms![student account data].studentid)
>>
>>that might work.... is there a way?
>>
>>
>>
>>"David Grist"
>>news:FxQWi.71$Cc.56@trndny09...
>>> Hello,
>>>
>>> Any help would be appreciated!!!
>>>
>>> I need to sum AMOUNTTEND on a receipts subform that meet a certain
>>> criteria.
>>>
>>> It has to be something like.
>>>
>>> =sum([amounttend]) where [description]="tour installment"
>>>
>>> in other words, I want to total only the amounts when the description
>>> field = "tour installment"
>>>
>>> Can anyone help with the correct syntax?
>>>
>>> Thanks,
>>>
>>> Dave
>>>
>>>
>>
>>
Tom,
This is what I now have and it's still totalling all receipts in a records
of the subform.
=DSum("[amounttend]","[receipts]","Forms![Student Account
Data].[StudentID]=[StudentID]" And "[description]=2")
I want just the total of AMOUNTTEND for the current student and where the
description is equal to 2.
Can you offer any suggestions?
"Tom van Stiphout"
news:8ejpi31vn4bj42dudp9pnlr2cd7s0nu9dc@4ax.com...
> On Sat, 03 Nov 2007 19:31:52 GMT, "David Grist"
> wrote:
>
> Of course you can have an AND condition. Any legal WHERE clause is
> allowed.
> Curious that earlier "Description" was a text field, whereas now it
> appears a numeric field.
> =dsum("[amounttend]","[receipts]","[description]=2 AND
> [studentid]=" & forms![student account data].studentid)
>
> -Tom.
>
>
>>Unfortunately, none of the offered solutions are working for me
>>
>>I tried, on the subform, ....
>>
>>=dsum("[amounttend]","[receipts]","[description]=2")
>>
>>this sums ALL records meeting the criteria in the receipts table and not
>>just the membership record I'm working on. If there was a way to put an
>>"AND" argument after the description qualifier, that might work? For
>>instance...
>>
>>=dsum("[amounttend]","[receipts]","[description]=2" AND
>>"[studentid]=forms![student account data].studentid)
>>
>>that might work.... is there a way?
>>
>>
>>
>>"David Grist"
>>news:FxQWi.71$Cc.56@trndny09...
>>> Hello,
>>>
>>> Any help would be appreciated!!!
>>>
>>> I need to sum AMOUNTTEND on a receipts subform that meet a certain
>>> criteria.
>>>
>>> It has to be something like.
>>>
>>> =sum([amounttend]) where [description]="tour installment"
>>>
>>> in other words, I want to total only the amounts when the description
>>> field = "tour installment"
>>>
>>> Can anyone help with the correct syntax?
>>>
>>> Thanks,
>>>
>>> Dave
>>>
>>>
>>
>>
David Grist wrote:
> Tom,
>
> This is what I now have and it's still totalling all receipts in a records
> of the subform.
>
> =DSum("[amounttend]","[receipts]","Forms![Student Account
> Data].[StudentID]=[StudentID]" And "[description]=2")
>
> I want just the total of AMOUNTTEND for the current student and where the
> description is equal to 2.
>
> Can you offer any suggestions?
>
Maybe build a Totals query that breaks/groups on StudentID or whatever.
Then in the subform use that query and link the StudentID to StudentID
in the main form.
>
> "Tom van Stiphout"
> news:8ejpi31vn4bj42dudp9pnlr2cd7s0nu9dc@4ax.com...
>
>>On Sat, 03 Nov 2007 19:31:52 GMT, "David Grist"
>>wrote:
>>
>>Of course you can have an AND condition. Any legal WHERE clause is
>>allowed.
>>Curious that earlier "Description" was a text field, whereas now it
>>appears a numeric field.
>>=dsum("[amounttend]","[receipts]","[description]=2 AND
>>[studentid]=" & forms![student account data].studentid)
>>
>>-Tom.
>>
>>
>>
>>>Unfortunately, none of the offered solutions are working for me
>>>
>>>I tried, on the subform, ....
>>>
>>>=dsum("[amounttend]","[receipts]","[description]=2")
>>>
>>>this sums ALL records meeting the criteria in the receipts table and not
>>>just the membership record I'm working on. If there was a way to put an
>>>"AND" argument after the description qualifier, that might work? For
>>>instance...
>>>
>>>=dsum("[amounttend]","[receipts]","[description]=2" AND
>>>"[studentid]=forms![student account data].studentid)
>>>
>>>that might work.... is there a way?
>>>
>>>
>>>
>>>"David Grist"
>>>news:FxQWi.71$Cc.56@trndny09...
>>>
>>>>Hello,
>>>>
>>>>Any help would be appreciated!!!
>>>>
>>>>I need to sum AMOUNTTEND on a receipts subform that meet a certain
>>>>criteria.
>>>>
>>>>It has to be something like.
>>>>
>>>>=sum([amounttend]) where [description]="tour installment"
>>>>
>>>>in other words, I want to total only the amounts when the description
>>>>field = "tour installment"
>>>>
>>>>Can anyone help with the correct syntax?
>>>>
>>>>Thanks,
>>>>
>>>>Dave
>>>>
>>>>
>>>
>>>
>
>
"David Grist"
news:TJ4Xi.188$FO.59@trndny01:
> Tom,
>
> This is what I now have and it's still totalling all receipts in a
> records of the subform.
>
> =DSum("[amounttend]","[receipts]","Forms![Student Account
> Data].[StudentID]=[StudentID]" And "[description]=2")
>
> I want just the total of AMOUNTTEND for the current student and
> where the description is equal to 2.
>
> Can you offer any suggestions?
>
The quotation marks around your criteria are misplaced. Try
=DSum("[amounttend]","[receipts]","[StudentID]= " & Forms![Student
Account Data].[StudentID] & " And [description]=2")
Where studentID is a number or .
=DSum("[amounttend]","[receipts]","[StudentID]= """ & Forms![Student
Account Data].[StudentID] & """ And [description]=2")
if it is a text field.
>
> "Tom van Stiphout"
> news:8ejpi31vn4bj42dudp9pnlr2cd7s0nu9dc@4ax.com...
>> On Sat, 03 Nov 2007 19:31:52 GMT, "David Grist"
>>
>>
>> Of course you can have an AND condition. Any legal WHERE clause
>> is allowed.
>> Curious that earlier "Description" was a text field, whereas now
>> it appears a numeric field.
>> =dsum("[amounttend]","[receipts]","[description]=2 AND
>> [studentid]=" & forms![student account data].studentid)
>>
>> -Tom.
>>
>>
>>>Unfortunately, none of the offered solutions are working for me
>>>
>>>I tried, on the subform, ....
>>>
>>>=dsum("[amounttend]","[receipts]","[description]=2")
>>>
>>>this sums ALL records meeting the criteria in the receipts table
>>>and not just the membership record I'm working on. If there was
>>>a way to put an "AND" argument after the description qualifier,
>>>that might work? For instance...
>>>
>>>=dsum("[amounttend]","[receipts]","[description]=2" AND
>>>"[studentid]=forms![student account data].studentid)
>>>
>>>that might work.... is there a way?
>>>
>>>
>>>
>>>"David Grist"
>>>news:FxQWi.71$Cc.56@trndny09...
>>>> Hello,
>>>>
>>>> Any help would be appreciated!!!
>>>>
>>>> I need to sum AMOUNTTEND on a receipts subform that meet a
>>>> certain criteria.
>>>>
>>>> It has to be something like.
>>>>
>>>> =sum([amounttend]) where [description]="tour installment"
>>>>
>>>> in other words, I want to total only the amounts when the
>>>> description field = "tour installment"
>>>>
>>>> Can anyone help with the correct syntax?
>>>>
>>>> Thanks,
>>>>
>>>> Dave
>>>>
>>>>
>>>
>>>
>
>
>
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.com
if the subform is only showing records for the "current" student, then the
expression i gave you should work. where is the control that you want to
show the total?
hth
"David Grist"
news:TJ4Xi.188$FO.59@trndny01...
> Tom,
>
> This is what I now have and it's still totalling all receipts in a records
> of the subform.
>
> =DSum("[amounttend]","[receipts]","Forms![Student Account
> Data].[StudentID]=[StudentID]" And "[description]=2")
>
> I want just the total of AMOUNTTEND for the current student and where the
> description is equal to 2.
>
> Can you offer any suggestions?
>
>
> "Tom van Stiphout"
> news:8ejpi31vn4bj42dudp9pnlr2cd7s0nu9dc@4ax.com...
> > On Sat, 03 Nov 2007 19:31:52 GMT, "David Grist"
> > wrote:
> >
> > Of course you can have an AND condition. Any legal WHERE clause is
> > allowed.
> > Curious that earlier "Description" was a text field, whereas now it
> > appears a numeric field.
> > =dsum("[amounttend]","[receipts]","[description]=2 AND
> > [studentid]=" & forms![student account data].studentid)
> >
> > -Tom.
> >
> >
> >>Unfortunately, none of the offered solutions are working for me
> >>
> >>I tried, on the subform, ....
> >>
> >>=dsum("[amounttend]","[receipts]","[description]=2")
> >>
> >>this sums ALL records meeting the criteria in the receipts table and not
> >>just the membership record I'm working on. If there was a way to put an
> >>"AND" argument after the description qualifier, that might work? For
> >>instance...
> >>
> >>=dsum("[amounttend]","[receipts]","[description]=2" AND
> >>"[studentid]=forms![student account data].studentid)
> >>
> >>that might work.... is there a way?
> >>
> >>
> >>
> >>"David Grist"
> >>news:FxQWi.71$Cc.56@trndny09...
> >>> Hello,
> >>>
> >>> Any help would be appreciated!!!
> >>>
> >>> I need to sum AMOUNTTEND on a receipts subform that meet a certain
> >>> criteria.
> >>>
> >>> It has to be something like.
> >>>
> >>> =sum([amounttend]) where [description]="tour installment"
> >>>
> >>> in other words, I want to total only the amounts when the description
> >>> field = "tour installment"
> >>>
> >>> Can anyone help with the correct syntax?
> >>>
> >>> Thanks,
> >>>
> >>> Dave
> >>>
> >>>
> >>
> >>
>
>
Tina,
This worked perfectly, as a matter of fact I created fields for all the
remaining charges and can now display them as well.
Thanks for your help!
"tina"
news:op6Xi.325795$ax1.94423@bgtnsc05-news.ops.worldnet.att.n et...
> if the subform is only showing records for the "current" student, then the
> expression i gave you should work. where is the control that you want to
> show the total?
>
> hth
>
>
> "David Grist"
> news:TJ4Xi.188$FO.59@trndny01...
>> Tom,
>>
>> This is what I now have and it's still totalling all receipts in a
>> records
>> of the subform.
>>
>> =DSum("[amounttend]","[receipts]","Forms![Student Account
>> Data].[StudentID]=[StudentID]" And "[description]=2")
>>
>> I want just the total of AMOUNTTEND for the current student and where the
>> description is equal to 2.
>>
>> Can you offer any suggestions?
>>
>>
>> "Tom van Stiphout"
>> news:8ejpi31vn4bj42dudp9pnlr2cd7s0nu9dc@4ax.com...
>> > On Sat, 03 Nov 2007 19:31:52 GMT, "David Grist"
>> > wrote:
>> >
>> > Of course you can have an AND condition. Any legal WHERE clause is
>> > allowed.
>> > Curious that earlier "Description" was a text field, whereas now it
>> > appears a numeric field.
>> > =dsum("[amounttend]","[receipts]","[description]=2 AND
>> > [studentid]=" & forms![student account data].studentid)
>> >
>> > -Tom.
>> >
>> >
>> >>Unfortunately, none of the offered solutions are working for me
>> >>
>> >>I tried, on the subform, ....
>> >>
>> >>=dsum("[amounttend]","[receipts]","[description]=2")
>> >>
>> >>this sums ALL records meeting the criteria in the receipts table and
>> >>not
>> >>just the membership record I'm working on. If there was a way to put
>> >>an
>> >>"AND" argument after the description qualifier, that might work? For
>> >>instance...
>> >>
>> >>=dsum("[amounttend]","[receipts]","[description]=2" AND
>> >>"[studentid]=forms![student account data].studentid)
>> >>
>> >>that might work.... is there a way?
>> >>
>> >>
>> >>
>> >>"David Grist"
>> >>news:FxQWi.71$Cc.56@trndny09...
>> >>> Hello,
>> >>>
>> >>> Any help would be appreciated!!!
>> >>>
>> >>> I need to sum AMOUNTTEND on a receipts subform that meet a certain
>> >>> criteria.
>> >>>
>> >>> It has to be something like.
>> >>>
>> >>> =sum([amounttend]) where [description]="tour installment"
>> >>>
>> >>> in other words, I want to total only the amounts when the description
>> >>> field = "tour installment"
>> >>>
>> >>> Can anyone help with the correct syntax?
>> >>>
>> >>> Thanks,
>> >>>
>> >>> Dave
>> >>>
>> >>>
>> >>
>> >>
>>
>>
>
>
Tom,
Although your idea was the same as mine, it kept totalling the entire table
instead of the single record, but, another user was able to fix my issue.
Thanks for all your efforts!
- Dave
"Tom van Stiphout"
news:8ejpi31vn4bj42dudp9pnlr2cd7s0nu9dc@4ax.com...
> On Sat, 03 Nov 2007 19:31:52 GMT, "David Grist"
> wrote:
>
> Of course you can have an AND condition. Any legal WHERE clause is
> allowed.
> Curious that earlier "Description" was a text field, whereas now it
> appears a numeric field.
> =dsum("[amounttend]","[receipts]","[description]=2 AND
> [studentid]=" & forms![student account data].studentid)
>
> -Tom.
>
>
>>Unfortunately, none of the offered solutions are working for me
>>
>>I tried, on the subform, ....
>>
>>=dsum("[amounttend]","[receipts]","[description]=2")
>>
>>this sums ALL records meeting the criteria in the receipts table and not
>>just the membership record I'm working on. If there was a way to put an
>>"AND" argument after the description qualifier, that might work? For
>>instance...
>>
>>=dsum("[amounttend]","[receipts]","[description]=2" AND
>>"[studentid]=forms![student account data].studentid)
>>
>>that might work.... is there a way?
>>
>>
>>
>>"David Grist"
>>news:FxQWi.71$Cc.56@trndny09...
>>> Hello,
>>>
>>> Any help would be appreciated!!!
>>>
>>> I need to sum AMOUNTTEND on a receipts subform that meet a certain
>>> criteria.
>>>
>>> It has to be something like.
>>>
>>> =sum([amounttend]) where [description]="tour installment"
>>>
>>> in other words, I want to total only the amounts when the description
>>> field = "tour installment"
>>>
>>> Can anyone help with the correct syntax?
>>>
>>> Thanks,
>>>
>>> Dave
>>>
>>>
>>
>>
you're welcome :)
"David Grist"
news:3s7Xi.920$lx.211@trndny05...
> Tina,
>
> This worked perfectly, as a matter of fact I created fields for all the
> remaining charges and can now display them as well.
>
> Thanks for your help!
>
> "tina"
> news:op6Xi.325795$ax1.94423@bgtnsc05-news.ops.worldnet.att.n et...
> > if the subform is only showing records for the "current" student, then
the
> > expression i gave you should work. where is the control that you want to
> > show the total?
> >
> > hth
> >
> >
> > "David Grist"
> > news:TJ4Xi.188$FO.59@trndny01...
> >> Tom,
> >>
> >> This is what I now have and it's still totalling all receipts in a
> >> records
> >> of the subform.
> >>
> >> =DSum("[amounttend]","[receipts]","Forms![Student Account
> >> Data].[StudentID]=[StudentID]" And "[description]=2")
> >>
> >> I want just the total of AMOUNTTEND for the current student and where
the
> >> description is equal to 2.
> >>
> >> Can you offer any suggestions?
> >>
> >>
> >> "Tom van Stiphout"
> >> news:8ejpi31vn4bj42dudp9pnlr2cd7s0nu9dc@4ax.com...
> >> > On Sat, 03 Nov 2007 19:31:52 GMT, "David Grist"
> >> > wrote:
> >> >
> >> > Of course you can have an AND condition. Any legal WHERE clause is
> >> > allowed.
> >> > Curious that earlier "Description" was a text field, whereas now it
> >> > appears a numeric field.
> >> > =dsum("[amounttend]","[receipts]","[description]=2 AND
> >> > [studentid]=" & forms![student account data].studentid)
> >> >
> >> > -Tom.
> >> >
> >> >
> >> >>Unfortunately, none of the offered solutions are working for me
> >> >>
> >> >>I tried, on the subform, ....
> >> >>
> >> >>=dsum("[amounttend]","[receipts]","[description]=2")
> >> >>
> >> >>this sums ALL records meeting the criteria in the receipts table and
> >> >>not
> >> >>just the membership record I'm working on. If there was a way to put
> >> >>an
> >> >>"AND" argument after the description qualifier, that might work? For
> >> >>instance...
> >> >>
> >> >>=dsum("[amounttend]","[receipts]","[description]=2" AND
> >> >>"[studentid]=forms![student account data].studentid)
> >> >>
> >> >>that might work.... is there a way?
> >> >>
> >> >>
> >> >>
> >> >>"David Grist"
> >> >>news:FxQWi.71$Cc.56@trndny09...
> >> >>> Hello,
> >> >>>
> >> >>> Any help would be appreciated!!!
> >> >>>
> >> >>> I need to sum AMOUNTTEND on a receipts subform that meet a certain
> >> >>> criteria.
> >> >>>
> >> >>> It has to be something like.
> >> >>>
> >> >>> =sum([amounttend]) where [description]="tour installment"
> >> >>>
> >> >>> in other words, I want to total only the amounts when the
description
> >> >>> field = "tour installment"
> >> >>>
> >> >>> Can anyone help with the correct syntax?
> >> >>>
> >> >>> Thanks,
> >> >>>
> >> >>> Dave
> >> >>>
> >> >>>
> >> >>
> >> >>
> >>
> >>
> >
> >
>
>