Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Add records with empty dates to calendar

Posted on 2004-10-25
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 58

Expert Comment

ID: 12400456
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

shanesuebsahakarn earned 2000 total points
ID: 12400462
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

ID: 12400471
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.
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

LVL 58

Expert Comment

ID: 12400525
Oops, shanesuebsahakarn read the question better than me :)

Author Comment

ID: 12404297

Worked first time out.  Thanks so much.


Author Comment

ID: 12404312
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.   :-)

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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 …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

597 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