Expression builder - if statement condition and Sum formula in a control source

[carissa7]

Hi all,



I have a form I build in access db with a control source where I want to write a formula in that control source based on a table called " Entity master"
The table has the following columns: child entity, parent entity, country of child, country of parent, and amounts

I want the if statement to say the following. If(EntityMaster.CountryofChild) = "US" AND if(entitymaster.CountryofParent = "US" then sum up only the negative amounts in the amounts columns.

Here is what I have below:

IIF([EntityMaster]).[ParentofJurisdictions]="United States",IIF([EntityMaster].[ChildOfJurisdiction]="United States",SUM([EntityMaster].[Amounts]))))

How do I specific to only sum negative amounts? also i'm getting a #name? error message on the form

.

Any help is appreciated, thank you.

[CJ_London]

try

SUM(IIF([EntityMaster]).[ParentofJurisdictions]="United States" AND [EntityMaster].[ChildOfJurisdiction]="United States" AND [EntityMaster].[Amounts]<0,[EntityMaster].[Amounts],0)

[carissa7]

hmmm it says the "expression you entered contains too many arguments" although syntax is better than mine haha, thoughts?

[June7]

Missing a closing paren. Parens, brackets, quotes, apostrophes must be in pairs when used as special characters and not literal text in strings.

Probably don't need the table prefix.

SUM(IIF([ParentofJurisdictions]="United States" AND [ChildOfJurisdiction]="United States" AND [Amounts]<0, [Amounts], 0))

Why the 's' at end of ParentofJurisdictions and not ChildOfJurisdiction? And maybe capitalize the 'O' in 'of'.

[carissa7]

Actually I tried this:

=Sum(Amounts * IIF(CountryofChild="US" And CountryofParent="US"  And Amounts<0,-1,0))

and it does sum the negative numbers, however it returns it with a positive. so it does -100 + -400 and returns a 500, any ideas?

[Micron]

I'd say that if Amounts is a negative number, then you're multiplying 2 negatives, which we know will create a positive. Your prior examples had no multiplication.

[June7]

Well, the negative numbers are multiplied by -1. A neg times a neg is a pos.

Why different sets of field names and values? In post 1, narrative shows one version and expression code shows another. Why confuse us?

[carissa7]

oh right ahhh ,okay thank you so much!!