Change of month

[Red6]

Hi,

At the end of each month I import data into my A97 db which via an append qry updates a table with with new data. The attached SQL shows what is going on. The problem I have is that at the start of a new month I have to go into the qry and alter the month, in the code area, [Alloc Apr]-[Overdue] AS [Due This Month]. For example tomorrow I will have to change the Alloc Apr to Alloc May and then change the subsequent months to match the table. ie M2 would be populated by Alloc Jun, M3 would be Alloc Jul etc etc. (M2 being month 2 as opposed to being the current month).

This a fairly simple but onerous task to do manually but I am looking for a way of automating it, in that as the month changes the overdues are deducted from the correct month and the subsequent months are updated into the correct field.

Any guidance would be greatfully appreciated.


INSERT INTO [tbl - Forthcoming Work Data] ( [ES Name], Overdue, [Current Month], M2, M3, M4, M5, M6 )
SELECT [New FCW Data].[ES Name], [New FCW Data].Overdue, [Alloc Apr]-[Overdue] AS [Due This Month], [New FCW Data].[Alloc May], [New FCW Data].[Alloc Jun], [New FCW Data].[Alloc Jul], [New FCW Data].[Alloc Aug], [New FCW Data].[Alloc Sep]
FROM [New FCW Data];

 

[Jon K]

You can build and run the SQL statement in VBA in the On Click event of a command button on a form.

----------------------------
Private Sub Command0_Click()

Dim SQL As String

SQL = "INSERT INTO [tbl - Forthcoming Work Data]" & _
" ( [ES Name], Overdue, [Current Month], M2, M3, M4, M5, M6 )" & _
" SELECT [New FCW Data].[ES Name], [New FCW Data].Overdue," & _
" [Alloc " & Format(Date,"mmm") & "]-[Overdue] AS [Due This Month]," & _
" [New FCW Data].[Alloc " & Format(DateAdd("m",1,Date),"mmm") & "]," & _
" [New FCW Data].[Alloc " & Format(DateAdd("m",2,Date),"mmm") & "]," & _
" [New FCW Data].[Alloc " & Format(DateAdd("m",3,Date),"mmm") & "]," & _
" [New FCW Data].[Alloc " & Format(DateAdd("m",4,Date),"mmm") & "]," & _
" [New FCW Data].[Alloc " & Format(DateAdd("m",5,Date),"mmm") & "]" & _
" FROM [New FCW Data];"

DoCmd.RunSQL SQL

End Sub
----------------------------

 

[Red6]

Many thanks Jon I will give it a try.