Add records with empty dates to calendar

Posted on 2004-10-25
Last Modified: 2008-02-01
The problem is to insert “empty dates” in a table containing information about The Boss’s calendar.

The table is called CleanCalendar.  It has the following fields:
StartDate (date/time)
Subject (text)
StartTime (date/time)
Location (text)
EndTime (date/time)
Description (text)

The Boss likes to have a space on his hard copy calendar for every day for the next six months, whether a given day has an appointment or not.

The appointments are being pulled in from his Outlook Calendar but Outlook only creates a record if there’s an appointment on that day, so I need to fill in the “empty dates.”

If  I were writing pseudo code for this it would look like:

For MyDay = Today to Today’s Month Plus 6
    Find Record where StartDate = MyDay
       If Not Found, Insert Record Where StartDate = MyDay
Next MyDay

Can I do this somehow in Access?
Question by:prettykittyq
    LVL 58

    Expert Comment

    The easiest solution is probably to create a table containing a full calendar, i.e. a single field datDate, date/time, key field. Let's call it ztblCalendar.

    You can fill this table thus:

    Sub FillCalendar(pintYear as Integer)

       Dim datCurrent as Date
       Dim datStop as Date

       With CurrentDb
          .Execute "DELETE * FROM zstblCalendar"
          datCurrent = DateSerial(pintYear, 1, 1)
          datStop = DateSerial(pintYear+1, 1, 1)
          Do Until datCurrent = datStop
             .Execute "INSERT INTO zstblCalendar (datDate) VALUES(" & CLng(datCurrent) & ")"
             datCurrent = datCurrent+1
       End With

    End Sub

    (code not tested)

    Then create a report based on a join query (zstblCalendar LEFT JOIN CleanCalendar) which will produce at least one record per day and Null fields for days without any appointment.

    Another solution would be to create a report based on your table, and using the Me.MoveLayout and Me.NextRecord, along with hiding fields etc. This way, you could force the report to produce one line for each day, printing the information in non-hidden fields.
    Cool programming perhaps, but neither efficient nor easy to maintain.
    LVL 41

    Accepted Solution

    You've almost got it:

    Dim MyDate As Date
    Dim db As DAO.Database

    Set db=CurrentDb
    For MyDay=Date() To DateSerial(Year(Date),Month(Date)+6,1)
       If IsNull(DLookUp("[StartDate]","CleanCalendar","[StartDate]=#"  & Format(MyDay,"mm/dd/yyyy") & "#")) Then
          db.Execute "INSERT INTO CleanCalendar (StartDate) VALUES(#" & Format(MyDay,"mm/dd/yyyy") & "#)"
       End If

    That should do it.
    LVL 58

    Expert Comment

    In the report, create a header for each day to group appts for the day, or set the "HideDuplicates" to true to avoid printing duplicate dates.
    LVL 58

    Expert Comment

    Oops, shanesuebsahakarn read the question better than me :)
    LVL 2

    Author Comment


    Worked first time out.  Thanks so much.

    LVL 2

    Author Comment

    ooops...except for the tiny typo in the first DIM statement...needs to be MyDay not MyDate to match the rest of the code

    But I caught that all by myself.   :-)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now