Setting Up a Calendar blanks - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Setting Up a Calendar blanks

William Kennedy

3 years ago

T need to load a table, CalendarT with blank records except for the Date field, DateTime. The records start Jan 1 and end Dec 31 of the year listed in Text box YearNo. I can't seem to get it to work. It seems simple but is not.

	Sami Shamma @Reply 3 years ago
William do you know VBA and SQL? if yes you can use a FOR/NEXT loop to insert into the table with one SQL statement. if you can not do it I will write the code for you.

Kevin Robertson @Reply

3 years ago

A simple For Next Loop but you do need to know how many days are in that particular year.

    Dim X As Long, DaysInYear As Long, StartDate As Date

    

    StartDate = DateSerial(txtYear, 1, 0)

    DaysInYear = DateDiff("d", StartDate, DateSerial(txtYear, 12, 31))

    

    For X = 1 To DaysInYear

        StartDate = DateAdd("d", 1, StartDate)

        CurrentDb.Execute "INSERT INTO CalendarT([DateTime]) " & _

            "VALUES(#" & StartDate & "#)"

    Next



    MsgBox "Dates Added!", vbInformation, "Success"

	Kevin Robertson @Reply 3 years ago
I would also add some error handling to the above code to ensure a valid year is entered in the Text Box. For example: `If Not IsNumeric(txtYear) Or InStr(txtYear, ".") <> 0 Or Len(txtYear) <> 4 Then MsgBox "Please enter a valid 4 digit year.", vbCritical, "Invalid Year" Exit Sub End If`

	William Kennedy OP @Reply 2 years ago
thanks

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View