Forumulas on Report/ (1 Viewer)

[kacey8]

I have a report which provides me with three totals.

Total 1
Total 2
Total 3

Total 1 is an easy one, it uses a count fields

=Count([FIELD 1])

Total 2 is another eays one, it is a sum of all the numbers

=Sum([FIELD 2])

Total 3 is a bit harder, it is the sum of all the ticks in a yes/no box. for this I use

=Sum(IIf([FIELD 3]=True,1,0))

Now... I use these 3 totals to work out three more values,

Value 1 is Total 1/Total 2 for this I use

=(Count([FIELD 1]))/(Sum([FIELD 2]))

Value 2 is where I am stuck at. It should be Total 2/Total 3
I thought I could try this, but it didn't work

=(Sum([FIELD 2]))/(Sum(IIf([FIELD 3]=True,1,0)))

However, it doesn't seem to like my work (I get an error when trying to run the report)

Data type Mismatch in criteria expression

Any help would be so helpful.

 

[BlueIshDan]

Try DSum

 

[kacey8]

Thanks... apparently thought it's simpler than I thought, to get the sum of the Yes/No Box I can just use

 =-Sum([Field 3])

and it converts the -result from the yes/no to a possitive, then I just used

 =(Sum([Field 2])/(-Sum([Field 3])

and it worked amazingly

Thanks for the suggestion though.