Multiple iif statements (at least 15) (1 Viewer)

rabbit2323

New member
Local time
Today, 18:20
Joined
Aug 16, 2021
Messages
6
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

I’m here to help
Staff member
Local time
Today, 15:20
Joined
Oct 29, 2018
Messages
19,004
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

New member
Local time
Today, 18:20
Joined
Aug 16, 2021
Messages
6
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 NameData TypeDescription
IDAutoNumber
AgentTextSale Agent
CUSTTextCustomer Name
OrderIDTextSale order number
InvoiceNo.TextInvoice number
SalesCurrencySales Amount
PayTermTextPayment Terms
ETDDate/TimeEstimate Time of Departure
ETADate/TimeEstimate Time of Arrival
InvDateDate/TimeInvoice Date
DateMatureDate/TimeLetter of Credit Maturity Date
DueDateDate/TimeEstimate Due Date
PaidDateDate/TimePayment receive date
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:20
Joined
Oct 29, 2018
Messages
19,004
Can any of those be calculated? For example ETD, ETA, or DueDate? Just curious...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:20
Joined
Oct 29, 2018
Messages
19,004
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

I’m here to help
Staff member
Local time
Today, 15:20
Joined
Oct 29, 2018
Messages
19,004
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

New member
Local time
Today, 18:20
Joined
Aug 16, 2021
Messages
6
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
A1XYZ202108INV1
$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
A1ABC202109INV2
$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

I’m here to help
Staff member
Local time
Today, 15:20
Joined
Oct 29, 2018
Messages
19,004
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
A1XYZ202108INV1
$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
A1ABC202109INV2
$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

Immoderate Moderator
Staff member
Local time
Today, 17:20
Joined
Feb 28, 2001
Messages
22,858
You MIGHT be able to reduce the complexity with the SWITCH function, which DOES work in a query:


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)

Code:
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

New member
Local time
Today, 18:20
Joined
Aug 16, 2021
Messages
6
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).
 

Attachments

  • Copy.accdb
    636 KB · Views: 406

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:20
Joined
Feb 19, 2002
Messages
36,409
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
 

Users who are viewing this thread

Top Bottom