<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

How to Create Calendar Reports from Access Queries

Posted on
6,853 Points
653 Views
2 Endorsements
Last Modified:
Experience Level: Intermediate
9:12
crystal (strive4peace) - Microsoft MVP, Access
Love empowering people by teaching and helping them develop applications.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen.

Visualize your data!  ... really see it

To use the code to create a calendar from a query, simply import the (only) module (mod_HtmlCalendarReport_s4p) from the download (see Step 1, below)  into your working database (and then compile and save, of course). You can get the same code by choosing File, Import in the VBE (Visual Basic Editor -- Alt-F11) and choosing the BAS file.  

Importing VBA doesn't, of course, include easy-to-launch macros (even though the macros run VBA procedures) ... triggering them is a snap -- especially if you use a menu form with buttons and other controls.  

Please, as you make changes, rename the 'test' procedures to have more meaningful names depending on how you customize them and how they are used. And add your own comments ... and indent?

If you feel like sharing, your ideas are welcome, and appreciated.

Using vba:
Application.FollowHyperlink Create_HtmlCalendar(sQueryName)
  • will open a web page with the calendar created from the query specified by sQueryName
  • The Create_HtmlCalendar function creates the calendar as an HTML file (web page)
  •   it returns the path and filename when done.
  • You have a calendar file you can email and share with others.
  • you may wish to add VBA Error Handling (Basics video)

What do you need to know to use this feature?

If you are a seasoned expert, no explanation needed ...

but for those of you who want to use this ... but don't quite understand yet -- take a breath and see things from a different perspective. Don't be overwhelmed.  It is quite simple, once you get past the code thing. Just line up the data you want to show in the calendar using a query. Name the fields what is expected. Define some field, or calculation, to be CalDate, and then, optionally, the calendar title (CalTitle), and up to 3 pieces of text (Text1, Text2, Text3) to show on days.

Once you have the query ... use it to make a calendar. You do not need to understand each line of this code to use this feature in YOUR databases ... only how to call it to make it give you what you want

... but if you want to take the time to understand, that is great.  Ask questions below if you need help -- and remember, if you don't know, chances are that someone else doesn't either. I want to give you a good tool -- and share my love of Access. Realize that if you do ask questions, the 'answers' may just lead you to find the answers yourself -- learning never stops.

to make things really easy ...
There is a macro in the sample database that asks for a query name and then ... presto! a calendar is displayed!

... and it even works for parameter queries, which I do say lightly because not near enough testing was done. If you can reproduce a problem (for instance, date might be a problem for non_American and non-ISO formats), please post a comment specifying the details, and let's all fix it.

A Calendar Report for one month will be made as an HTML file (web page) -- so everyone with a browser, which is just about everyone! can see it too!

It just doesn't get any easier than this! Access is such a wonderful tool.

Please Like, Comment, and Share with your friends. Thank you.

Through sharing, we will all get better.

respectfully,
crystal
 
~ have an awesome day ~

Video Steps

1. Download sample database or BAS file


   Access Database: inside HTML_Calendar_170727_ACCDB.zip
   BAS module: inside  mod_HtmlCalendarReport_s4p__BAS.zip

2. Import the module, mod_HtmlCalendarReport_s4p, into your working database

3. Optionally, import the 2 macros as well

4. Make a query containing fieldnames:


  •   CalDate - required
  •   CalTitle - optional
  •   Text1 - optional
  •   Text2 - optional
  •   Text3 - optional

5. Sort by CalDate in Ascending order

6. Save query and copy the name

7. Run the mcr_CALENDAR-From-Query macro and paste name of query when prompted

8. ... OR call the Create_HtmlCalendar function


  • Query name or SQL is a required parameter
  • Path and File name, or part of a file name, are optional

9. Here is an example of the output:

Calendar Report as a web page
Determine the Perfect Price for Your IT Services
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

2
Comment
  • 2
3 Comments

Expert Comment

by:susan nichols
I was wondering if there's a way to have it show multiple months at a time?
1
LVL 24
hi susan,

yes, there is a way to output multiple calendar pages, if you modify the code to loop. I do this sometimes too -- just didn't want to make the example too complicated. Since the code is open, you can see it and modify it -- just after nDate1 is set. In the loop, test YYMM to see if a file needs to be finished and a new one needs to be started. I'd be happy to answer questions if you want to tackle it yourself.

What will you be using this for?

thanks for commenting!
~crystal
1

Expert Comment

by:susan nichols
Thank you for your answer! Currently I'm handling travel for our division, so I'm using this to generate calendars of upcoming travel and conferences so other departments can easily see who is going where and when since they have to do things on those dates. I've got it working perfectly doing the single month, but being of the "I want my db to do the thing and then googling how to do the thing" variety of access user, I'm not sure how to start with your instructions (this is sad, I know this is sad).
0

Featured Post

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Join & Write a Comment

Know the 5 most critical benefits of Microsoft Office 365 Exchange online for business, enterprise, education, and students. Also, be aware of Office 365 Challenges and how to backup data safely.
Switching from G suite to Office 365? Here are the top 5 reasons you need know for G Suite to Office 365 migration, comparing all the possible factors of both Google Apps and Exchange Online.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month