Problems with Calculation

[Vj]

Guys,

Past few days I'm Struggling with Calculation of Total, based on the Type of
Holder.

formula as follows

SumI= IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding])))

SumP= IIf([TypeofHolding].[Value]="P",IIf([%ofholding]>4.9999,([%ofholding])))


Holder Type % Holding SumI SumP TotI
TotP Total(I&P)

P 4
Error Error
I 6 6
I 8 8
P 12
12

For TotI I tried different formulas, I am getting error or Blank cell.
TotI= Sum([SumI]
TotI=Sum(Nz(SumI))
Total= CDbl(Sum([SumI]))
TotI=Dsum("SumI", "TableName", "CompanyID=" & CompnayID")

Total(I&P)=sum([TotI])+Sum([TotP])

CompanyID is Unique, for each company it has different Share Holder's
All the above fields, Datatype's are Double.

Please Help me out, Thanks
Vj

 

[Stefan Hoffmann]

hi,

Guys,

Not only guys here...

Past few days I'm Struggling with Calculation of Total, based on the Type of
Holder.
formula as follows
SumI= IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding])))

Were is the False part of IIf:

IIf(Condition, True-Part, False-Part)

mfG
--> stefan <--

 

[NickOconell]

hi,

Guys,

Not only guys here...

Past few days I'm Struggling with Calculation of Total, based on the Type of
Holder.
formula as follows
SumI= IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding])))

Were is the False part of IIf:

IIf(Condition, True-Part, False-Part)

mfG
--> stefan <--

Stefan,

Earlier I tried false part with 0, But didnt work
SumI= IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding]),
0),0)

 

[VJ]

Guys,

Past few days I'm Struggling with Calculation of Total, based on the Type of

Holder.

formula as follows

SumI= IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding]),0),0)

SumP= IIf([TypeofHolding].[Value]="P",IIf([%ofholding]>4.9999,([%ofholding]),0),0)

Holder Type % Holding SumI SumP TotI TotP Total(I&P)

P 4
Error Error
I 6 6
I 8 8
P 12 12


For TotI I tried different formulas, I am getting error or Blank cell.
TotI= Sum([SumI]
TotI=Sum(Nz(SumI))
Total= CDbl(Sum([SumI]))
TotI=Dsum("SumI", "TableName", "CompanyID=" & CompnayID")

Total(I&P)=sum([TotI])+Sum([TotP])

CompanyID is Unique, for each company it has different Share Holder's
All the above fields, Datatype's are Double.

Please Help me out, Thanks

 

[Stefan Hoffmann]

hi,

Holder.
formula as follows
SumI= IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding])))

Were is the False part of IIf:

IIf(Condition, True-Part, False-Part)

Earlier I tried false part with 0, But didnt work
SumI= IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding]),
0),0)

Where do you use these formulas? In VBA code or as control source?

mfG
--> stefan <--

 

[NickOconell]

I use this formulas in: Control Source

hi,
[quoted text clipped - 5 lines]

SumI= IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding]),
0),0)

Where do you use these formulas? In VBA code or as control source?

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Nick,

I use this formulas in: Control Source

formula as follows

[quoted text clipped - 5 lines]

SumI= IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding]),
0),0)

Where do you use these formulas? In VBA code or as control source?

This formula as far as i understand gets you the correct result and only
calculating the total results in an error:

For TotI I tried different formulas, I am getting error or Blank cell.
TotI=Sum(Nz(SumI))

You can't use Sum() on your controls. Sum() only works for table fields
and VBA functions.

So

TotI=Sum( _
IIf([TypeofHolding]="I",IIf([%ofholding]>4.9999,([%ofholding]),0),0) _
)

should work.


mfG
--> stefan <--

 

[NickOconell]

Dear Stefan,
Thanks for ut help

I tried wht you said, but I am getting #Error.

its not working

hi Nick,

I use this formulas in: Control Source

formula as follows
[quoted text clipped - 5 lines]
SumI= IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding]),
0),0)
Where do you use these formulas? In VBA code or as control source?

This formula as far as i understand gets you the correct result and only
calculating the total results in an error:

For TotI I tried different formulas, I am getting error or Blank cell.
TotI=Sum(Nz(SumI))

You can't use Sum() on your controls. Sum() only works for table fields
and VBA functions.

