Multiple iif statements (at least 15)

[rabbit2323]

In my query, I have following fields: PayTerm, InvDate, DateMature, ETD, ETA, and DueDate. Base on the payment term, I'm trying to have database auto-calculate the Due Date in the query. Then update the due date in the table. The problem is that I have many payment terms. I try to fit them all in one iif statement, but the database doesn't allow it. So I created 3 different queries to accomplish the task. Then I created update qry for each one to update the table. I prefer not to have multiple queries to calculate the due date. Do you have a solution where I can use just 1 query? Or maybe create a VBA code for it where I can use it in access form?

Below is my iif statement:

DueDate: IIf([DateMature] Is Not Null,[DateMature],
IIf([PayTerm]="SIGHT DP",[InvDate]+14,
IIf([PayTerm]="SIGHT LC",[InvDate]+14,
IIf([PayTerm]="SIGHT TT",[InvDate]+14,
IIf([PayTerm]="LC 60 Days After BL DATE",[ETD]+60,
IIf([PayTerm]="LC 90 Days After BL DATE",[ETD]+90,
IIf([PayTerm]="TT 0 Days After ARRIVAL",[ETA],
IIf([PayTerm]="TT 0 Days After CUSTOMS CLEARANCE",[ETA],
IIf([PayTerm]="TT 10 Days After BL DATE",[ETD]+10,
IIf([PayTerm]="TT 30 Days After BL DATE",[ETD]+30,
IIf([PayTerm]="TT 30 Days After INVOICE DATE",[InvDate]+30,
IIf([PayTerm]="TT 40 Days After BL DATE",[ETD]+40,
IIf([PayTerm]="TT 45 Days After BL DATE",[ETD]+45,
IIf([PayTerm]="TT 60 Days After BL DATE",[ETD]+60,
IIf([PayTerm]="20% DEPOSIT 80% TT UPON ARRIVAL",[ETA],
IIf([PayTerm]="30% DEPOSIT 70% TT UPON ARRIVAL",[ETA],
IIf([PayTerm]="NET 7 Days After Invoice Date",[InvDate]+7
)))))))))))))))))

 

[theDBguy]

Hi. Welcome to AWF!

Yes, creating a custom function would probably solve the problem. However, I think a better solution is a table-based one. That way, you can just simply join the tables in your query. Also, I wonder if there's an issue with your table structure. Can you show us what it looks like? Thanks.

 

[rabbit2323]

Hi. Welcome to AWF!

Yes, creating a custom function would probably solve the problem. However, I think a better solution is a table-based one. That way, you can just simply join the tables in your query. Also, I wonder if there's an issue with your table structure. Can you show us what it looks like? Thanks.

I have these fields in my table:

Field Name	Data Type	Description
ID	AutoNumber
Agent	Text	Sale Agent
CUST	Text	Customer Name
OrderID	Text	Sale order number
InvoiceNo.	Text	Invoice number
Sales	Currency	Sales Amount
PayTerm	Text	Payment Terms
ETD	Date/Time	Estimate Time of Departure
ETA	Date/Time	Estimate Time of Arrival
InvDate	Date/Time	Invoice Date
DateMature	Date/Time	Letter of Credit Maturity Date
DueDate	Date/Time	Estimate Due Date
PaidDate	Date/Time	Payment receive date

 

[theDBguy]

Can any of those be calculated? For example ETD, ETA, or DueDate? Just curious...

 

[rabbit2323]

Can any of those be calculated? For example ETD, ETA, or DueDate? Just curious...

Just the DueDate. Hence why I created the queries to calculate it.

The rest of the dates are entered.

 

[theDBguy]

Just the DueDate. Hence why I created the queries to calculate it.

The rest of the dates are entered.

In that case, you don't really need it in your table.

 

[theDBguy]

Just the DueDate. Hence why I created the queries to calculate it.

The rest of the dates are entered.

Also, are you saying there is no relationship between ETD and ETA? Just checking...

 

[rabbit2323]

Also, are you saying there is no relationship between ETD and ETA? Just checking...

Thats correct.

Perhaps this example will help. Base on PayTerm "Sight DP", due date should be same as InvDate.

Agent​	CUST​	OrderID​	InvoiceNo.​	Sales​	PayTerm​	ETD​	ETA​	InvDate​	DueDate​
A1	XYZ	202108	INV1	$10000​	SIGHT DP	7/18/2021​	9/4/2021​	8/11/2021​	8/11/2021​

Or if PayTerm is "TT 60 Days After BL Date" (FYI: BL Date is same as ETD), then due date will be 60 days after ETD.

