Multiple IIF Conditions

[vandude2023]

Have the existing nested if statement and it works fine:
SELECT TimeStart.EmpNo, TimeStart.Name, TimeStart.UN, TimeStart.BU, TimeStart.Object, TimeStart.Subsidiary, TimeStart.HomeBU, TimeStart.PBDA, TimeStart.DistPay, TimeStart.GrossPay, TimeStart.Subledger, TimeStart.SubledgerType, TimeStart.Batch, IIf([Object]="51036" Or [Object]="51050" Or [Object]="51051" Or [Object]="51052" Or [Object]="51053" Or [Object]="51054" Or [Object]="51056" Or [Object]="51073" Or [Object]="51075",[BU] & "." & [Object] & ".100000",
-- START
IIf([BU]="1",[HomeBU] & ".50075.100000",
-- END
IIf(Len(Trim([Subledger]))="0",[BU] & ".50075.100000","\" & [Subledger] & ".50075"))) AS PostAcct, tblRetroRatesByUnion.RetroFactor, Round([GrossPay]*[RetroFactor],2) AS LDRetro
FROM TimeStart INNER JOIN tblRetroRatesByUnion ON TimeStart.UN = tblRetroRatesByUnion.UnionCode
ORDER BY TimeStart.BU, TimeStart.Object, TimeStart.Subsidiary;

There's a requirement to have those [BU]="1" AND TimeStart.PBDA in (501.503) AND TimeStart.UN = '0' to be "135.51075.100000" (see START END above).

Tried the following changes but didn't seem to work:
SELECT TimeStart.EmpNo, TimeStart.Name, TimeStart.UN, TimeStart.BU, TimeStart.Object, TimeStart.Subsidiary, TimeStart.HomeBU, TimeStart.PBDA, TimeStart.DistPay, TimeStart.GrossPay, TimeStart.Subledger, TimeStart.SubledgerType, TimeStart.Batch, IIf([Object]="51036" Or [Object]="51050" Or [Object]="51051" Or [Object]="51052" Or [Object]="51053" Or [Object]="51054" Or [Object]="51056" Or [Object]="51073" Or [Object]="51075",[BU] & "." & [Object] & ".100000",
-- START
IIf([BU]="1",[HomeBU] & ".50075.100000",
IIf([PBDA]="501" or [PBDA]="503",
IIf([UN]="0", "135.51075.100000",
-- END
IIf(Len(Trim([Subledger]))="0",[BU] & ".50075.100000","\" & [Subledger] & ".50075"))))) AS PostAcct, tblRetroRatesByUnion.RetroFactor, Round([GrossPay]*[RetroFactor],2) AS LDRetro
FROM TimeStart INNER JOIN tblRetroRatesByUnion ON TimeStart.UN = tblRetroRatesByUnion.UnionCode
ORDER BY TimeStart.BU, TimeStart.Object, TimeStart.Subsidiary;

Please let me know how to fix the syntax.
Any guidance would be greatly appreciated!

 

[theDBguy]

Hi. Welcome to AWF!

The IIf() statement always have a True and False parts. So, you'll need to make sure you provide the necessary arguments for each part and also ensure you have matching pairs of parens (). I can't tell you how it should looks since I don't understand what you SQL statement means. Sorry.

 

[arnelgp]

which SQL are you using?

 

[plog]

In SQL you get to nest 1 IIF inside another, more than that and its time to make a function in VBA.

I suggest you make a function in a module like so:

Public Function get_PostAcct(in_Object, in_BU, in_HomeBU, in_PBDA, in_UN, in_Subledger) AS String
  ' converts data to correct PostAcct value

Dim ret As String
ret="Error"
' return variable, initialy set to error, will be determined by logic below



' put existing logic here and set the variable ret based on it


get_PostAcct = ret
' returns value calculated for PostAcct

End Function

Then in a query you call it like so:

PostAcct: get_PostAcct(i[Object],[BU], [HomeBU], [PBDA], [UN], [Subledger]) AS String

However, based on the names of your fields I have 2 observations I suggest you look into and fix:

1. Name and Object are reserved words in Access and should not used as names. Change them by prefixing or suffixing them for what they are for (CustName, ObjectType, etc.)

2. Non-generic field names are a sign of poor normalization. You shouldn't have to understand the organization's jargon (HomeBU, PBDA, UN) to understand what a field represents based on its name. THis is a sign that you are assigning values which should be in the fields of the tables to the field names. In a sales database you would not have a fields called JohnsSales, DavesSales. Instead you would have a field called SAles for those values and also a field called SalesPerson and you would store John and Dave in there. I fear you've done that with those fields I mentioned above.

 

[June7]

Could definitely shorten expression with IN()

IIf([Object] IN("51036","51050","51051","51052","51053","51054","51056","51073","51075"), [BU] & "." & [Object] & ".100000",
Maybe even: IIf([Object] BETWEEN "51036" AND "51075")

IIf([PBDA] IN("501","503")

However, the logic is not clear to me.

You say AND in "requirement to have those [BU]="1" AND TimeStart.PBDA in (501.503) AND TimeStart.UN = '0' to be "135.51075.100000"
but show a different result when BU = 1.

Maybe:

IIf([Object] IN("51036","51050","51051","51052","51053","51054","51056","51073","51075"), [BU] & "." & [Object] & ".100000",
IIf([BU]="1" AND [PBDA] IN("501","503") AND [UN]="0", "135.51075.100000",
IIf([BU]="1", [HomeBU] & ".50075.100000",
IIf(Len(Trim([Subledger]))="0",[BU] & ".50075.100000", "\" & [Subledger] & ".50075")))) AS PostAcct

Are these fields all text type?

Agree, probably time to build a custom VBA function.

 

[Josef P.]

If many IIf have to be nested, one can ask whether an additional table or a further normalization level could help.