Calculate sum from Sub form (DataSheet View) column

[vbaInet]

I've gotten rid of some extra parens and spaced out some bits that were wrong:

=IIf([qrypayments]![CurrencyID] <> 3,

IIf(IsNull([currAmtRcvd]), Null, [Forms]![frmTempMain1]![currNonUAEPrem]-DSum("currAmtRcvd", "qrypayments", "[qrypayments]![CreditContID] = " & [Forms]![frmTempMain1]![CreditContID] & " AND PaymentsID <= " & [PaymentsID] & " AND [qrypayments]![CurrencyID] <> 3")),

IIf(IsNull([currAmtRcvd]), Null, [Forms]![frmTempMain1]![sfrmUAEMem].[Form]![currPremiuminAED]-DSum("currAmtRcvd", "qrypayments", "[qrypayments]![CreditContID] = " & [Forms]![frmTempMain1]![CreditContID] & " AND PaymentsID <= " & [PaymentsID] & " AND [qrypayments]![CurrencyID] = 3")))

What are the data types of these [CreditContID], [CurrencyID]?

 

[Shallo9]

I've gotten rid of some extra parens and spaced out some bits that were wrong:

=IIf([qrypayments]![CurrencyID] <> 3,
 
IIf(IsNull([currAmtRcvd]), Null, [Forms]![frmTempMain1]![currNonUAEPrem]-DSum("currAmtRcvd", "qrypayments", "[qrypayments]![CreditContID] = " & [Forms]![frmTempMain1]![CreditContID] & " AND PaymentsID <= " & [PaymentsID] & " AND [qrypayments]![CurrencyID] <> 3")),
 
IIf(IsNull([currAmtRcvd]), Null, [Forms]![frmTempMain1]![sfrmUAEMem].[Form]![currPremiuminAED]-DSum("currAmtRcvd", "qrypayments", "[qrypayments]![CreditContID] = " & [Forms]![frmTempMain1]![CreditContID] & " AND PaymentsID <= " & [PaymentsID] & " AND [qrypayments]![CurrencyID] = 3")))

What are the data types of these [CreditContID], [CurrencyID]?

They both are numbers CreditContID is foreign key for payments table and currencyID though displays the currency type but actually stores the ID from the currencyList table.


I think I've tried earlier exactly the way you've specified but with no success.

 

[vbaInet]

I think I've tried earlier exactly the way you've specified but with no success.

What I wrote is not the same thing as what you wrote. They may look the same but things were taking out and put in so copy and paste it and give a try first.

 

[Shallo9]

Hi I've tried pasting it but it displays #Name? I also tried the below but No success again,

=IIf(IsNull([currAmtRcvd]),Null,
IIf([qrypayments]![CurrencyID] <> 3,[Forms]![frmTempMain1]![currNonUAEPrem]-DSum("currAmtRcvd","qrypayments","[qrypayments]![CreditContID] = " & [Forms]![frmTempMain1]![CreditContID] & " AND PaymentsID <= " & [PaymentsID] & " AND [qrypayments]![CurrencyID] <> 3"),
[Forms]![frmTempMain1]![sfrmUAEMem].[Form]![currPremiuminAED]-DSum("currAmtRcvd", "qrypayments", "[qrypayments]![CreditContID] = " & [Forms]![frmTempMain1]![CreditContID] & " AND PaymentsID <= " & [PaymentsID] & " AND [qrypayments]![CurrencyID] = 3")))

 

[vbaInet]

It's complaining about your field references, in which case you could perform a trial and elimination method starting with this:

=IIf([qrypayments]![CurrencyID] <> 3,

Null,

IIf(IsNull([currAmtRcvd]), Null, [Forms]![frmTempMain1]![sfrmUAEMem].[Form]![currPremiuminAED]-DSum("currAmtRcvd", "qrypayments", "[qrypayments]![CreditContID] = " & [Forms]![frmTempMain1]![CreditContID] & " AND PaymentsID <= " & [PaymentsID] & " AND [qrypayments]![CurrencyID] = 3")))

But are you sure sfrmUAEMem is the name of the subform control or the name of the form inside the subform control?

 

[Shallo9]

It's complaining about your field references, in which case you could perform a trial and elimination method starting with this:

=IIf([qrypayments]![CurrencyID] <> 3,

Null,

IIf(IsNull([currAmtRcvd]), Null, [Forms]![frmTempMain1]![sfrmUAEMem].[Form]![currPremiuminAED]-DSum("currAmtRcvd", "qrypayments", "[qrypayments]![CreditContID] = " & [Forms]![frmTempMain1]![CreditContID] & " AND PaymentsID <= " & [PaymentsID] & " AND [qrypayments]![CurrencyID] = 3")))

But are you sure sfrmUAEMem is the name of the subform control or the name of the form inside the subform control?

As I said before both these parts the true part and the false part work absolutely fine when used on their own. Only when I use them both as the True block and false block they fail.
sfrmUAEMem is a subform and currPremiuminAED is a control in it.

The Dsum function value gets subtracted from currPremiuminAED value if currencyID is 3 else gets subtracted from NONUAEPremium from the frmTempMain1 form.

 

[vbaInet]

As I said before both these parts the true part and the false part work absolutely fine when used on their own. Only when I use them both as the True block and false block they fail.

If you have a better way of troubleshooting please feel free to use it.

sfrmUAEMem is a subform and currPremiuminAED is a control in it.

That still doesn't answer the question. A subform control is a control just like a textbox is a control. Is sfrmUAEMem the name of the control or the name of the subform. These are two different things.

 

[Shallo9]

That still doesn't answer the question. A subform control is a control just like a textbox is a control. Is sfrmUAEMem the name of the control or the name of the subform. These are two different things

sfrmUAEMem is the name of the sub form

 

[vbaInet]

If that's the name of the subform, you want to use the name of the subform control, not the name of the subform.

Upload your db let me have a look at what you're doing.

 

[Shallo9]

If that's the name of the subform, you want to use the name of the subform control, not the name of the subform.

Upload your db let me have a look at what you're doing.

Hi Yah (find it hard to refer you as vbaInet),

Tell you what??? its working....I just played around with the code and finally framed them right...Have a look at the refined code below.

=iif(IsNull([currAmtRcvd]),null,IIf([CurrencyID]=3,[Forms]![frmTempMain1]![sfrmUAEMem].[Form]![currPremiumin
AED]-DSum(" currAmtRcvd","qrypayments"," [qrypayments]![CreditContID] =" & 
[Forms]![frmTempMain1]![sfrmUAEMem].[Form]![CreditContID] & " AND PaymentsID 
<= " & [PaymentsID] & " AND [qrypayments]![CurrencyID] = 
3"),[Forms]![frmTempMain1]![currNonUAEPrem]-DSum(" 
currAmtRcvd","qrypayments"," [qrypayments]![CreditContID] =" & 
[Forms]![frmTempMain1]![CreditContID] & " AND PaymentsID <= " & [PaymentsID] 
& " AND [qrypayments]![CurrencyID] <> 3")))

All Happies
Thank you once again for your great help.

 

[vbaInet]

Good work Shallo9.

By the way, if the query qryPayments is not linked to any other table, you don't need the full reference, e.g. [qrypayments]![CreditContID]. You don't need the red bit.