query for creating Due dates based on a start date until count 12

[gverp]

Hi ,

I want to create a report or query to create a following

my table consists of contract no ,Customer number ,Invoice amt ,start date and freq of billing( which can be monthly or qtrly)

I want to create a query to append a new field called Due dates which should have 12 duedates one below the other until count is 12 for eg if the start date is 05-jan-2004 and freq is Monthly the duedates will be 04-feb-2004,04-march-04....and so on until 05-dec-2004.Mind you this dates should be below each other .

Can anyone help?

 

[Brian1960]

Due Dates

Not quite sure what you mean, but if you just want a report with the current records data and also the 12 due dates then you could add a text fields with the data =DateAdd("m", 1, CDate(StartDate)) then another with data =DateAdd("m", 2, CDate(StartDate)) etc. etc

 

[gverp]

Incrementing dates

I have a table with records as follows;

CONTRACT NO(A) FREQ (B) START DATE OF CONT ( C)
C-0001 MONTHLY 01-Jan-04
C-0002 QTRLY 15-Feb-04

My requiremnt is I should calculate 12 duedates for each contract based on the start date( For eg If Freq ="MONTHLY" ,then due dates = Start date +30 ,the next due dates will be incremented by 30 days until count =12

Same for the 2nd record ,since its Freq="QTRLY", the due dates for the second record should be incremented by 90 days until the count is 12.


The results should look like below .

How can i Acheive this in Access query?


CONTRACT NO(A) FREQ (B) START DATE OF CONT ( C) DUEDATES (D)
C-0001 MONTHLY 01-Jan-04 31-Jan-04
C-0001 MONTHLY 01-Jan-04 01-Mar-04
C-0001 MONTHLY 01-Jan-04 31-Mar-04
C-0001 MONTHLY 01-Jan-04 30-Apr-04
C-0001 MONTHLY 01-Jan-04 30-May-04
C-0001 MONTHLY 01-Jan-04 29-Jun-04
C-0001 MONTHLY 01-Jan-04 29-Jul-04
C-0001 MONTHLY 01-Jan-04 28-Aug-04
C-0001 MONTHLY 01-Jan-04 27-Sep-04
C-0001 MONTHLY 01-Jan-04 27-Oct-04
C-0001 MONTHLY 01-Jan-04 26-Nov-04
C-0001 MONTHLY 01-Jan-04 26-Dec-04

C-0002 QTRLY 15-Feb-04 15-May-04
C-0002 QTRLY 15-Feb-04 13-Aug-04
C-0002 QTRLY 15-Feb-04 11-Nov-04
C-0002 QTRLY 15-Feb-04 09-Feb-05
C-0002 QTRLY 15-Feb-04 10-May-05
C-0002 QTRLY 15-Feb-04 08-Aug-05
C-0002 QTRLY 15-Feb-04 06-Nov-05
C-0002 QTRLY 15-Feb-04 04-Feb-06
C-0002 QTRLY 15-Feb-04 05-May-06
C-0002 QTRLY 15-Feb-04 03-Aug-06
C-0002 QTRLY 15-Feb-04 01-Nov-06
C-0002 QTRLY 15-Feb-04 30-Jan-07

 

[Rich]

What would be the purpose of creating these records?

 

[gverp]

Query regarding appending dates

What would be the purpose of creating these records?

See the purpose of creating this records is once created I can run cross tab report to show "future Cash Inflow report".

I smiulated the same in excel and the pivot tables gives me desired cash flow. It is something like this


Customer Jan Feb March APril Total
Customer A 2000 2000 2000 3000 9000
Customer B 2000 3000 2000 1000 8000

The currency amounts like 2000 ,3000 are the summarised Invoice amounts for different contracts per customer which falls is specific periods.

I could only find one logic that could get the above result i.e is 1st to create a records as above then run cross tab query .

If anyone can suggest a better solution please help ..I have been thinking for days abvout this problem .


My email id is gverghes@4c.com.kw

 

[Rich]

Function FillTableDteDue()
Dim Db As DATABASE
Dim rst As Recordset
Dim StDate As Date
Dim I As Variant
Dim ssFreq As String
ssFreq = Forms!frmDatesDue!txtFreq
Dim NoOfMnths As Integer, Heading As String, PymtType As String, Description As String, Payee As String, PaymentAmount As Currency
NoOfMnths = Forms!frmDatesDue!NoOfMnths
StDate = Forms!frmDatesDue!StDate
Set Db = CurrentDb
Set rst = Db.OpenRecordset("DateDue")
rst.MoveFirst
For I = -1 To NoOfMnths - 2
Select Case ssFreq
Case "Monthly"
rst.AddNew
rst!AccTypeID = Forms!frmDatesDue!AccTypeID
rst!DateDue = DateAdd("m", I + 1, StDate)
rst!Heading = Forms!frmDatesDue!Heading
rst!PymtType = Forms!frmDatesDue!PymtType
rst!Description = Forms!frmDatesDue!Description
rst!Payee = Forms!frmDatesDue!Payee
rst!PaymentAmount = Forms!frmDatesDue!PaymentAmount
rst!AccTypeID1 = Forms!frmDatesDue!txtTransf

rst.Update
rst.MoveNext
Case "Quarterly"
rst.AddNew
rst!AccTypeID = Forms!frmDatesDue!AccTypeID
rst!DateDue = DateAdd("m", I * 3, StDate)
rst!Heading = Forms!frmDatesDue!Heading
rst!PymtType = Forms!frmDatesDue!PymtType
rst!Description = Forms!frmDatesDue!Description
rst!Payee = Forms!frmDatesDue!Payee
rst!PaymentAmount = Forms!frmDatesDue!PaymentAmount
rst!AccTypeID1 = Forms!frmDatesDue!txtTransf
rst.Update
rst.MoveNext
End Select
Next I


rst.Close
Set rst = Nothing
Db.Close
Set Db = Nothing


End Function