Agent​	CUST​	OrderID​	InvoiceNo.​	Sales​	PayTerm​	ETD​	ETA​	InvDate​	DueDate​
A1	ABC	202109	INV2	$10000​	TT 60 Days After BL Date	8/18/2021​	9/30/2021​	8/15/2021​	10/17/2021​

Since I have so many payment terms and I can't fit all of them in one "IIF" statement, what do you think would be best way to calculate the due date? FYI: I only have beginner knowledge of building database.

Thanks.

 

[theDBguy]

Thats correct.

Perhaps this example will help. Base on PayTerm "Sight DP", due date should be same as InvDate.

Agent​	CUST​	OrderID​	InvoiceNo.​	Sales​	PayTerm​	ETD​	ETA​	InvDate​	DueDate​
A1	XYZ	202108	INV1	$10000​	SIGHT DP	7/18/2021​	9/4/2021​	8/11/2021​	8/11/2021​

Or if PayTerm is "TT 60 Days After BL Date" (FYI: BL Date is same as ETD), then due date will be 60 days after ETD.

Agent​	CUST​	OrderID​	InvoiceNo.​	Sales​	PayTerm​	ETD​	ETA​	InvDate​	DueDate​
A1	ABC	202109	INV2	$10000​	TT 60 Days After BL Date	8/18/2021​	9/30/2021​	8/15/2021​	10/17/2021​

Since I have so many payment terms and I can't fit all of them in one "IIF" statement, what do you think would be best way to calculate the due date? FYI: I only have beginner knowledge of building database.

Thanks.

Hi. Thanks for the additional information. As I indicated earlier, to make it simpler for your to maintain your query, you could create a separate table for your payment terms with the logic of what the the Due Date would be. For example, it might look something like this:

tblPaymentTerms
PayTermID
PayTerm
BaseDateField
DaysOffset

For "Sight DP," the entry might look like this:
Sight DP; InvDate; 0

In your query, you might have a calculated field that looks like this:
DueDate: DateAdd("d",[DaysOffset],Eval([BaseDateField]))

That's untested, of course. It would help get things going faster if you could post a sample db with test data.

 

[The_Doc_Man]

You MIGHT be able to reduce the complexity with the SWITCH function, which DOES work in a query:

SWITCH SQL Function

A syntax description of the SQL function SWITCH for the SmallSQL database.

www.smallsql.de

I looked it up to be sure, but Access's version of SQL does not support the CASE statement - though some other versions of SQL do.

It might look like this... (I'll get it started; up to you to finish)

SELECT ..., IIF( Not IsNull(DateMature), DateMature,
    SWITCH (   PayTerm = 'SIGHT DP', InvDate+14,
               PayTerm = 'SIGHT LC', InvDate + 14,
               PayTerm = 'SIGHT TT', InvDate + 14,
               PayTerm = 'LLC 60 Days After BL Date', ETD+60,
               PayTerm = 'LC 90 Days After BL Date', ETD + 90,
              ...)

You don't need the brackets around any of the actual fields as long as they have neither spaces nor special characters (and also cannot be any Access reserved words). This might reduce your typing

 

[rabbit2323]

Hi. Thanks for the additional information. As I indicated earlier, to make it simpler for your to maintain your query, you could create a separate table for your payment terms with the logic of what the the Due Date would be. For example, it might look something like this:

tblPaymentTerms
PayTermID
PayTerm
BaseDateField
DaysOffset

For "Sight DP," the entry might look like this:
Sight DP; InvDate; 0

In your query, you might have a calculated field that looks like this:
DueDate: DateAdd("d",[DaysOffset],Eval([BaseDateField]))

That's untested, of course. It would help get things going faster if you could post a sample db with test data.

Attach is the sample database I made. I appreciate you trying to help me. I tested the calculated field you mention, but the due date shows as error (see DueDateTEST qry).

 

[Pat Hartman]

I would create a function. You can't do this directly with a table lookup because you are using 4 different dates as the base I would still create a table with the PayTerm and number of days to add but I would include a third column to hold the BaseDate name to simplify the calculation. The query would join to the PlusDays table and it would pass the days field, the DateToUse field, and the 4 dates to the function. The function would be a Select case.

Sorry about the code. The site is broken again and it kept dropping the code if I enclosed it in the tags.

Public Function fDueDT(DateToUse, DaysToAdd, MatureDate, InvDate, ETD, ETA) As Date
Select Case DateToUse
Case MatureDate
fDueDT = MatureDate
Case InvDate
fDueDT = InvDate + DaysToAdd
Case ETD
fDueDT = ETD + DaysToAdd
Case ETA
fDueDT = ETA +DaysToAdd
End Case