How to Create Calendar Reports from Access Queries with dates! ... as shareable Web Pages
ฝัง
- เผยแพร่เมื่อ 26 ก.ค. 2017
- 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 link 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.
Download free tool:
msaccessgurus.com/tool/HtmlCa...
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.
add your own Error Handling
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 wonderful.
Please Like, Comment, and Share with your friends. Thank you.
Through sharing, we will all get better.
respectfully,
crystal
msaccessgurus.com/
~ have an awesome day ~ - วิทยาศาสตร์และเทคโนโลยี
![อาจารย์ยอด : สวยกินคน [ผี]](http://i.ytimg.com/vi/_-TIJOwkRA4/mqdefault.jpg)
omg I always wanted to do this and stumbled upon your post. 30 minutes...Done! Beautiful. Thank you! PS. most Access how-to videos are boring...love the creative ideas you bring.
you're welcome and thank you, Danielle ~ happy to help
Thanks for the Calendar Fun!!!
thanks, Mike! ... it is so much fun visualizing data :) oh what geeks we are ~
thank you, Mike!
Thank you. It is very help to me. Great presentation.
you're welcome and thank you, Kazi ! Happy you are using Access!
thank you for creating this fabulous video. Unfortunately I had a very hard time following. Do you have other calendar tutorials related to calendar creation (couldnt find any)?
you're welcome and thank you! This is the only one I have posted so far
help, I have one table with all my dates sequentially, I need to read 31 records vertically anywhere in my table based on year and month and create calendar based on 31 records selected from that one table.
Thank you for the calendar and video, this is impressive. Is there a way to add a 'Text4' to the days? Also, the time is in long format, can you change it to short format?
you're welcome and thank you! Yes, whatever you want can be added, and you have the source code to change. If you want me to teach you how, we can connect -- I do private tutoring. Visit msaccessgurus.com/
Thanks for this tool. I am curious how hard it would be to display weekly calendar reports? Do you mind pointing me in the general directions how I should go about it? Thank you.
you're welcome and thanks! I get asked that a lot but so far, I haven't had time to do it ... I could make the time though, if there was some compensation ~
I know this is old and Access is outdated, but we still use it and your code worked amazing..only question (am I'm a novice) is how can i get it to opening a different browser? It seems to open in Internet Explorer which is no longer used and i cant figure out where i can change that?
thank you and you're welcome. To change your default browser, right-click on an HTM file in File Explorer, choose Open With …, pick what you want, and check the box at the bottom to always use that application to open those types of files.
Is there a way to create a report where you can schedule payment due dates for someone 6 months out? and be able to print that?
Thanks so much for the help. This has really helped us go in the right direction.
you're welcome, happy to help! re: "schedule payment due dates for someone 6 months out?" Yes, you can make a calendar using a query. I often use a Numbers table to project records for a query, and you could use that for months 1-6. A more in-depth discussion of how to do this is too complex to write here though. Visit my website and contact me if you want me to help you. msaccessgurus.com
This is really great and very useful. Have you ever found a way of doing this for more than one month?
thanks, Dave. I have another version that that does a range, but haven't had a chance to make it public yet.
this CalendarMaker does more than one month: msaccessgurus.com/tool/CalendarMaker.htm I'm still working on it, so its not hooked up to the menu yet, but what is posted works.
is it possible to take 31 sequential records vertically from one table and write to a 2nd temporary table horizontally all 31 records based on year and month input?
hi Jack, sorry I just saw your comments. I posted a CalendarMaker that will also print data from a query ... still changing it, so this isn't the final version, but it works :) msaccessgurus.com/tool/CalendarMaker.htm
thanks alot. can you help me to make patients appointment calender ic access
you're welcome, and thank you. Simply make a query, as demonstrated. For information you want to show on the calendar, assign field names for CalDate (required), CalTitle (optional), Text1 (optional), Text2 (optional), and Text3 (optional). Try it! If you have issues, post the SQL for your query and describe how you are launching the calendar, thanks.
Download no longer available?
oops, switched web hosting -- link is fixed!
@@LearnAccessByCrystal The download link is still not working
finally got it right! msaccessgurus.com/tool/HtmlCalendarReport.htm
updated video description too