MS Access Calendar

[nerd4access]

I am in the United States.

I adjusted the code as indicated above to see if that would make a difference, but there was not change in the outcome.

I have converted the database to the 2003 version as you requested.

It seems that this is a SQL Server issue as the information displays properly when the table is in access instead of linked through an ODBC connection to the SQL Server.

For example:

The database connected to the SQL Server does not properly display the data for 11/2/2010. The data is displayed properly in the list box, but it is not shown in the calendar grid.

In the attached database, the data for 11/2/2010 is displayed properly in the list box and the calendar grid.

[ADezii]

I'm at a real loss on this one. The only thing that I can come up with is the fact that in the SQL Statement that defines the Recordset that populates the Calendar, lngFirstOfMonth and lngLastOfMonth are actually LONG INTEGER values and maybe may need to be coerced to Date. This would hardly explain, of course, why only a few Dates are not properly displayed.

strSQL = "SELECT * From dbo_vwCalendar "
strSQL = strSQL & "WHERE dbo_vwCalendar.[MeetingDate] Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
                  " ORDER BY dbo_vwCalendar.[EventID];"

[nerd4access]

Thanks so much for your help. I will keep searching for a solution.

[NeoPa]

ADezii, if you tell me what the field type of [MeetingDate] is, and post the resultant SQL produced where you know it is failing, then I will look into what is going wrong here for you. I see this as a very important thread and will be happy to help if I can.

[nerd4access]

[MeetingDate] is a datetime field. I have attached a screen shot of the calendar connected to the SQL Server.

As mentioned in a previous post, you can see that November 2, 2010 is selected on the calendar. The box in the calendar grid is empty, but the list box displays all of the events that take place on that date.

[imgnothumb]http://bytes.com/attachments/attachment/4228d1290615330/calendar_meetin gdate.jpg[/imgnothumb]

[ADezii]

This is a tough one, NeoPa, and as always you help will be greatly appreciated. Per the OP:

1. The Code works as intended when the Back End is converted to Access 2003 Format.
2. In SQL Server Format, only a few Dates do not display properly, but only in the Calendar itself, and NOT in the Events List Box.
3. The Code is quite extensive, but I'll post below what I feel are the relevant issues, namely:
   1. The SQL Statement that will create the actual Recordset that will populate a String Array, which in turn provides the Calendar with the actual Data for display on each Date. This code is posted below (strEvent is the String Variable that directly writes Data to the Calendar Blocks):
      

      Private Sub PopulateCalendar()
      '...Code intentionally omitted
      Set db = CurrentDb
      
      strSQL = "SELECT * From dbo_vwCalendar "
      strSQL = strSQL & "WHERE dbo_vwCalendar.[MeetingDate] Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
                        " ORDER BY dbo_vwCalendar.[EventID];"
      
      Set rstEvents = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)    'Added 4/16/2008
      
      Do While Not rstEvents.EOF
        strEvent = "[" & rstEvents![EventID] & "] - " & rstEvents![EventTitle]
        bytEventDayOfMonth = (rstEvents!MeetingDate - lngLastOfPreviousMonth)
        bytBlockCounter = bytEventDayOfMonth + bytBlankBlocksBefore
          If astrCalendarBlocks(bytBlockCounter) <> "" Then
            astrCalendarBlocks(bytBlockCounter) = _
            astrCalendarBlocks(bytBlockCounter) & vbNewLine & strEvent
          Else
            astrCalendarBlocks(bytBlockCounter) = strEvent
          End If
          rstEvents.MoveNext
      Loop
      '...Code intentionally omitted

   2. The Sub-Routine that populates the Events List Box which is 'ALWAYS' correct.
      

      Private Sub PopulateEventsList(ctlDayBlock As Control)
      On Error GoTo Err_PopulateEventsList
      Dim strSQL2 As String
                                
      strSQL2 = "SELECT dbo_vwCalendar.[EventID] As [ID], dbo_vwCalendar.[EventTitle] As [Event Title], " & _
                "dbo_vwCalendar.[MeetingDate] As [Meeting Date], " & _
                "dbo_vwCalendar.[EndDate] As [End Date]" & _
                "FROM dbo_vwCalendar WHERE dbo_vwCalendar.[MeetingDate] = " & _
                ctlDayBlock.Tag & " ORDER BY dbo_vwCalendar.[EventID];"
      
      lstEvents.RowSource = strSQL2
      
      lblEventsOnDate.caption = Format(ctlDayBlock.Tag, "m-dd-yyyy")
      lstEvents.Visible = True
      lblEventsOnDate.Visible = True
          
      Exit_PopulateEventsList:
        Exit Sub
        
      Err_PopulateEventsList:
        MsgBox Err.Description, vbExclamation, "Error in PopulateEventsList()"
        Resume Exit_PopulateEventsList
      End Sub

