VBA: Insert ascending Date in Table

Question

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

Have you read this: http://stackoverflow.com/questions/771861/insert-sql-command-with-datetime-in-ms-access ? — Roland, Sep 06 '16 at 09:34

score 1 · Accepted Answer · edited Sep 21 '17 at 22:35

1

You should use the following date calculation functions:

DateDiff() to calculate the number of months between your 2 dates
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

edited Sep 21 '17 at 22:35

Graham

7,431
18
59
84

answered Sep 06 '16 at 09:36

Thomas G

9,886
7
28
41

I tried to find a solution where it is possible to define DateFrom/DateTo with Text- or Comboboxes. Do you know a possible solution for that? – Flow74 Sep 06 '16 at 10:19
Yes but you should first tell us more about the technology used. VBA is not enough. Is it MS ACcess or another Office product ?? – Thomas G Sep 06 '16 at 10:25
Sorry it's MS Access. I think i need a way to connect two Comboboxes to the For Loop. For Date = "' & Combobox1 & '" To "' & Combobox2 & '" ... Next Date – Flow74 Sep 06 '16 at 11:28
It will not work as expected unless you format the string expressions for the date values correctly: `"' , #" & Format(dtCurrent, "yyyy\/mm\/dd") & "#, '"`. – Gustav Sep 06 '16 at 13:46
@Gustav : You are a bit too much sure of something unknown here. It really depends if its date column type is declared as string or date. I have assumed it is string and it looks like I was correct. – Thomas G Sep 06 '16 at 13:52
Then you should have stated that. The data type for dates should always be _DateTime_. – Gustav Sep 06 '16 at 15:15

VBA: Insert ascending Date in Table

1 Answers1