CODEWITHSUNDEEP
sqlms-accessiif
excelguy
excelguy

Reputation: 1624

Access, multiple iif functions in one

I am trying to come up with a new iif statement to have different logic, however I am getting 0's in my new table when I see records from 1 - Master that should have a balance.

  1. If 1 - Master = 1234 then use "Balance"
  2. If a record from 1 - Master <> 1234, then apply the isnull logic.
  3. if a record is null , then use avg factor * balance
  4. if a record is not null, then use factor * balance
IIf([1 - Master].rln='1234', [1 - Master].Balance, 
(IIf(IsNull([0 -  Month End Factors].Factor), [1 - Master].Balance*[0 -  Average Factors].[Avg Factor], [1 - Master].Balance*[0 -  Month End Factors].Factor)))  
AS [Final Balance]

Can anyone help with this? I am getting 0's after running this, perhaps my statement isnt correctly written.

Thanks.

Upvotes: 0

Views: 701

Answers (1)

June7
June7

Reputation: 21389

Don't see anything wrong with syntax but expression could be simpler. Consider:

IIf([1 - Master].rln='1234', [1 - Master].Balance, 
[1 - Master ].Balance * Nz([0 -  Month End Factors].Factor, [0 -  Average Factors].[Avg Factor])) 
AS [Final Balance]

Field names must be prefixed with table names only if multiple tables in query have same field names.

IIf([rln]='1234', [Balance], [Balance] * Nz([Factor], [Avg Factor])) AS [Final Balance]

IsNull() and Nz() are both VBA functions. To avoid calls to VBA in an SQL statement, use IS NULL.

IIf([rln]='1234', [Balance], [Balance] * IIf([Factor] IS NULL, [Avg Factor], [Factor])) AS [Final Balance]

If rln is number type field, don't use apostrophes around 1234.

Upvotes: 1

Related Questions