Billing Year (1 Viewer)

Eljefegeneo

Still trying to learn
Local time
Today, 06:15
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
Code:
    MOH2:  IIf([SpecialBilling]=-1,[MonthOfInvoice],IIf(([MonthOfInvoice]="January"  Or [MonthOfInvoice]="February"),[MonthOfInvoice] & " " &  Format(DateAdd("yyyy",1,Date()),"yyyy"),[MonthOfInvoice] & " " &  Format(Date(),"yyyy")))
My best shot at this was to write the following:
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")))))
Where
Code:
  FM1: Format(Date(),"mm")
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.
 

Micron

AWF VIP
Local time
Today, 09:15
Joined
Oct 20, 2018
Messages
3,476
Presumably you're doing this off of a form.

Can't you just use controls to provide month/day/year?
 

Solo712

Registered User.
Local time
Today, 09:15
Joined
Oct 19, 2012
Messages
828
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
Code:
    MOH2:  IIf([SpecialBilling]=-1,[MonthOfInvoice],IIf(([MonthOfInvoice]="January"  Or [MonthOfInvoice]="February"),[MonthOfInvoice] & " " &  Format(DateAdd("yyyy",1,Date()),"yyyy"),[MonthOfInvoice] & " " &  Format(Date(),"yyyy")))
My best shot at this was to write the following:
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")))))
Where
Code:
  FM1: Format(Date(),"mm")
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 am sorry but I don't understand your issue. The MonthAdd function adds the same way all year round, and handles the year rollover. January and February do not need special logic. You say the billing month is the month following the invoice. So, if the invoices are late a month, the billing month will always be only a month late.
Code:
Format(DateAdd("m", 1, MonthOfInvoice,"YOURFORMAT"))
That's it. I never heard of a way of keeping dates such that you'd have to create query like you are presenting here.

Best,
Jiri
 

Eljefegeneo

Still trying to learn
Local time
Today, 06:15
Joined
Jan 10, 2011
Messages
904
Thank you for your response. Perhaps I did not explain it correctly. MonthOfInvoice is the full name of the month. That is, January, February, March, etc. Perhaps if it were a date merely formatted as "MMMM YYYY" I wouldn't have this problem, but that is the way the data is set up. When the user wants to change the MonthOfInvoice, they select month from a drop down list on a form and using an update query, the months are changed.

So, that being said, when the month is one of the ten months of the year excluding January and February, it is easy to determine the InvMonth as per my second (and third) bit of code as listed in my first post. January is also easy you merely have to add one year to the current date to get the next year as the invoices are printed either on November 30th or December 1. The Special Billing is a manual bill that uses the MonthOfInvoice changed by the user on an individual record.

It is February that is the problem, but only if the user forgets or doesn't have time to print the invoices before the end of the year date, i.e. December 31st.

The easiest way would be to use the suggestion of Micron, which originally when the DB was coded was the best way to go. However in my quest to automate the DB as much as possible and leave little to user error, I dismissed this method.

I realize that I have, in theory, solved my problem, however, in my quest to simplify code, I was seeking an alternate way of looking at this problem. If there isn't then so be it. But I have learned from this forum that sometimes my several lines of code or multiple IFF statements can be simplified and condensed. That's all I was looking for.
 

Solo712

Registered User.
Local time
Today, 09:15
Joined
Oct 19, 2012
Messages
828
Thank you for your response. Perhaps I did not explain it correctly. MonthOfInvoice is the full name of the month. That is, January, February, March, etc. Perhaps if it were a date merely formatted as "MMMM YYYY" I wouldn't have this problem, but that is the way the data is set up. When the user wants to change the MonthOfInvoice, they select month from a drop down list on a form and using an update query, the months are changed.

So, that being said, when the month is one of the ten months of the year excluding January and February, it is easy to determine the InvMonth as per my second (and third) bit of code as listed in my first post. January is also easy you merely have to add one year to the current date to get the next year as the invoices are printed either on November 30th or December 1. The Special Billing is a manual bill that uses the MonthOfInvoice changed by the user on an individual record.

It is February that is the problem, but only if the user forgets or doesn't have time to print the invoices before the end of the year date, i.e. December 31st.

The easiest way would be to use the suggestion of Micron, which originally when the DB was coded was the best way to go. However in my quest to automate the DB as much as possible and leave little to user error, I dismissed this method.

I realize that I have, in theory, solved my problem, however, in my quest to simplify code, I was seeking an alternate way of looking at this problem. If there isn't then so be it. But I have learned from this forum that sometimes my several lines of code or multiple IFF statements can be simplified and condensed. That's all I was looking for.

Be it as it may, your code will not fix anything - in theory or otherwise. This is just bad design that needs to be fixed. Period. You can easily rewrite the dropdown box to show month and year. Say something on a twelve-month rotation like this:

Code:
Private Sub PrepareMthList()
  Dim tdate As Date, i As Long
  
  tdate = Date
  With Me!MthList
     .RowSource = ""
     For i = 1 To 12
          .AddItem Format(tdate, "mmmm yyyy") & ";" & Year(tdate) * 100 + Month(tdate)
          tdate = DateAdd("m", -1, tdate)
     Next i
    .Selected(0) = True
 End With
End Sub

Your problem would be solved. But I suspect we are not as much solving a problem here as admiring one's programming navel. Well, suit yourself!
 

Attachments

  • DropdownExample.png
    DropdownExample.png
    13.8 KB · Views: 92

Users who are viewing this thread

Top Bottom