DATEADD behaviour (1 Viewer)

[Chrism2]

I have using the following code to insert records into a table.

This is for contract work where, for example, an annual contract might be paid monthly. The vba takes the start date, and the total amount and cuts it up, accordingly - depending on the contracts length (be it 12 months, 24 months etc and then divides it into billing cycles, eg annual, quarterly or monthly.)

The works okay, except for one thing.

If you take a 12 month contract, start it say, in April and then split it into 12 monthly segments (as my code does)... it's fine until december - when it reverts back to October.

loopBillDate = DateAdd("m", CFREQ, loopBillDate)

Consider that CFREQ is 12.

In the case above, this adds 12 *days" to every step - no doubt something to do with US/UK date formats and SQL.

If you change the code to

loopBillDate = DateAdd("d", CFREQ, loopBillDate)

The months are changed - but when you get to a point where the next "step" takes you to the next year, it fails and moves you to October

Any clues? (More code below)


Thanks, friends.

Private Sub cmdAddLines_Click()
' This sub is designed to calculate, according to the contract value, length and bill frequency, how many lines are required in the contract, when they are to be billed and how much each invoice is to be.

'We need to ascertain out starting values:

Dim CTYPEID As Integer          'The contract type as defined in tblContractTypes
Dim CSTARTDATE As String        'When we want the first installment to begin
Dim CTYPE As String             'The name of the contract type; each line is named as such
Dim CLENGTH As Integer          'How long the contract lasts - this is the divisor for the CVAL variable
Dim CVAL As Currency            'The total selling price of the contract; for example, a 12 month WFBA AntiVirus solution for 10 users is £250
Dim CVALSPLIT As Currency       'CVAL / CLENGTH - in the example above, 20.83 (the value broken down to it's monthly equiv.)
Dim CFREQ As Integer            'How often we are billing. Everything is calculated monthly, so a monthly billing cycle will multiply CVALSPLIT by 1, quarterly by 3 and annual will multiply it by 12.
                                'In this case, Trend is annual - so we multiply by 12. £249.96
Dim CBILL As Currency           'The calculated monthly value.
Dim CREM As Currency            'the discrepant difference between the initial total value and the calculated total. (in our case, 0.04p)

CSTARTDATE = Format(Me.txtDateStart, "MM/DD/YYYY")      'Change to SQL friendly US format
CTYPEID = Me.ContractID                                 'The ID of the contract we're inserting
CTYPE = Me.txtCType                                     'the description of the contract
CLENGTH = Me.txtCLength                                 'How long the contract lasts
CVAL = Format(Me.txtContractVal, "#.00")                'The total selling price of the contract
CFREQ = Me.txtCFreq                                     'The frequency by which the total will be billed, in months.

If Not CFREQ = 12 Then                                  'If the contract length is 12 months, it is counter productive to do this.
    CVALSPLIT = Format((CVAL / CLENGTH), "#.00")        'Divide the total by the contract length to give us monthly equivalents.
    CBILL = Format((CVALSPLIT * CFREQ), "#.00")         'Multiply the monthly equivs to give us our billing installments
    CREM = CVAL - Format((CVALSPLIT * CLENGTH), "#.00") 'Calculate the remainder (if any)
Else
    CVALSPLIT = CVAL                                    'Its a 12 month contract and is paid once, so the value stays the same
    CBILL = CVAL                                        'The billing period is annual
    CREM = CVAL - CBILL                                 'For completion really; the value will be 0.
End If

'confirm what we're about to do
MsgBox ("You have £" & CVAL & " to be paid in " & CFREQ & " month installments. Based on a " & CLENGTH & " month contract, this will come to £" & CBILL & " per billing cycle. The remainder is £" & CREM)

Dim loopFreq As Integer         'How many loops we need to generate individual contract lines
Dim loopBillDate As Date        'Store the date of each line

loopFreq = 12 / CFREQ           'i.e Annual = 1, Quarterly = 4, Monthly = 12
loopBillDate = CSTARTDATE       'Start the billing from the date specified

For l = 1 To loopFreq           'set l as the loop counter and count to the figure stored in loopfreq

If Not CREM > 0 Then            'Check if there is a remainder
    If l = loopFreq Then        'If we are on the last record / loop
        CBILL = CBILL + CREM    'Add the remainder to the last bill
    End If
End If

DoCmd.SetWarnings (warningsoff) 'turns off annoying SQL insert warnings.
    
        strSQLstatement = ""                        ' to be sure it starts blank
        strSQLstatement = strSQLstatement & "INSERT INTO tblContractLines ( contractID, contractlineDate, contractlineDesc, contractlineSell )"
        strSQLstatement = strSQLstatement & "SELECT " & CTYPEID & " AS contractID, "
        strSQLstatement = strSQLstatement & "#" & loopBillDate & "# AS contractlineDate, "                  'remember US format for SQL
        strSQLstatement = strSQLstatement & "'PART: " & l & ". " & CTYPE & "' AS contractlineDesc, "
        strSQLstatement = strSQLstatement & CBILL & " AS contractlineSell"
        
        DoCmd.RunSQL strSQLstatement                'Do the insert
        
        
     DoCmd.SetWarnings (warningson)                 'Switch back on warnings
     
loopBillDate = DateAdd("m", CFREQ, loopBillDate)    'Change the NEXT billing date to add X months, where X is the Frequency by which we are billing
    
  Next l                                            'Continue the loop

Me.sfrmContractLines.Requery                        'Refresh our subform



End Sub

 

[spikepl]

loopBillDate = CSTARTDATE !

Your loopBillDate is a Date, CSTARTDATE is a String containing the date in the US format and not according to your locale.

You should at least use Cdate to go from String to DATE, but remember it will convert your date according to your locale. So you converted it to the mm/dd/yyy format and Access will convert it back WHEN IT CAN . If you need to go back from the string-version, first convert it into the same format as per your locale.

 

[spikepl]

"Unfortunately, Microsoft tried to be too smart at helping Access accept dates. If you enter a date that is invalid for your local settings, Access spins the date around trying to find an interpretation that works. For example, with British dates in Control Panel, if you enter 10/13/01, Access realises there is no 13th month, and decides you must have intended 13-Oct-01. The results can be bizarre. The entry 02/29/01 should generate an error message that 2001 is not a leap year. It doesn't. Instead, Access plays with the entry and decides you must have intended Feb-1-2029 !!!"


from http://allenbrowne.com/ser-36.html

 

[Chrism2]

Sorted.

I've changed the code to only format the date in the SQL section.

It works fine now.

Thanks