I'm trying to work out some invoice due dates. I have the following code:
Code:
DUE DATE: Format(IIf([account terms]="30 days EOM",DateSerial(Year([TaxDate]),Month([TaxDate])+2,1),IIf([account terms]="60 days EOM",DateSerial(Year([TaxDate]),Month([TaxDate])+3,1),[TaxDate])),"Short Date")
My issue is this is not presenting the correct day.
I need the code to add on the days first, then find the end of the month the new date falls in to:
Invoice date 02/01/01 + 30 days is 01/02/01 due date would be 28/02/01
However in the current format it would display the first day of the month following the date it should be due..
I understand I can do
Code:
DUE DATE: Format(IIf([account terms]="30 days EOM",DateSerial(Year([TaxDate]),DAY([TaxDate])+30,
but how to I then get it to display the end of that month?
Edit:
-1 after the following month. Doh.
for anyone hunting:
Code:
DUE DATE: Format(IIf([account terms]="30 days EOM",DateSerial(Year([TaxDate]),Day([TaxDate])+2,1)-1,IIf([account terms]="60 days EOM",DateSerial(Year([TaxDate]),Day([TaxDate])+60,1)-1,[TaxDate])),"Short Date")
'the last day of the next month:
[DueDate_30dEOM] : dateserial(year(dateadd("m",2,[TaxDate])),month(dateadd("m",2,[TaxDate])),1)-1
[I]'add 2 months, find the 1st day of that month, and subtract 1 day[/I]
'the last day of the month after that
[DueDate_60dEOM] : dateserial(year(dateadd("m",3,[TaxDate])),month(dateadd("m",3,[TaxDate])),1)-1
[I]'add 3 months, find the 1st day of that month, and subtract 1 day[/I]
I didn't update the expression you included since it seems to be missing an 'else' segment. (ie., what happens if [Account Terms] is neither "30 days EOM" nor "60 days EOM"?)
I have a table that has my terms in, two fields, TermID and PaymentTerms
for example TermID is 1 and paymentTerms is 30 Days EOM
There are a total of 13 available payment terms. in order to correct the date value displaced in the query I would need to created a nested iff statement with 14 options. As I understand this isn't possible as it's limited to 7?
So I now I guess I need to create a function to find the correct calculation for the payment terms
What would be the best way to go about this?
Code:
Function CalcDue(TermID As Integer)
If TermID Like "1" Then
CalcDue = "DateSerial(Year([TaxDate]), Day([TaxDate]) + 30, 1) - 1"
From memory (not always reliable), I think you can have 50 nested IIf statements.
Even so, it would be a nightmare to setup or modify
If payment terms are always the number of days 'grace', then you could use a DLookup function
Code:
Dim N As Integer
N = DLookup("PaymerntTerms","YourTableName", "TermID=" & TermID)
CalcDue = "DateSerial(Year([TaxDate]), Day([TaxDate]) + 30, N) - 1"
Otherwise use a Select Case statement for cases 1 to 13
Code:
Function CalcDue
Select Case TermID
Case 1 '30 days
CalcDue = "DateSerial(Year([TaxDate]), Day([TaxDate]) + 30, 1) - 1"
Case 2 '45 days
CalcDue = "DateSerial(Year([TaxDate]), Day([TaxDate]) + 45, 1) - 1"
Case 3
...
Case 13
End Select
End Function
EDIT: sorry - didn't read the previous reply in this thread - stick with that approach if you prefer
Assuming that the Case number needs to relate to the TermID:
Code:
Function CalcDue()
Select Case TermID
Case 1 '30 days EOM
CalcDue = "DateSerial(Year([TaxDate]), Day([TaxDate]) + 30, 1) - 1"
Case 2 '60 days EOM
CalcDue = "DateSerial(Year([TaxDate]), Day([TaxDate]) + 60, 1) - 1"
Case 3 'PRO FORMA
CalcDue = "[TaxDate]"
Case 5 '90 Days EOM
CalcDue = "DateSerial(Year([TaxDate]), Day([TaxDate]) + 90, 1) - 1"
Case 6 '30 Days DOI
CalcDue = "DateSerial(Year([TaxDate]), Day([TaxDate]) + 30)"
Case 7 '60 Days DOI
CalcDue = "DateSerial(Year([TaxDate]), Day([TaxDate]) + 60)"
Case 8 '45 Days EOM
CalcDue = "DateSerial(Year([TaxDate]), Day([TaxDate]) + 45)"
Case 10 '10 Days DOI
CalcDue = "DateSerial(Year([TaxDate]), Day([TaxDate]) + 10)"
Case 12 '30 Days EOM +5
CalcDue = "DateSerial(Year([TaxDate]), Day([TaxDate]) + 30, 1) + 4"
Case 13 '60 Days EOM +5
CalcDue = "DateSerial(Year([TaxDate]), Day([TaxDate]) + 60, 1) + 4"
End Select
I've allowed for where term options have been removed, is this correct?
I assumed you already had that sorted when you suggested a function!
I would suggest you have an after update event in the form used to enter data that will be invoiced.
If TermID is a field on the form, that seems the obvious place
If the field is called InvoiceDueDate and is Date/Time field type then something like
Code:
Private Sub TermID_AfterUpdate
Me.InvoiceDueDate = #" & CalcDue & "#
End Sub
NOTE: If you aren't US based (mm/dd/yyyy format), you may need to reformat the date to match the default style for your country.
In the UK, its dd/mm/yyyy
If InvoiceDueDate is a text field, then modify the delimiters
I'm glad you found my suggestion helpful -- but the formula evolved down the thread, so there are a couple issues:
DateSerial needs it's arguments in the order "Year-Month-Day" regardless of where you live in the world (which is partly why I used it; so your regional settings won't affect the calculation results.)
Some brackets are in the wrong spot. Day([TaxDate]) + 30 versus Day([TaxDate] + 30 )
When adding time to date-parts, you usually will need to add to more than just one part, or else the entire date won't be adjusted (the year in this example).
For "30 days EOM", I suggested: (we'll call this Example A) DateSerial(Year(DateAdd("m", 2, [TaxDate])), Month(DateAdd("m", 2, [TaxDate])), 1) - 1
as opposed to what it changed to further down this thread: (we'll call this Example B) DateSerial(Year([TaxDate]), Day([TaxDate]) + 30, 1) - 1
Code:
If we use the date {December 23, 2017}...
[B][I]Example A[/I][/B] will return :
DateSerial( Year( {February 23, 2018} ), Month( {February 23, 2018} ), 1 )-1
which is: DateSerial ( 2018,2,1)-1
which is: '[U]January 31, 2018[/U]' [I](Correct, I think?)[/I]
[B][I]Example B[/I][/B] will return :
DateSerial( Year( {December 23, 2017} ), Day( {December 23, 2017} ) , 1 )-1
which is: DateSerial ( 2017,23,1)-1
which is: '[U]October 31, 2018[/U]' [I] (Incorrect, I think?)[/I]
Notice how Example B is always going to return the year of TaxDate even if the DueDate should be in the following year.
Also notice how Example B is putting the DAY in the position where the MONTH should go in DateSerial, which will give some funky results.
Another example is your formula for "60 days EOM" again using {December 23, 2017} :
Code:
Another example is your formula for "60 days EOM" again using {December 23, 2017} :
CalcDue = DateSerial(Year([TaxDate]), Day([TaxDate]) + 60, 1) - 1
which is: DateSerial(2017, 23+60, 1)-1
which is: '[U]October 31, 2023[/U]' (I need a creditor with terms like that!)
Using DateAdd like I suggested is more reliable than simply adding days since not all months are the same length.
If we need to know the what month it will be one month from {January 30, 2017}:
Day([TaxDate]+30) is {March 1, 2017}
...so Month(Day([TaxDate]+30))is adding an extra month
DateAdd("m",1,[TaxDate]) is {February 28,2017}
...so it's (correctly) returning the month of February
I know the DateAdd function makes the formula longer and harder to read, but accuracy first, right?
So, I'm afraid some of your formulas need to be re-written... or, now that I see the pattern in the possible Terms, maybe a different method could make the function it a bit more flexible. Also you asked:
...which implies to me that you would like to use the function in a query or on a form, so we can give it some arguments, like this:
Code:
Function CalcDue(fTaxDate As Date, fAccountTerms As String) As Date
'calculate Due Date based on [fTaxDate] with terms of [fAccountTerms]
Dim xDays As Integer, yDays As Integer
fAccountTerms = UCase(fAccountTerms) 'work with uppercase & lowercase letters
If InStr(1, fAccountTerms, "EOM") <> 0 Then 'fAccountTerms contains 'EOM'
xDays = Val(Left(fAccountTerms, 3)) 'numeric part of the first 3 digits (allows up to 999 days)
If InStr(1, fAccountTerms, "+") <> 0 Then 'fAccountTerms also contains '+'
yDays = Val(Right(fAccountTerms, 3)) 'numeric part of the last 3 digits (days to add)
Else
yDays = 0 'no '+' so don't add any days
End If
'add X days, find the 1st day of that month, and subtract 1 day
CalcDue = DateSerial(Year(DateAdd("m", 1, DateAdd("d", xDays, fTaxDate))), _
Month(DateAdd("m", 1, DateAdd("d", xDays, fTaxDate))), 1) - 1
CalcDue = CalcDue + yDays 'add yDays in case a "+Y" was found
Exit Function
End If
If Right(fAccountTerms, 3) = "DOI" Then 'Due exactly X days from fTaxDate
xDays = Val(fAccountTerms) 'returns the numeric part of the string
CalcDue = DateAdd("d", xDays, fTaxDate)
Exit Function
End If
If fAccountTerms = "PRO FORMA" Then
CalcDue = fTaxDate
Exit Function
End If
'if we made it this far then AccountTerms is unrecognized. Ask user for DueDate by un-commenting this:
'CalcDue = DateValue(InputBox("Unknown Terms: '" & fAccountTerms & "' for TaxDate: '" & fTaxDate & "'." & vbCrLf & vbCrLf & _
"Enter Due Date: YYYY/MM/DD", "Unknown Terms", fTaxDate))
'or just use default:
CalcDue = fTaxDate 'use default like "PRO FORMA"
End Function
You can paste paste that code into a module (Access or Excel), and then call it from a query, form, report, worksheet cell, etc, with:
=CalcDue ( [TaxDate], [AccountTerms] )
for example: CalcDue ( #June 27, 2017#, "30 Days EOM" ) returns 2017-07-31
This function will allow you to specify other 'custom' AccountTerms like "120 Days EOM", "18 Days DOI", "33 Days EOM +12", etc.
Of course, the SELECT..CASE method above will work fine too, and may be easier to read, but your formulas need to be fixed up -- test them out with dates like December 30, January 30, and February 28 to see what they calculate. Also keep in mind that it will only be looking for those specific "hardcoded" AccountTerms.
I wouldn't use Iif() or a custom function. Let the engine do its best work instead.
Firstly, store AccountTerms as a numeric code. Hold the translation for the displayed name string in a lookup table. In that table also include whatever numbers are required as parameters to calculate the whole range of possible due dates for a variety of terms. That might be as simple as the number of days or multiple parameters for a complex EOM formula.
Now join this lookup table to the main table on the AccountTerms numeric code and use the numbers contained there to plug into the formula in the query field that calculates the DueDate. I have no doubt that this technique would vastly outperform the other approaches.
Moreover the AccountTerms calculation data now resides in a table. Any number of new term arrangements can be added by simply adding records to a table. Although custom functions are usually best avoided, if you do ultimately decide on a function to make the calculation, this is a far superior way to feed the parameters.
Always try to think first in terms of data. The term arrangements are data, not mathematical laws and so they belong in a table rather than a function.
There are two images in the link, the first is the Query result, the second is a PDF of an invoice created prior to this work, please note I'm trying to rectify the due date being the first of the month instead of the last day of the previous month.
Hmm, it's tough to troubleshoot this at a distance.
Re: the list of 10 items with the same invoice number, ("QryInvoiceNetTotal", I think) I gather there are not actually 10 invoices with the same number?... Could you add a few more fields to that same query, like [TaxDate] and [PaymentTerms], and post the result? There must be "10 of something" joined to that one invoice, and judging by the different DueDates, it's got something to do with the date that CalcDue is looking at.
If you remove the CalcDue expression from that query does it return only the 1 invoice?
Are you possibly using the terms "CalcDue", "TaxDate", or "PaymentTerms" in multiple places, like as both a field name in a table, and a control on a form? That could confuse things as well.
You now have 3 very different solutions to choose from, all of which should work. In the end you need to decide which works best for you.
You say you got some strange results using ashleedawg's solution
I must admit I also couldn't understand what the results you uploaded actually meant.
Just out of interest, did you actually test the select case solution I suggested and if so did that behave in the way you expected? All I know is you populated the function but not whether you tried it out....
If you can't fix this yourself, suggest you upload a stripped down version of your db with some realistic data and stating what the due date results should be for each record.
