Eljefegeneo
Still trying to learn
- Local time
- Yesterday, 16:28
- Joined
- Jan 10, 2011
- Messages
- 904
I am trying to figure out how to formulate a multi-Iff statement for an invoice. The invoices are sent out a month in advance on the first of every month for billing one month in advance. Simply stated, if an invoice was sent out on February 1, 2019, the Billing Month would be March 2019. The problem arises at the end of the year. Last year on December 1, 2018, the invoices were sent out for the Billing Month of January 2019. I figured out to word the iff statement so that if the [BillingMonth] January, I used the DateAdd formula and added on Year to the current year. The problem arises for the Billing Month of Feburary 2019. The Invoices were supposed to be printed before the end of December so the iff statement would work correctly. However, because of the now almost standard custom of taking the week off between Christmas and New Year's, sometimes the invoices are not printed on time. Not always, but it did happen this year. So the [BillingMonth] was shown as February 2020 instead of February 2019.
I have been using the following which would work just fine if the February invoices were printed on time. But not wanting to make an assumption that people would always complete tasks on time, I tried to amend the following to show the correct Billing Month regardless of when the invoices were printed.
Original
My best shot at this was to write the following:
Where
Since only the February invoices might be a problem, I separated out January. But, my usual attempts at such code tend to be laborious, and I am wondering if there is a simpler solution to what I want to do.
Thanks to anyone who might take a look at this and make suggestions.
I have been using the following which would work just fine if the February invoices were printed on time. But not wanting to make an assumption that people would always complete tasks on time, I tried to amend the following to show the correct Billing Month regardless of when the invoices were printed.
Original
Code:
MOH2: IIf([SpecialBilling]=-1,[MonthOfInvoice],IIf(([MonthOfInvoice]="January" Or [MonthOfInvoice]="February"),[MonthOfInvoice] & " " & Format(DateAdd("yyyy",1,Date()),"yyyy"),[MonthOfInvoice] & " " & Format(Date(),"yyyy")))
Code:
InvMonth: IIf([SpecialBilling]=-1,[MonthOfInvoice],IIf(([MonthOfInvoice]="January"),[MonthOfInvoice] & " " & Format(DateAdd("yyyy",1,Date()),"yyyy"),IIf(([MonthOfInvoice]="February") And ([FM1]=1),[MonthOfInvoice] & " " & Format(Date(),"yyyy"),IIf(([MonthOfInvoice]="February") And ([FM1]<>1),[MonthOfInvoice] & " " & Format(DateAdd("yyyy",1,Date()),"yyyy"),[MonthOfInvoice] & " " & Format(Date(),"yyyy")))))
Code:
FM1: Format(Date(),"mm")
Thanks to anyone who might take a look at this and make suggestions.