4. If you can solve this one, you get a giant Chocolate Chip Cookie, presently I am stumped. It definitely appears to be an SQL, and not Access, issue.

@nerd4access - If there is any critical information that I have omitted or misrepresented, please make both NeoPa and myself aware of this.

@nerd4access - Just out of curiosity, remove the dbSeeChanges Option in Post #354, Code Line# 9, and see if it makes a difference.

[nerd4access]

@Adezii - your description of the issue is accurate. The issue always surrounds the 1st and 2nd of the month. It appears that the issue arises with the 15th and the 22nd on occasion. It is quite strange that it only affects those dates.

[ADezii]

@nerd4access - Have you tried removing the dbSeeChanges Option in Post #354, Code Line# 9, to see if it makes a difference?

@nerd4access - One important highlight, with NeoPa on the case, you got the best helping you!

@NeoPa - What do you think about asking the SQL Server Guys to have a look at this specific problem, if we cannot get it resolved?

[nerd4access]

@Adezii - Removing the dbSeeChanges Option provided the same result.

I feel very fortunate to have the help of both you and NeoPa as I am self taught in MS Access, VBA, and SQL.

Any help that you can provide is greatly appreciated.

[NeoPa]

That's good info ADezii, but if you could post the resultant SQL produced where you know it is failing, that would be even more help. I'm guessing your using integral values to represent dates, but without the exact SQL produced I'm either guessing or reading up on the thread so far - which might prove quite a lot of work after >350 posts :-D

My initial thinking is that such a format for checking dates is not a supported one and that the DateValue() function may be a more appropriate alternative, but I'm largely guessing with limited knowledge of what you're really doing so I can't be sure.

