Sum IIF in subform

[rproactive]

Hi- Could use some help with the subform Sum(IIF()) blues. I want to sum the values in a subform for only certain values.

My subform is bound to table M_SchedRecurring
My mainform is bound to table M_ReceiptSlip

My subform control is named subFS_SchedREcurringCONTROL
  Source Object:  FS_SchedRecurring
  Link Child:  SchedRecurringLeases_IDs	
        in table M_SchedRecurring
  Link Master:  ReceiptSlipLeases_IDs  
        in table M_ReceiptSlip
	

[SchedRecurringCategoryAmt] & [SchedRecurringCategoryName] are fields in table M_SchedRecurring

[cboSchedRecurringCategoryName] is control on subform that displays the [SchedRecurringCategoryName]

I would like to sum the values on subform bound to [SchedRecurringCategoryAmt]
iif the category name [SchedRecurringCategoryName] is 56.

I have tried the following 3 variations:

=Sum(IIf([b]56[/b]=56,SchedRecurringCategoryAmt],0))  
works , but 56=56 isn’t the condition I want

=Sum(IIf([b][cboSchedRecurringCategoryName][/b]=56,[SchedRecurringCategoryAmt],0))		#ERROR

=Sum(IIf([b][SchedRecurringCategoryName][/b]=56,[SchedRecurringCategoryAmt],0))		#ERROR

=Sum(IIf([b]”SchedRecurringCategoryName”[/b]=56,[SchedRecurringCategoryAmt],0))		#ERROR

thanks for any suggestions

 

[TheAceMan1]

How are ya rproactive . . .

In VBE help have a look at [blue]DSum[/blue] . . .

See Ya! . . . . . .

 

[rproactive]

Hi - was trying to do it in control without VB

any ideas on possible
syntax problem?

 

[TheAceMan1]

rproactive . . .

. . . and this:

[blue]=DSum("[SchedRecurringCategoryAmt]","[purple][b][i]TableName[/i][/b][/purple]","[SchedRecurringCategoryName]=56")[/blue]

Note: you need to do something about [purple]shortening your naming convention[/purple] or you'll spend too much time deciphering what you have. Imagine trying to read an SQL where [blue]]","[SchedRecurringCategoryName][/blue] & [blue][SchedRecurringCategoryAmt][/blue] occur 10 or more times . . . whew!

Example: SRCname, SRCamt . . .

[blue]=DSum("[SchedRecurringCategoryAmt]","[purple][b][i]TableName[/i][/b][/purple]","[SchedRecurringCategoryName]=56")
=DSum("[SRCamt]","[purple][b][i]TableName[/i][/b][/purple]","[SRCname]=56")[/blue]

. . . now thats easily read & ID'ed

For you (the programmer) the idea of naming convention is to make things easier to read and identify. Always make it easy for yourself!

See Ya! . . . . . .