Access 2010 unable to use multiple dates in record in SQL SELECT statement

I've been pulling my hair out for 2 weeks trying to overcome this issue. I am creating a calendar form in an access 2010 database. I have a set of text boxes that populate the date in the month depending on which month is displayed. That part works good. Underneath each text box, I have a list box to populate last name, first initial and text.

I am using the code from http://www.tek-tips.com/faqs.cfm?fid=838 to build the text boxes for the calendar. I am reusing the same code for the listboxes, except I changed curday to boxday and I am putting it into a different sub. I can get the correct information to display for 5 of the dates, which are the same type of text. I have 3 other dates which display a different text after the name and I can't get those to work. I know I am going about this the wrong way.

[FirstDate] is the current month and year in mmmm /yyyy format.

Here's the code that works:

Please Login or Register  to view this content.

I figured once I could get one of the next dates to populate along with the SVA, SVB, SVC, SVD, and SVE dates, I could add the remaining date fields I need. I know my ColumnCount is wrong, but that doesn't seem to affect how it works. What I am trying to add is another SELECT statement. I've tried using UNION, but then nothing displays. I've learned that UNION statements have to have the same amount of fields so I tried it this way, but nothing displays in the calendar:

Please Login or Register  to view this content.

I am guessing that my UNION statement can't have this type of WHERE clause in it. If I remove the WHERE clause, then all the records return with the name and R for every date.

Let me know if you need any further info to help me out. Thanks!

Ok, I finally figured out what I was doing wrong. Of course, it was an error on my part. I was placing the UNION SELECT statement in the wrong section of the code, so it was only being read before the loop, not during the loop. I also realized I could remove all the references to "qryAppointmentPatient" since that was the only query I was pulling records from.

Here is the working version (for those of you who might benefit from my mistake):

Please Login or Register  to view this content.