This might be a good reason to get Skype going and we can discuss things in greater detail. Still your call of course (I really don't want to press), but seems a particularly appropriate juncture.

[nerd4access]

I am not exactly sure where SQL is failing. I do not receive an error message of any kind and the data in the linked table is accurate.

Let me see if I can provide some background:

My tables for the access front end are linked to a database on the SQL Server (2005). I wanted to query the data in SQL before pushing it to the front end to make the database run faster. With that being said, I created a view called 'dbo.vwCalendar '.

My SQL Statement for 'dbo.vwCalendar ' is as follows:

SELECT DISTINCT 
                      dbo.tblEventSetup.SetupID, dbo.tblEvents.EventID, CASE WHEN OptEventName IS NULL THEN EventName WHEN OptEventName IS NOT NULL 
                      THEN OptEventName END AS EventTitle, dbo.tblEvents.Status, dbo.tblEventSetup.MeetingDate AS MtgStartDate, 
                      dbo.tblEvents.EndDate AS MtgEndDate, CASE WHEN dbo.tblEventSetup.ProgramStartTime IS NULL THEN CONVERT(varchar, 
                      dbo.tblEvents.ProgramStartTime, 108) WHEN dbo.tblEventSetup.ProgramStartTime IS NOT NULL THEN CONVERT(varchar, 
                      dbo.tblEventSetup.ProgramStartTime, 108) END AS ProgramStart, CASE WHEN dbo.tblEventSetup.ProgramEndTime IS NULL THEN CONVERT(varchar, 
                      dbo.tblEvents.ProgramEndTime, 108) WHEN dbo.tblEventSetup.ProgramEndTime IS NOT NULL THEN CONVERT(varchar, 
                      dbo.tblEventSetup.ProgramEndTime, 108) END AS ProgramEnd, 
                      dbo.tblEventSetup.MeetingDate + 2 + CASE WHEN dbo.tblEventSetup.ProgramStartTime IS NULL 
                      THEN dbo.tblEvents.ProgramStartTime WHEN dbo.tblEventSetup.ProgramStartTime IS NOT NULL 
                      THEN dbo.tblEventSetup.ProgramStartTime END AS StartTime, dbo.tblEvents.EndDate + 2 + CASE WHEN dbo.tblEventSetup.ProgramEndTime IS NULL 
                      THEN dbo.tblEvents.ProgramEndTime WHEN dbo.tblEventSetup.ProgramEndTime IS NOT NULL 
                      THEN dbo.tblEventSetup.ProgramEndTime END AS EndTime, dbo.tblEvents.Category, dbo.tblEventSetup.MeetingDate, dbo.tblEvents.EndDate 

FROM         dbo.tblEvents INNER JOIN
                      dbo.tblEventSetup ON dbo.tblEvents.EventID = dbo.tblEventSetup.Event
WHERE     (dbo.tblEvents.Status <> 'Cancelled') AND (dbo.tblEvents.Status <> 'Released')

I included a copy of the data produced from this view in the database attached to post #349

Does this information help?

Note: I wasn't sure if I should post the select statement as code, but I did it any just to be sure.

[NeoPa]

That's good background information, but probably not quite what we're looking at for now.

My main interest now is in looking at the exact SQL produced by the code in post #350. When I have that I will do what tests I can locally to determine what I would expect to see happening, and we can go from there. Not having access to all your SQL Server data may prove a problem, but we'll handle that when we get that far.

As for your view though, did you notice that on lines #9 through #11, where [StartTime] is calculated, you check for dbo.tblEventSet up.ProgramStart Time being NULL, then add the field whether it is or not?

It's clear from the view's SQL though, that [MeetingDate] is passed through unaffected, from the source (dbo.tblEventSet up), which is helpful to know.

[ADezii]

@nerd4access - I do believe that I may have arrived at a possible solution, but before I get too excited and explain how it came about, I'll let you make the final decision. It appears as though all LONG INTEGER Date representations , when they appear in SQL Statements, have to be explicitly converted to Dates and Delimited. I am referring to the lngFirstOfMonth and lngLastOfMonth Variables, as well as the Tag Property of each Control Block. Try the following two fixes, then let me know:

1. In the PopulateCalenda r() Sub-Routine, change the SQL Statement to:
   

   strSQL = "SELECT * From dbo_vwCalendar "
   strSQL = strSQL & "WHERE dbo_vwCalendar.[MeetingDate] Between #" & CDate(lngFirstOfMonth) & "# And #" & CDate(lngLastOfMonth) & _
                     "# ORDER BY dbo_vwCalendar.[EventID];"

2. In the PopulateEventsL ist() Sub-Routine, change the SQL Statement to:
   

   strSQL2 = "SELECT dbo_vwCalendar.[EventID] As [ID], dbo_vwCalendar.[EventTitle] As [Event Title], " & _
             "dbo_vwCalendar.[MeetingDate] As [Meeting Date], " & _
             "dbo_vwCalendar.[EndDate] As [End Date]" & _
             "FROM dbo_vwCalendar WHERE dbo_vwCalendar.[MeetingDate] = #" & _
             CDate(ctlDayBlock.Tag) & "# ORDER BY dbo_vwCalendar.[EventID];"

3. Good Luck, and let us know how you make out.

[NeoPa]

... not forgetting of course, that date strings should always be formatted into SQL date format (See Literal DateTimes and Their Delimiters (#)) to insert into a SQL string. Omitting the formatting only works when the code is run in the USA and locales where the date format matches the SQL standard format of m/d/y. In other locales this will fail in some places, and be very difficult to spot (as it will always determine the intention unless the date is entirely ambiguous - EG 1/2/2010, where you would need to know the format used to interpret it correctly, but not 13/1/2010, as that would never occur naturally in SQL format so it will realise the error and handle it correctly anyway).

[nerd4access]

@Adezii - I tried the fixes you suggested. With those changes neither the calendar nor the list box were populated with data; however an error was not produced.

I thought it may be useful to add a screenshot of the SQL produced for dbo_vwCalendar (the view on which the calendar form is based).

@NeoPa - I am not sure if I have provided a good answer to your inquiry about "where SQL is failing". Can you provide some more insight as to what information you may need? Also, I spoke with our IT guy this morning in regards to a Skype call. He said that would be fine if needed and he is checking with our network folks to see if we can do a WebX session if that is needed as well.

Again, thank you both for providing such wonderful assistance. I am sure everyone that has reviewed this very informative thread appreciates your insight and hard work.