So

TotI=Sum( _
IIf([TypeofHolding]="I",IIf([%ofholding]>4.9999,([%ofholding]),0),0) _
)

should work.

mfG
--> stefan <--

 

[BruceM]

I'm getting a bit lost here, but I have a few observations about this
expression:
SumI=
IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding]),0),0)

1) In the control source for a text box, start with the = sign
2) If TypeofHolding is a text box, Value is assumed as the property; you
don't need to specify. If it is a field, I don't think the Value property
applies. In any case, a property is not enclosed in brackets:
[TypeofHolding].Value
3) A field name should contain only alphanumeric characters, hyphens, and
underscores. Access may be able to handle the % sign, but it could cause
problems with coding. I don't think that is the problem here, but I'll
mention it anyhow.
4) In general you have overused parentheses. In particular, all of the
closing parentheses in a nested IIf statement go at the end. However, there
is no need for the nested IIf. Try using AND:

= IIf([TypeofHolding]= "I" And [%ofholding] > 4.9999, [%ofholding] ,0)
or maybe:
= IIf([TypeofHolding]= "I" And [%ofholding] >= 5, [%ofholding] ,0)

This expression appears as the control source of an unbound text box. If
TypeofHolding and %ofholding are fields, it could also appear as a
calculated query field. In query design view, at the top of a blank column:
MyField: IIf([TypeofHolding]= "I" And [%ofholding] > 4.9999, [%ofholding]
,0)

Base the form on the query, and bind a text box on the form to MyField. Use
whatever field name you choose.

Dear Stefan,
Thanks for ut help

I tried wht you said, but I am getting #Error.

its not working

hi Nick,

I use this formulas in: Control Source
formula as follows
[quoted text clipped - 5 lines]
SumI=
IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding]),
0),0)
Where do you use these formulas? In VBA code or as control source?

This formula as far as i understand gets you the correct result and only
calculating the total results in an error:

For TotI I tried different formulas, I am getting error or Blank cell.
TotI=Sum(Nz(SumI))

You can't use Sum() on your controls. Sum() only works for table fields
and VBA functions.

So

TotI=Sum( _
IIf([TypeofHolding]="I",IIf([%ofholding]>4.9999,([%ofholding]),0),0) _
)

should work.

mfG
--> stefan <--

 

[NickOconell]

Hello Bruce,

SumI=IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding]),0)
,0) this one is working fine

I have problem for calculating TotI
I have Used following formulas

TotI= Sum([SumI]
TotI=Sum(Nz(SumI))
TotI= CDbl(Sum([SumI]))
TotI=Dsum("SumI", "TableName", "CompanyID=" & CompnayID")
TotI=Sum(IIf([TypeofHolding]="I",IIf([%ofholding]>4.9999,([%ofholding]),0),0))

I'm getting a bit lost here, but I have a few observations about this
expression:
SumI=
IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding]),0),0)

1) In the control source for a text box, start with the = sign
2) If TypeofHolding is a text box, Value is assumed as the property; you
don't need to specify. If it is a field, I don't think the Value property
applies. In any case, a property is not enclosed in brackets:
[TypeofHolding].Value
3) A field name should contain only alphanumeric characters, hyphens, and
underscores. Access may be able to handle the % sign, but it could cause
problems with coding. I don't think that is the problem here, but I'll
mention it anyhow.
4) In general you have overused parentheses. In particular, all of the
closing parentheses in a nested IIf statement go at the end. However, there
is no need for the nested IIf. Try using AND:

= IIf([TypeofHolding]= "I" And [%ofholding] > 4.9999, [%ofholding] ,0)
or maybe:
= IIf([TypeofHolding]= "I" And [%ofholding] >= 5, [%ofholding] ,0)

This expression appears as the control source of an unbound text box. If
TypeofHolding and %ofholding are fields, it could also appear as a
calculated query field. In query design view, at the top of a blank column:
MyField: IIf([TypeofHolding]= "I" And [%ofholding] > 4.9999, [%ofholding]
,0)

Base the form on the query, and bind a text box on the form to MyField. Use
whatever field name you choose.

Dear Stefan,
Thanks for ut help

[quoted text clipped - 31 lines]

 

[BruceM]

Additional questions inline.

Hello Bruce,

