Calendar Enhancement - How To Build a Fully Functional Calendar Form and Printable Monthly Report in Microsoft Access


	Back to Calendar Seminar Lessons Comments List

Calendar Enhancement

Teeka Leone

5 years ago

Hi Richard. LOVED the Calendar Seminar. It really helped with a work project but I want to expand on it. One of the columns in the appt. list box is called "Type". The type effects scheduling capacity for that day so I'd like to show each type's count for the day in a textbox as stop light colors. I got the color part working, (yay)! The SELECT COUNT statement works in SQL query view but not the text box row source. I tried using DCOUNT in the default value field instead. Not sure what I'm doing wrong or maybe it can't be done? I have screenshots if needed

	Teeka Leone OP @Reply 5 years ago

	Richard Rost @Reply 5 years ago
Type is a reserved word. Either change the field name or you have to remember to put brackets around it everywhere. [Type]

	Teeka Leone OP @Reply 5 years ago
Ahhhhhhh! Brilliant!

Teeka Leone OP @Reply

5 years ago

Hi Richard. I'm sure that was part of the problem but it still doesn't work after changing the field name from "Type" to "RelType" in the table, the query and the form's list box control source. I can't find anything specific to this requirement in your videos. How can I count the items in the List box for that day where RelType = "value"? I read something somewhere about Listbox.ListCount property but don't know how to use it. Pulling my hair out!!!

	Adam Schwanz @Reply 5 years ago
What about DCount? DCOUNT("ApptID","CalendarQ","DateTime>=" & Forms!DatePickerF!StartDate+18 & " and DateTime<" & Forms!DatePickerF!StartDate+19 & " and RelType=""" & Major & """")

	Adam Schwanz @Reply 5 years ago
I think It's actually this, string quotes are fun ;) DCOUNT("ApptID","CalendarQ","DateTime>=" & Forms!DatePickerF!StartDate+18 & " and DateTime<" & Forms!DatePickerF!StartDate+19 & " and RelType=""Major""")

	Scott Axton @Reply 5 years ago
There are times when it would be nice to have an edit button huh Adam? LOL

	Adam Schwanz @Reply 5 years ago
Oh yes lol

	Scott Axton @Reply 5 years ago
Adam - Sticking my nose in the middle. Don't you have to surround the date field with #? DCOUNT("ApptID","CalendarQ","DateTime>= #" & Forms!DatePickerF!StartDate+18 & "# and DateTime< #" & Forms!DatePickerF!StartDate+19 & "# and RelType=""Major""")

	Adam Schwanz @Reply 5 years ago
Hey would you look at that, now there's 2 times I wish I had an edit button :) LOL I think you're right

	Alex Hedley @Reply 5 years ago
Oh it's nice to have an edit button :p

	Scott Axton @Reply 5 years ago
Alex - Show off!

	Teeka Leone OP @Reply 5 years ago
IT WORKED!!! Thank you ALL! This is Awesome! I NEVER would have figured that one out. Too many quotes, ampersands and parenthesis!

	Scott Axton @Reply 5 years ago
Teeka - I find it helps when ever I have a difficult thing like that to put in "hard" data and make sure it works. Then one by one convert the formula to using the variables.

	Richard Rost @Reply 5 years ago
Edit button is coming, guys. Only so many hours in the day. :/

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Calendar Seminar Lessons.

Next Unseen