Private Sub cmdSplitData_Click()
Call SplitData
End Sub
Private Sub SplitData()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim ref As String
Dim lineId As Long
Dim sales As String
Dim customer As String
Dim startDt As Date
Dim endDt As Date
Dim priCon As String
Dim brFam As String
Dim prdPlat As String
Dim prdType As String
Dim prdName As String
Dim units As String
Dim amount As Currency
Dim conMonth As String
Dim custConDt As Date
Dim amtCur As String
Dim navID As String
Dim wStartDt As Date
Dim wEndDt As Date
Dim nStartDt As Date
Dim nEndDt As Date
Dim nAmount As Currency
Dim lastOne As Boolean
Dim totDays As Long
Dim monDays As Long
Dim monAmt As Currency
Dim runAmt As Currency
Dim strSQL As String
' Open up query in recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("qrySourceTable", dbOpenDynaset) 'enter your query name
' Loop through recordset
rst.MoveFirst
Do While Not rst.EOF
' Capture record values
ref = rst!Reference
lineId = rst![Line ID]
sales = rst![Sales Person]
customer = rst!customer
startDt = rst![Start Date]
endDt = rst![End Date]
priCon = rst![Primary Contact]
brFam = rst![Brand Family]
prdPlat = rst![Product Platform]
prdType = rst![Product Type]
prdName = rst![Product Name]
units = rst![Unit of Measure]
amount = rst!amount
conMonth = rst![Contract Month]
custConDt = rst![Customer Contract Date]
amtCur = rst![Amount Currency]
navID = rst![Navision ID]
' Initialize counter
lastOne = False
wStartDt = startDt
wEndDt = EOMDate(startDt)
runAmt = 0
' Capture total number of days
totDays = endDt - startDt + 1
' Loop through records
Do
' Check to see if end date is after end of month, and set dates
If endDt > wEndDt Then
nStartDt = wStartDt
nEndDt = wEndDt
Else
nStartDt = wStartDt
nEndDt = endDt
lastOne = True
End If
' Calculate monthly days & monthly amount
monDays = nEndDt - nStartDt + 1
' Calculate monthly amount and running amount
If lastOne Then
nAmount = amount - runAmt
Else
nAmount = Round(amount * monDays / totDays, 2)
runAmt = runAmt + nAmount
End If
' Build SQL query to insert new record
strSQL = "INSERT INTO DestTable ( Reference, [Line ID], [Sales Person], Customer, [Start Date], [End Date], [Primary Contact], [Brand Family], [Product Platform], "
strSQL = strSQL & "[Product Type], [Product Name], [Unit of Measure], Amount, [Contract Month], [Customer Contract Date], [Amount Currency], [Navision ID]) "
strSQL = strSQL & "VALUES ('" & ref & "', " & lineId & ", '" & sales & "', '" & customer & "', #" & nStartDt & "#, #" & nEndDt & "#, '"
strSQL = strSQL & priCon & "', '" & brFam & "', '" & prdPlat & "', '" & prdType & "', '" & prdName & "', '" & units & "', " & nAmount & ", '"
strSQL = strSQL & conMonth & "', #" & custConDt & "#, '" & amtCur & "', '" & navID & "')"
' Run SQL
'MsgBox strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
' Increment dates for next round
wStartDt = BOMDate(wEndDt + 1)
wEndDt = EOMDate(wStartDt)
Loop Until lastOne = True
' Move to next record
rst.MoveNext
Loop
' Close recordset
rst.Close
MsgBox "Data split complete!", vbOKOnly
End Sub
Function BOMDate(inputDate) As Date
' Returns the beginning of month date for any inputted date
BOMDate = DateSerial(Year(inputDate), Month(inputDate), 1)
End Function
Function EOMDate(inputDate) As Date
' Returns the end of month date for any inputted date
EOMDate = DateSerial(Year(inputDate), Month(inputDate) + 1, 0)
End Function
