Need to update date based on type of program (1 Viewer)

maheshashish

Registered User.
Local time
Tomorrow, 04:01
Joined
Feb 22, 2017
Messages
20
Hi all,

I have a form, in several fields out of which, I have a combo box called "Program" which contains "Monthly";"Quarterly";"Half yearly";"Annually".

Now there is another field called "Next Invoice date". I want the Next invoice date to be updated automatically based on the program selected. Let us say if he joins program on 4th of march , if the customer chooses Quarterly then the next invoice date should be updated automatically to 4th of 4th of June. and accordingly for half yearly or annually.

can any one help with vb for doing this task.

Thanks in advance.
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:31
Joined
Jan 23, 2006
Messages
15,364
For clarity, if he joins Annual program on October 27 2017, what is the next invoiceDate?
 

sneuberg

AWF VIP
Local time
Today, 15:31
Joined
Oct 17, 2014
Messages
3,506
Please give us the rules you want to use to determine the date. If go by the example you give and assumed the same day three months later that rules fails for Jan 31 as April 31 is not a valid date. If you can live with a simple but imprecise rule like 30, 90,180, or 365 days later that would be fairly easy to implement.
 

maheshashish

Registered User.
Local time
Tomorrow, 04:01
Joined
Feb 22, 2017
Messages
20
For clarity, if he joins Annual program on October 27 2017, what is the next invoiceDate?

It depends on what program he chooses, and it goes like this

program Next invoice date
Monthly November 27 2017
quarterly January 27 2018
Half yearly March 27 2018
Annually October 27 2018
 

maheshashish

Registered User.
Local time
Tomorrow, 04:01
Joined
Feb 22, 2017
Messages
20
Please give us the rules you want to use to determine the date. If go by the example you give and assumed the same day three months later that rules fails for Jan 31 as April 31 is not a valid date. If you can live with a simple but imprecise rule like 30, 90,180, or 365 days later that would be fairly easy to implement.

Yeah even that works fine with me, can you please help for that scenario
 

Yousef Bajubair

New member
Local time
Tomorrow, 01:31
Joined
Mar 4, 2017
Messages
2
hope this code helps

Code:
Select Case Me.program
Case "Monthly"
    Me.NextInvoicedate = DateAdd("m", 1, Me.joinDate)
Case "Quarterly"
    Me.NextInvoicedate = DateAdd("m", 3, Me.joinDate)
Case "Half yearly"
    Me.NextInvoicedate = DateAdd("m", 6, Me.joinDate)
Case "Annually"
    Me.NextInvoicedate = DateAdd("yyyy", 1, Me.joinDate)
End Select
 

maheshashish

Registered User.
Local time
Tomorrow, 04:01
Joined
Feb 22, 2017
Messages
20
hope this code helps

Code:
Select Case Me.program
Case "Monthly"
    Me.NextInvoicedate = DateAdd("m", 1, Me.joinDate)
Case "Quarterly"
    Me.NextInvoicedate = DateAdd("m", 3, Me.joinDate)
Case "Half yearly"
    Me.NextInvoicedate = DateAdd("m", 6, Me.joinDate)
Case "Annually"
    Me.NextInvoicedate = DateAdd("yyyy", 1, Me.joinDate)
End Select

Does this work with months of february, and months with 31 days as well?
I don't think so
 

maheshashish

Registered User.
Local time
Tomorrow, 04:01
Joined
Feb 22, 2017
Messages
20
I had attached the sample db, please also see the attached db, thank you
 

Attachments

  • Training.zip
    31.1 KB · Views: 83

jdraw

Super Moderator
Staff member
Local time
Today, 18:31
Joined
Jan 23, 2006
Messages
15,364
Here's a small sample using the select case from earlier post
Code:
'---------------------------------------------------------------------------------------
' Procedure : testDates
' Author    : mellon
' Date      : 04-Mar-2017
' Purpose   : in response to
'https://www.access-programmers.co.uk/forums/showthread.php?p=1524011#post1524011
'---------------------------------------------------------------------------------------
'
Sub testDates()
    Dim i As Integer, j As Integer
    Dim nextInvoicedate As Date
    Dim joinDate(2) As Date
10        On Error GoTo testDates_Error

20    joinDate(0) = #3/1/2017#
30    joinDate(1) = #2/28/2017#
40    joinDate(2) = #3/31/2017#
    Dim program(3) As String
50    program(3) = "Annually"
60    program(2) = "Monthly"
70    program(1) = "Quarterly"
80    program(0) = "Half Yearly"

90    For i = 0 To 3
100     For j = 0 To 2
110         Select Case program(i)
            Case "Monthly"
120             nextInvoicedate = DateAdd("m", 1, joinDate(j))
130         Case "Quarterly"
140             nextInvoicedate = DateAdd("m", 3, joinDate(j))
150         Case "Half yearly"
160             nextInvoicedate = DateAdd("m", 6, joinDate(j))
170         Case "Annually"
180             nextInvoicedate = DateAdd("yyyy", 1, joinDate(j))
190         End Select
200         Debug.Print "Programtype "; program(i); "  Join Date "; joinDate(j); "Next Invoice Date "; nextInvoicedate
210     Next j
220   Next i

230       On Error GoTo 0
240       Exit Sub

testDates_Error:

250       MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure testDates of Module AWF_Related"
End Sub

and output

Code:
Programtype Half Yearly  Join Date 01-Mar-2017 Next Invoice Date 01-Sep-2017 
Programtype Half Yearly  Join Date 28-Feb-2017 Next Invoice Date 28-Aug-2017 
Programtype Half Yearly  Join Date 31-Mar-2017 Next Invoice Date 30-Sep-2017 
Programtype Quarterly  Join Date 01-Mar-2017 Next Invoice Date 01-Jun-2017 
Programtype Quarterly  Join Date 28-Feb-2017 Next Invoice Date 28-May-2017 
Programtype Quarterly  Join Date 31-Mar-2017 Next Invoice Date 30-Jun-2017 
Programtype Monthly  Join Date 01-Mar-2017 Next Invoice Date 01-Apr-2017 
Programtype Monthly  Join Date 28-Feb-2017 Next Invoice Date 28-Mar-2017 
Programtype Monthly  Join Date 31-Mar-2017 Next Invoice Date 30-Apr-2017 
Programtype Annually  Join Date 01-Mar-2017 Next Invoice Date 01-Mar-2018 
Programtype Annually  Join Date 28-Feb-2017 Next Invoice Date 28-Feb-2018 
Programtype Annually  Join Date 31-Mar-2017 Next Invoice Date 31-Mar-2018
 

maheshashish

Registered User.
Local time
Tomorrow, 04:01
Joined
Feb 22, 2017
Messages
20
hope this code helps

Code:
Select Case Me.program
Case "Monthly"
    Me.NextInvoicedate = DateAdd("m", 1, Me.joinDate)
Case "Quarterly"
    Me.NextInvoicedate = DateAdd("m", 3, Me.joinDate)
Case "Half yearly"
    Me.NextInvoicedate = DateAdd("m", 6, Me.joinDate)
Case "Annually"
    Me.NextInvoicedate = DateAdd("yyyy", 1, Me.joinDate)
End Select

It works, thanks
 

Users who are viewing this thread

Top Bottom