Flow74 Flow74 - 2 months ago 13
SQL Question

VBA: Insert ascending Date in Table

I'm trying to simplify the input by users with a code. It's difficult to explain the process so I show you what I mean.

The user only has to Input the following values:

Event:

event1
From: 01.01.2017 To:
01.04.2017
Cost:
5000


Result in the Table:

event1 01.01.2017 5000
event1 01.02.2017 5000
event1 01.03.2017 5000
event1 01.04.2017 5000


I tried it with this code:

Private Sub Save_Click()

Dim strSQL As String
Dim Period As Date

For Period = "' & Me!FromDate & '" To "' & Me!ToDate & '"

strSQL = "INSERT INTO tblEvents (EventName, Date, Costs) VALUES ('" & Me!EventName& "' , '" & Date & "', '" & Me!Costs& "')"
CurrentDb.Execute strSQL

Next Date

End Sub

Answer

You should use the following date calculation functions:

  1. DateDiff() to calculate the number of months between your 2 dates
  2. DateAdd() to create a date by adding a number of months to another date

Edit: Adding infor for the data input

add 2 textboxes to your form, name them TextDtFrom and TextDtTo, and give them the format shortdate so you'll see a calendar appear to pick up the dates easily.


Add a button with this click event :

Private Sub TheButton_Click()


    Dim strSQL As String
    Dim dtFrom As Date
    Dim dtTo As Date
    Dim dtCurrent As Date
    Dim intMonths As Integer
    Dim i As Integer

    'dtFrom = DateSerial(2017, 1, 1)
    'dtTo = DateSerial(2017, 4, 1)

    dtFrom = TextDtFrom.Value
    dtTo = TextDtTo.Value

    ' Calculate the number of months between the 2 dates
    intMonths = DateDiff("m", dtFrom, dtTo)

    ' Looping on the number of months
    For i = 0 To intMonths

        ' Computing Datefrom + month
        dtCurrent = DateAdd("m", i, dtFrom)

        strSQL = "INSERT INTO tblEvents (EventName, Date, Costs) VALUES ('" & Me!EventName & "' , '" & Format(dtCurrent, "DD.MM.YYYY") & "', '" & Me!Costs & "')"

        CurrentDb.Execute strSQL

    Next i


End Sub

Documentation: VBA Date Time Manipulation

Comments