SumI=IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding]),0)
,0) this one is working fine

Where does this expression appear? What is SumI? Is TypeofHolding a field
or a control? How about %ofholding? What is the advantage of complicating
the expression?

I have problem for calculating TotI
I have Used following formulas

Where have you used these formulas? What is TotI?

TotI= Sum([SumI]
TotI=Sum(Nz(SumI))
TotI= CDbl(Sum([SumI]))
TotI=Dsum("SumI", "TableName", "CompanyID=" & CompnayID")
TotI=Sum(IIf([TypeofHolding]="I",IIf([%ofholding]>4.9999,([%ofholding]),0),0))

I'm getting a bit lost here, but I have a few observations about this
expression:
SumI=
IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding]),0),0)

1) In the control source for a text box, start with the = sign
2) If TypeofHolding is a text box, Value is assumed as the property; you
don't need to specify. If it is a field, I don't think the Value property
applies. In any case, a property is not enclosed in brackets:
[TypeofHolding].Value
3) A field name should contain only alphanumeric characters, hyphens, and
underscores. Access may be able to handle the % sign, but it could cause
problems with coding. I don't think that is the problem here, but I'll
mention it anyhow.
4) In general you have overused parentheses. In particular, all of the
closing parentheses in a nested IIf statement go at the end. However,
there
is no need for the nested IIf. Try using AND:

= IIf([TypeofHolding]= "I" And [%ofholding] > 4.9999, [%ofholding] ,0)
or maybe:
= IIf([TypeofHolding]= "I" And [%ofholding] >= 5, [%ofholding] ,0)

This expression appears as the control source of an unbound text box. If
TypeofHolding and %ofholding are fields, it could also appear as a
calculated query field. In query design view, at the top of a blank
column:
MyField: IIf([TypeofHolding]= "I" And [%ofholding] > 4.9999,
[%ofholding]
,0)

Base the form on the query, and bind a text box on the form to MyField.
Use
whatever field name you choose.

Dear Stefan,
Thanks for ut help

[quoted text clipped - 31 lines]

mfG
--> stefan <--

 

[NickOconell via AccessMonster.com]

Expression appears in Control box

SumI is calculated based on type of holding and % of Holding>5

and then I have to calculate Total= sum([SumI])
SumI=IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding]),0)
,0) I'm getting values for this
condition.

But I couldnt calculate Total, I hope you got the problem. Expression is
working fine.

Thanks for ur help

Additional questions inline.

Hello Bruce,

SumI=IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding]),0)
,0) this one is working fine

Where does this expression appear? What is SumI? Is TypeofHolding a field
or a control? How about %ofholding? What is the advantage of complicating
the expression?

I have problem for calculating TotI
I have Used following formulas

Where have you used these formulas? What is TotI?

TotI= Sum([SumI]
TotI=Sum(Nz(SumI))

[quoted text clipped - 42 lines]

 

[BruceM]

I assume that by "Control Box" you mean the control source of an unbound
text box.

My other questions remain unanswered, so I can only guess what you are
trying to do. If you have a number field [YourNumberField] in a table, and
a form is bound to that table, you can sum this in the control source of an
unbound text box in the form footer:
=Sum([YourNumberField])

However, if you want to sum a calculation, you need to put the calculation
into the control source of the text box in the footer:
=Sum(IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding]),0)
,0))

Two different people in this thread have asked the same questions several
times. If you need more help with this, start by reading the replies you
have already received and answering the questions.

Expression appears in Control box

SumI is calculated based on type of holding and % of Holding>5

and then I have to calculate Total= sum([SumI])
SumI=IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding]),0)
,0) I'm getting values for this
condition.

But I couldnt calculate Total, I hope you got the problem. Expression
is
working fine.

Thanks for ur help

Additional questions inline.

Hello Bruce,

SumI=IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>4.9999,([%ofholding]),0)
,0) this one is working fine

Where does this expression appear? What is SumI? Is TypeofHolding a
field
or a control? How about %ofholding? What is the advantage of
complicating
the expression?

I have problem for calculating TotI
I have Used following formulas

Where have you used these formulas? What is TotI?

TotI= Sum([SumI]
TotI=Sum(Nz(SumI))

[quoted text clipped - 42 lines]

mfG
--> stefan <--