Add records with empty dates to calendar

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?
LVL 1
prettykittyqAsked:
Who is Participating?
 
shanesuebsahakarnConnect With a Mentor Commented:
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
Next

That should do it.
0
 
harfangCommented:
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
      Loop
   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.
0
 
harfangCommented:
Forgot:
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.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
harfangCommented:
Oops, shanesuebsahakarn read the question better than me :)
0
 
prettykittyqAuthor Commented:
Shane!

Worked first time out.  Thanks so much.

Kitty
0
 
prettykittyqAuthor Commented:
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.   :-)
0
All Courses

From novice to tech pro — start learning today.