Acces calander with mysql

[dannyflee]

Hello,

I've used the calendar from the post:


http://bytes.com/topic/access/answer...ccess-calendar

It works great and was just the thing i was looking for. Props for those who made it. But i have one problem.

The table from where the calander gets it data is mysql
and it doesn't show any dates. in the calander boxes.

If i convert the table to a local table everything works just fine. I think it has something to do with de date notation in mysql (YYYY-MM-DD) and when i convert it to a local (access) table access converts the datenotaion back to (dd-mm-yyyy).

The strange thing is that the date in Mysql is yyyy-mm-dd, but is shown in access as dd-mm-yyyy. So access somehow already converts it to the correct natation. Sadly the calendar doesn't work.

I've thougt of 2 options:
- Make an ajustment in the VBA code so it works.
- Make a local (acces) table with the relevant data for the calender that is in sync with the Mysql table.

But in both cases i'm stuck.

Any help is appreciated

[zmbd]

OK, as you've discovered: Internally, the date format that access uses, no matter what the local date setting is, is in the "MM/DD/YYYY HH:MM:SS" format.

Make sure that you are using "#" around your dates.

If you will please post the code where you made the change to reference the MYSQL table within the calendar it would be most helpful.

Please make sure to format the code using the [CODE/] button on the toolbar.

[dannyflee]

Thanks for your reply,

What do you mean by putting "#" around the dates. Can you be more specific.

This is the code to populate the calendar:

Private Sub PopulateCalendar()
On Error GoTo Err_PopulateCalendar
Dim strFirstOfMonth As String, bytFirstWeekdayOfMonth As Byte, bytBlockCounter As Byte
Dim bytBlockDayOfMonth As Byte, lngBlockDate As Long, ctlDayBlock As TextBox
Dim bytDaysInMonth As Byte, bytEventDayOfMonth As Byte, lngFirstOfMonth As Long
Dim lngLastOfMonth As Long, lngFirstOfNextMonth As Long, lngLastOfPreviousMonth As Long
Dim lngEventDate As Long, bytBlankBlocksBefore As Byte, bytBlankBlocksAfter As Byte
Dim astrCalendarBlocks(1 To 42) As String, db As DAO.Database, rstEvents As DAO.Recordset
Dim strEvent As String
Dim lngSystemDate As Long   'CFB added 1-25-08
Dim ctlSystemDateBlock As TextBox, blnSystemDateIsShown As Boolean  'CFB added 1-25-08
Dim strSQL As String        'Added 4/16/2008
Dim lngFirstDateInRange As Long     'CFB added 2-18-10
Dim lngLastDateInRange As Long      '
Dim lngEachDateInRange As Long      '
Dim strStartTime As String          '
 
lngSystemDate = Date        'CFB added 1-25-08
intMonth = objCurrentDate.Month
intYear = objCurrentDate.Year
lstEvents.Visible = False
lblEventsOnDate.Visible = False
lblMonth.Caption = MonthAndYear(intMonth, intYear)
strFirstOfMonth = Str(intMonth) & "/1/" & Str(intYear)
 
'*************************************************************************
  'ADezii
  'NOTE: Will work in the UK (United Kingdom) and other European Nations
  'strFirstOfMonth = "1/" & Str(intMonth) & Str(intYear)
 
 
bytFirstWeekdayOfMonth = WeekDay(strFirstOfMonth)
lngFirstOfMonth = DateSerial(intYear, intMonth, 1)
lngFirstOfNextMonth = DateSerial(intYear, intMonth + 1, 1)
lngLastOfMonth = lngFirstOfNextMonth - 1
lngLastOfPreviousMonth = lngFirstOfMonth - 1
bytDaysInMonth = lngFirstOfNextMonth - lngFirstOfMonth
bytBlankBlocksBefore = bytFirstWeekdayOfMonth - 1
bytBlankBlocksAfter = 42 - (bytBlankBlocksBefore + bytDaysInMonth)
 
Set db = CurrentDb
 
strSQL = "SELECT sales1.naam_klant, sales1.woonplaats, sales1.date, tblVisitType.Type, tblVisitType.Code, sales1.time " & _
         "FROM tblVisitType INNER JOIN sales1 ON tblVisitType.TypeID = sales1.TypeID " & _
         "WHERE sales1.date Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
         " ORDER BY sales1.time, sales1.naam_klant, sales1.woonplaats;"
 
Set rstEvents = db.OpenRecordset(strSQL)    'Added 4/16/2008
 
Do While Not rstEvents.EOF
  'CFB added 2-18-10
  lngFirstDateInRange = rstEvents![Date]      '<Substitute for [Start Date]>
  If lngFirstDateInRange < lngFirstOfMonth Then
    lngFirstDateInRange = lngFirstOfMonth
  End If
  lngLastDateInRange = rstEvents![Date]         '<Substitute for [End Date]>
  If lngLastDateInRange > lngLastOfMonth Then
    lngLastDateInRange = lngLastOfMonth
  End If
 
  For lngEachDateInRange = lngFirstDateInRange To lngLastDateInRange
    bytEventDayOfMonth = (lngEachDateInRange - lngLastOfPreviousMonth)
    bytBlockCounter = bytEventDayOfMonth + bytBlankBlocksBefore
                                              '<Substitute for [Title]>
      If astrCalendarBlocks(bytBlockCounter) = "" Then
        astrCalendarBlocks(bytBlockCounter) = Format$(rstEvents![Time], "hh:nn AM/PM") & vbCrLf & rstEvents![naam_klant] & ", " & _
                                              Left$(rstEvents![Woonplaats], 1) & "." & " [" & rstEvents! & "]"
      Else                                    '<Substitute for [Title]>
        astrCalendarBlocks(bytBlockCounter) = astrCalendarBlocks(bytBlockCounter) & vbNewLine & _
                                              Format$(rstEvents![Time], "hh:nn AM/PM") & vbCrLf & rstEvents![naam_klant] & ", " & _
                                              Left$(rstEvents![Woonplaats], 1) & "." & " [" & rstEvents! & "]"
 
      End If
  Next lngEachDateInRange
  'End of CFB added 2-18-10
 
    rstEvents.MoveNext
Loop
 
For bytBlockCounter = 1 To 42                           'blank blocks at start of month
  Select Case bytBlockCounter
    Case Is < bytFirstWeekdayOfMonth
      astrCalendarBlocks(bytBlockCounter) = ""
      ReferenceABlock ctlDayBlock, bytBlockCounter
      'ctlDayBlock.BackColor = 12632256
      ctlDayBlock.BackColor = 8421440
      ctlDayBlock = ""
      ctlDayBlock.Enabled = False
      ctlDayBlock.Tag = ""
    Case Is > bytBlankBlocksBefore + bytDaysInMonth     'blank blocks at end of month
      astrCalendarBlocks(bytBlockCounter) = ""
      ReferenceABlock ctlDayBlock, bytBlockCounter
      'ctlDayBlock.BackColor = 12632256
      ctlDayBlock.BackColor = 8421440
      ctlDayBlock = ""
      ctlDayBlock.Enabled = False
      ctlDayBlock.Tag = ""
        ctlDayBlock.Visible = Not (bytBlankBlocksAfter > 6 And bytBlockCounter > 35)
    Case Else   'blocks that hold days of the month
      bytBlockDayOfMonth = bytBlockCounter - bytBlankBlocksBefore
      ReferenceABlock ctlDayBlock, bytBlockCounter
      lngBlockDate = lngLastOfPreviousMonth + bytBlockDayOfMonth 'block's date
        If bytBlockDayOfMonth < 10 Then
          ctlDayBlock = Space(2) & bytBlockDayOfMonth & _
                        vbNewLine & astrCalendarBlocks(bytBlockCounter)
        Else
          ctlDayBlock = bytBlockDayOfMonth & _
                        vbNewLine & astrCalendarBlocks(bytBlockCounter)
        End If
 
        'If this block is the system date, change its color (CFB 1-25-08)
        If lngBlockDate = lngSystemDate Then
          ctlDayBlock.BackColor = RGB(0, 0, 255)
          ctlDayBlock.ForeColor = QBColor(15)
          Set ctlSystemDateBlock = ctlDayBlock
          blnSystemDateIsShown = True
        Else
          ctlDayBlock.BackColor = QBColor(15)
          ctlDayBlock.ForeColor = 8388608 '====> Added by ADezii on 1/28/2008 (Date
        End If                                  'Text was essentially invisible without it for
          ctlDayBlock.Visible = True            'Block representing current day position)
          ctlDayBlock.Enabled = True
          ctlDayBlock.Tag = lngBlockDate
  End Select
Next
 
'If the system date is in this month, show its events (CFB added 1-25-08)
If blnSystemDateIsShown Then
  PopulateEventsList ctlSystemDateBlock
End If
 
Call PopulateYearListBox    'Added by ADezii on 1/28/2008 - suggested by CFB
 
Exit_PopulateCalendar:
  Exit Sub
Err_PopulateCalendar:
  MsgBox Err.Description, vbExclamation, "Error in PopulateCalendar()"
  Call LogErrors(Err.Number, Err.Description, "frmCalendar", "PopulateCalendar() Sub-Routine", "Called from Multiple Locations")
    Resume Exit_PopulateCalendar
End Sub

This code is used to pouplate the eventlist:

Private Sub PopulateEventsList(ctlDayBlock As Control)
On Error GoTo Err_PopulateEventsList
Dim strSQL2 As String
 
strSQL2 = "SELECT sales1.id, sales1.naam_klant, sales1.woonplaats, sales1.date, sales1.time, " & _
          "tblVisitType.Type, tblVisitType.Code, sales1.adres FROM tblVisitType INNER JOIN sales1 ON " & _
          "tblVisitType.TypeID = sales1.TypeID " & _
          "WHERE sales1.date = " & ctlDayBlock.Tag & _
          " ORDER BY sales1.time, sales1.naam_klant, sales1.woonplaats;"
 
lstEvents.RowSource = strSQL2
 
lblEventsOnDate.Caption = Format(ctlDayBlock.Tag, "m-dd-yyyy")
 
If DCount("*", "sales1", "[date] = #" & CDate(ctlDayBlock.Tag) & "#") > 0 Then
  lstEvents.Visible = True
  lblEventsOnDate.Visible = True
Else
  lstEvents.Visible = True
  lblEventsOnDate.Visible = False
End If
 
Exit_PopulateEventsList:
  Exit Sub
 
Err_PopulateEventsList:
  MsgBox Err.Description, vbExclamation, "Error in PopulateEventsList()"
  Call LogErrors(Err.Number, Err.Description, "frmCalendar", "PopulateEventsList() Sub-Routine", _
                 "Called from PopulateCalendar() and all Text Boxes GotFocus() Events")
    Resume Exit_PopulateEventsList
End Sub

Additional info:

I've used the widescreen version of the calender and haven't made any changes to the script. I've only changed the linked table and fields.

This is the calendar i've used:
(A wide angled version (for wider screen real-estate) has also been posted now at Post #327. The attachment there is Wide Calendar with Switchboard.zip.)

[zmbd]

I've used the widescreen version of the calender and haven't made any changes to the script. I've only changed the linked table and fields.

Can open the linked table directly within the access FE?

If not, then this might explain why when you convert/import the SQLServer to a local table things start to work as expected.

ADezii is really the expert on "The Calendar" project and will have a better insight as to the better solution.

Just in case: How to create a DSN-less connection to SQL Server for linked tables in Access

[dannyflee]

I can open the table in the FE and it works just fine. It's used in querys/forms/etc and i'm having no troubles at all.

Only the dates from the table won't show up in the calender as long as the table is a mysql table. I've also tried to make a query and link the script of the calendar to the query, but i gives the same (no) result.

So my thought was that the script of the calendar doesn't understand the dates provided by the MySQL table.

By the way the link you added shows how to connect to SQL and i'm using MySQL. I always heard those are not the same.

[zmbd]

You're right about the MySQL v SQL Server.
I've been having issues with one of my backends and have that stuck in my head. Stupid updates /.,.\


Go in to the MYSQL and change the date column to "DATETIME"
It seems to me that was an issue with one of my early attempts and the DBA didn't have any issue changing the type cast for the field and I just re-read something about this while attempting to track down your issue... This may also be of some help since you are using the MySql backend:(SQL Data Types for Various DBs)

(among other type cast issues... it isn't pretty)

[dannyflee]

I've tried changing the date column to "DATETIME" and it had no effect. Also set the time-column to "DATETIME" but also without succes.


Also found something about converting the date like:

Format(CDate(Format([sales1.date], "0000-00-00")), "dd-mm-yyyy")

But it gives an error

[ADezii]

Try changing

'************** Code Intentionally Removed **************
strFirstOfMonth = Str(intMonth) & "/1/" & Str(intYear)
 
'COMMENTS intentianally removed
'strFirstOfMonth = "1/" & Str(intMonth) & Str(intYear)
'************** Code Intentionally Removed **************
 

to

'************** Code Intentionally Removed **************
'strFirstOfMonth = Str(intMonth) & "/1/" & Str(intYear)
 
'COMMENTS intentianally removed
strFirstOfMonth = "1/" & Str(intMonth) & Str(intYear)
'************** Code Intentionally Removed **************
 

in the PopulateCalendar() Sub-Routine. If this is not successful, try an explicit Date Conversion (Code Lines 4 & 8)

'************** Code Intentionally Removed **************
Do While Not rstEvents.EOF
  'CFB added 2-18-10
  lngFirstDateInRange = CDate(rstEvents![Date])
    If lngFirstDateInRange < lngFirstOfMonth Then
      lngFirstDateInRange = lngFirstOfMonth
    End If
      lngLastDateInRange = CDate(rstEvents![Date])
    If lngLastDateInRange > lngLastOfMonth Then
      lngLastDateInRange = lngLastOfMonth
    End If
'************** Code Intentionally Removed **************

[dannyflee]

Adezii,

I've made the changes you suggested in the script, but the result is the same.
The calendar is still empty.

[ADezii]

Let's go back to the very basics and start from there.

1. Revert back to the Original Code that you were using.
2. I wrote a small Code Snippet that will:
   1. Test and see if the Recordset (rstEvents) contains any Records for the current Month (December) or is Empty.
   2. If the Recordset does contain records, how many?
   3. Is the [Date] Field in the Recordset recognized as a Valid Date Format by Access?
3. Insert the following Code immediately after the Recordset (rstEvents) is created in the PopulateCalendar() Sub-Routine as indicated below:
   

   '*********************** Code Intentionally Omitted ***********************
   Set rstEvents = db.OpenRecordset(strSQL)    'Added 4/16/2008
    
   With rstEvents
     If .BOF And .EOF Then         'NO Records
       MsgBox "rstEvents contains 0 Records"
     Else
       .MoveLast: .MoveFirst       'for accurate Record Count
         MsgBox "rstEvents consists of " & .RecordCount & " Records"
           MsgBox "[Date] " & IIf(IsDate(![Date]), " IS ", " IS NOT ") & _
                  "recognized by Access as a Valid Date Field"
     End If
       .Close: Set rstEvents = Nothing
   End With
    
   Exit Sub
   '*********************** Code Intentionally Omitted ***********************

4. These questions will be answered, then the Routine will be gracefully exited.
5. We'll take it from here on.

[zmbd]

Are we actually using "Date" as a field name?
It is one of the reserved token words in Access, even when enclosed within square braces "[]" this has caused me much grief. Slightly changing the name in the field has solved many an issue in my inherited DBs even though I did have to change it in tons of queries and forms.

[ADezii]

@zmbd:
I thought of that also, and I always discourage its use, but the Field is always qualified by the Recordset Object, namely:

rstEvents![Date]

In this specific Instance, it should not cause any problem(s). I think that the key point here is that all works well with the converted, local Table.

[dannyflee]

@Adezii

rstEvents contains 0 records.
The current month should contain 3 records

@zmbd
I've used the same fieldname as in the original calender.

[ADezii]

rstEvents contains 0 records

That would explain the blank Calendar.

What does Msgbox display (True/False) after executing the below?

Set rstEvents = db.OpenRecordset(strSQL)
Msgbox IsDate(rstEvents![Date])

[dannyflee]

It displays onwaar.
Is dutch for false.

Is it correct that this means that acces/the script indentifies the date as not a date format?

[ADezii]

Is it correct that this means that acces/the script indentifies the date as not a date format?

It means that Access is saying that the Expression (IsDate(rstEvents![Date])) cannot be converted to a Date, and therein lies your problem. Try using the Date Delimiter (#) in the building of your SQL Statement (strSQL) as in Code Lines 4 and 5.

strsql = "SELECT sales1.naam_klant, sales1.woonplaats, sales1.date, tblVisitType.Type, " & _
         "tblVisitType.Code, sales1.time " & _
         "FROM tblVisitType INNER JOIN sales1 ON tblVisitType.TypeID = sales1.TypeID " & _
         "WHERE sales1.date Between #" & lngFirstOfMonth & "# And #" & lngLastOfMonth & _
         "# ORDER BY sales1.time, sales1.naam_klant, sales1.woonplaats;"

[zmbd]

Which is what I suggested in my first reply:

Make sure that you are using "#" around your dates.

[dannyflee]

I get the following error:

Error in PopulateCalendar()

Syntaxisfout in datum. in query-expressie sales1.date Between #41609# And #41639

In english i think it would say:
Syntax error in date. in query-expression sales1.date Between #41609# And #41639

I think the numbers between "#" represent the lngfirstofmonth and lnglastofmonth. In the error the last one doesn't give the # at the end.

@zmbd
In the third post i asked if you could be more specific, I didn't really understood what you ment:)

[ADezii]

Do you have a closing '#' at the end of lngLastOfMonth with no spaces, as in

"Between #" & lngFirstOfMonth & "# And #" & lngLastOfMonth & "#"

which translates to

BETWEEN #XXXXX# AND #XXXXX#?

Try a 2 Date Converts within the SQL String along with the Delimiters ('#'), as in Code Lines 4 and 5:

strSQL = "SELECT sales1.naam_klant, sales1.woonplaats, sales1.date, tblVisitType.Type, " & _
         "tblVisitType.Code, sales1.time " & _
         "FROM tblVisitType INNER JOIN sales1 ON tblVisitType.TypeID = sales1.TypeID " & _
         "WHERE sales1.date Between #" & CDate(lngFirstOfMonth) & "# And #" & CDate(lngLastOfMonth) & _
         "# ORDER BY sales1.time, sales1.naam_klant, sales1.woonplaats;"

[dannyflee]

I copied the script you posted in post 16. Then the last # was on the next line in front of "ORDER BY"

Now it looks like this:

strSQL = "SELECT sales1.naam_klant, sales1.woonplaats, sales1.date, tblVisitType.Type, " & _
         "tblVisitType.Code, sales1.time " & _
         "FROM tblVisitType INNER JOIN sales1 ON tblVisitType.TypeID = sales1.TypeID " & _
         "WHERE sales1.date Between #" & lngFirstOfMonth & "# And #" & lngLastOfMonth & "#" & _
         "ORDER BY  sales1.time, sales1.naam_klant, sales1.woonplaats;"

And it still gives the same error

[zmbd]

dannyflee
You are absolutly correct, you did ask for clarification; however, after looking thru the code and comparing it to the original from the Calendar project I wanted to make sure that you had a good link and that the data types were correct before altering ADezii's code.

[ADezii]

I made 2 minor corrections. Copy-N-Paste the following assignment to strsql exactly, completely overwriting your Statement.

strsql = "SELECT sales1.naam_klant, sales1.woonplaats, sales1.date, tblVisitType.Type, " & _
         "tblVisitType.Code, sales1.time " & _
         "FROM tblVisitType INNER JOIN sales1 ON tblVisitType.TypeID = sales1.TypeID " & _
         "WHERE sales1.date Between #" & CDate(lngFirstOfMonth) & "# And #" & CDate(lngLastOfMonth) & "# " & _
         "ORDER BY  sales1.time, sales1.naam_klant, sales1.woonplaats;"

[ADezii]

@zmbd:
Tough one, hey zmdb! (LOL).

[dannyflee]

Great it works:)

The Cdate did the trick.
I'm truly amazed. You guys are great!!

Only the eventlist is not functioning correctly.
It works, but it only works for the end of the month. day 1-11 doesn't show up in the event list. Day 12 -31 do show up in the eventlist.
Any Suggestions?


There is one other thing. In the same table i have another date i would like to populate in the calander.
Since the script is way beyond my knowledge of VBA i haven't got any clue how to do this. Or is it best to make a new post on this matter?

[dannyflee]

Adezii,

Came up with any suggestions for my remaining questions?

Could the eventlist problem have anything to do with the date notation in the script.


I've tried changing:

lblEventsOnDate.Caption = Format(ctlDayBlock.Tag, "mm-dd-yyyy")

to:

lblEventsOnDate.Caption = Format(ctlDayBlock.Tag, "dd-mm-yyyy")

But that didn't do the trick.

For the second item,
Should the second date (and it's additional data) be a part of the strsql in populatecalendar, or should it be a new strsql in populatecalendar?

[ADezii]

Try the following SQL in order to populate the Event List Box:

strSQL2 = "SELECT sales1.id, sales1.naam_klant, sales1.woonplaats, sales1.date, sales1.time, " & _
          "tblVisitType.Type, tblVisitType.Code, sales1.adres FROM tblVisitType INNER JOIN sales1 ON " & _
          "tblVisitType.TypeID = sales1.TypeID " & _
          "WHERE sales1.date = #" & CDate(ctlDayBlock.Tag) & _
          "# ORDER BY sales1.time, sales1.naam_klant, sales1.woonplaats;"
 

The Access Calendar was designed to work with either a single Date Field or a Date Range as specified by a Start and End Date. To Plot two, independent Dates would involve creating a Date Argument to the PopulateCalendar() Sub-Routine, making two successive Calls to the Routine, with a different Argument passed for each Call with the subsequent SQL modified. This is how I see it for now, never modified it within this context.

[dannyflee]

Eventlist:

I've sort of made that change myself as you solved how to populate the calender. (i've made a few little changes in order for the eventlistbox).

This is the code:

strSQL2 = "SELECT sales1.id, sales1.naam_klant, sales1.woonplaats, sales1.adres , sales1.date, sales1.time,  " & _
          "tblVisitType.Type, tblVisitType.Code FROM tblVisitType INNER JOIN sales1 ON " & _
          "tblVisitType.TypeID = sales1.TypeID " & _
          "WHERE sales1.date = #" & CDate(ctlDayBlock.Tag) & "#" & _
          " ORDER BY sales1.time, sales1.naam_klant, sales1.woonplaats;"

As you can see i made the change :
#" & CDate(ctlDayBlock.Tag) & "#"

So it should reconize the dates from my "mysql table" and apparently it does, because the eventlist shows data. Except for the first 11 days of each month.

Only i can't think of a reason why:(

Independent dates:
As i read your comment on this matter. This doesn't sound as a thing i'm capable of with my limited knowledge of vba & the calendar code.
Is there a possibility you would want to help me in this matter.

[ADezii]

This doesn't sound as a thing i'm capable of with my limited knowledge of vba & the calendar code.
Is there a possibility you would want to help me in this matter.

Right now I simply do not have the time. When I do, I'll develope a Version of the Access Calendar that utilizes independent Dates. When I do I will send you a Private Message (PM).

[dannyflee]

My appreciation is great and i will be patient. I will wait for your PM.

Got any suggestions on the eventlist problem?

[ADezii]

Got any suggestions on the eventlist problem?

Very, very strange. When the MySQL Data is converted to Local Access Tables, did you have the same problem?

[dannyflee]

Yes,

It gives the same result

[ADezii]

Execute the SQL in a Saved Query and see what happens.

[dannyflee]

nope,

still the same. Very strange right?

[ADezii]

The only thing left at this point would be for you to Send me a Copy of the Converted Database in Access 2007 or lower Version. If this is an option I will send you my E-Mail Address in a Private Message - just let me know one way or the other.

[dannyflee]

@ADezii
Adezii,

I think that is à good option. I'm working in 2010. So i Will save as 2007.
I Will See your mail in à PM

[dannyflee]

Adezii,

You've got mail:)

[ADezii]

Got it and already looked at it. From everything that I can see the Event List is populated exactly as it should be.

[dannyflee]

That's strange:(

In my case al days up to the 11th day are not shown in the eventlist.
So this might be an access 2010 thing?
But when i open the original it works fine in 2010.

Or could it have to do something between date notation europe/USA?

[ADezii]

To be perfectly honest, at this point I have no idea what could possibly be causing all Dates up the 11th not to be shown in the Events List. Try the following: make sure that you have a Record, or Edit one, dated 12/9/2013 (<11th). Modify strSQL2 to include a Literal date of 12/9/2013 as follows. Let's now see if a single, literal Date, will display in the List Box.

strSQL2 = "SELECT sales1.id, sales1.naam_klant, sales1.woonplaats, sales1.date, sales1.time, " & _
          "tblVisitType.Type, tblVisitType.Code, sales1.adres FROM tblVisitType INNER JOIN sales1 ON " & _
           "tblVisitType.TypeID = sales1.TypeID " & _
           "WHERE sales1.date = #12/9/2013# ORDER BY sales1.time, " & _
           "sales1.naam_klant, sales1.woonplaats;"

[dannyflee]

Yes that works perfectly

[ADezii]

A Super Mystery, must have something to do with the CDate(ctlDayBlock.Tag) Construct, but why only for the first 10 days of each Month. Are you sure it occurs for only the first 10 days and not the single digit ones (1 to 9)? Just thinking out load. Back to the Drawing Board! Let me sleep on this for a day or two since it is a very difficult scenario when you cannot reproduce this Anomaly.

P.S. - Are you calling PopulateEventsList() in the traditional fashion, namely in the GotFocus() Event of each Date Text Box? Also, make sure that all 42 Text Boxes make a call to this Sub-Routine in their GotFocus() Events, namely:

Private Sub txtDayBlockXX_GotFocus()
  PopulateEventsList Me.ActiveControl
  If CDate(Screen.ActiveControl.Tag) <> Date Then Me.ActiveControl.BackColor = 16777164
End Sub

[dannyflee]

No, i'm sure. And it's not the first 10, but the first 11 days.
The 12th is the always first date that shows up in the eventlist.

Every Private Sub txtDayBlockXX_GotFocus() has a PopulateEventsList Me.ActiveControl.

Maybe a teamviewer session is helpfull so you can see what happens?

[ADezii]

1. Before I give up for now, place this Debugging Code in the PopulateEventsList() Routine exactly as shown (Code Lines 4, 6, 22, and 26).
   Private Sub PopulateEventsList(ctlDayBlock As Control)
   

   Private Sub PopulateEventsList(ctlDayBlock As Control)
   On Error GoTo Err_PopulateEventsList
   Dim strSQL2 As String
   Static intCtr As Integer
    
   intCtr = intCtr + 1
    
   strSQL2 = "SELECT sales1.id, sales1.naam_klant, sales1.woonplaats, sales1.adres , sales1.date, sales1.time,  " & _
             "tblVisitType.Type, tblVisitType.Code FROM tblVisitType INNER JOIN sales1 ON " & _
             "tblVisitType.TypeID = sales1.TypeID " & _
             "WHERE sales1.date = #" & CDate(ctlDayBlock.Tag) & "#" & _
             " ORDER BY sales1.time, sales1.naam_klant, sales1.woonplaats;"
    
   'WHERE sales1.Date = " & ctlDayBlock.Tag &" _
    
   lstEvents.RowSource = strSQL2
    
    
   lblEventsOnDate.Caption = Format(ctlDayBlock.Tag, "dd-mm-yyyy")
    
   If DCount("*", "sales1", "[date] = #" & CDate(ctlDayBlock.Tag) & "#") > 0 Then
    Debug.Print Format$(intCtr, "00") & "-[DATA]==> " & CDate(ctlDayBlock.Tag)
     lstEvents.Visible = True
     lblEventsOnDate.Visible = True
   Else
    Debug.Print Format$(intCtr, "00") & "-[NO DATA]==> " & CDate(ctlDayBlock.Tag)
     lstEvents.Visible = True
     lblEventsOnDate.Visible = False
   End If
    
   '************************ Code Intentionally Omitted ************************

2. Exit & Save the Database.
3. Open the Database and TAB/Set Focus to every day in the month of December so PopulateEventsList() is executed for each Day.
4. View the Immediate Window and see if the Output matches the actual Data plotted on the Calendar.

[dannyflee]

Adezii,

Still gives the same result:(
I've opened the orginal calendar and have the same problem. I first thougt the problem didn't occur in the original file, but i was wrong. So i think it's a problem that occurs in access 2010.

Correction on the above,

I now have a file that totally works with the local table:

strSQL2 = "SELECT sales1.id, sales1.naam_klant, sales1.woonplaats, sales1.date, sales1.time, " & _
          "tblVisitType.Type, tblVisitType.Code, sales1.adres FROM tblVisitType INNER JOIN sales1 ON " & _
          "tblVisitType.TypeID = sales1.TypeID " & _
          "WHERE sales1.date = " & ctlDayBlock.Tag & _
          " ORDER BY sales1.time, sales1.naam_klant, sales1.woonplaats;"

With the MySQL table the problem occurs on this code.

strSQL2 = "SELECT sales1.id, sales1.naam_klant, sales1.woonplaats, sales1.date, sales1.time, " & _
          "tblVisitType.Type, tblVisitType.Code, sales1.adres FROM tblVisitType INNER JOIN sales1 ON " & _
          "tblVisitType.TypeID = sales1.TypeID " & _
          "WHERE sales1.date = #" & CDate(ctlDayBlock.Tag) & "#" & _
          " ORDER BY sales1.time, sales1.naam_klant, sales1.woonplaats;"

Even with this code when i set convert to a local table the problem stays. So the problem seems to occur when Cdate converts the date.

[PPelle]

In the working strSQL2, replace ctlDayBlock.Tag with one of these, try one at a time:
1. Format(ctlDayBlock.Tag,"YYYY-MM-DD")
2. Format(ctlDayBlock.Tag,"\#YYYY-MM-DD\#")
3. Format(ctlDayBlock.Tag,"\#mm\/dd\/yyyy hh\:nn\:ss\#;;;\N\u\l\l")

If none of them work, try this:
4. Format(CDate(ctlDayBlock.Tag),"YYYY-MM-DD")
5. Format(CDate(ctlDayBlock.Tag),"\#YYYY-MM-DD\#")
6. Format(CDate(ctlDayBlock.Tag),"\#mm\/dd\/yyyy hh\:nn\:ss\#;;;\N\u\l\l")

Also, I would suggest to change this:

strFirstOfMonth = "1/" & Str(intMonth) & "/" & Str(intYear)
'strFirstOfMonth = Str(intMonth) & "/1/" & Str(intYear)
 

To:

strFirstOfMonth = DateSerial(intYear, intMonth, 1)

This, I believe, is more independent of regional settings.

[zmbd]

Please, hummor me,
Go back to the MySQL table.
CHeck the data type cast on the date

[dannyflee]

@PPelle

I will try your suggestions and reply the results


@zmbd

The type is "datetime"

[dannyflee]

@PPelle

The very first suggestion did the trick.

I've changed
CDate(ctlDayBlock.Tag)

to

Format(ctlDayBlock.Tag,"YYYY-MM-DD")

Great:)
Love you guys for the great help:)

That leaves only one open question.
That is how to populate the calendar with 2 seperate dates from one table.

@Adezii, I will open a new post on this matter. Since it's not directly linked to the calendar/mysql problem.

Can i choose 2 best answers? Because there are 2 answers that solved the problem.

[ADezii]

In my opinion, @PPelle deserves the Best Answer since his solution was the one that ultimately solved this annoying problem. I still cannot fathom why Dates after the 11th of each Month would show up but not before, oh well. Dannyflee, glad it all worked out for you. Runner Up should go to zmdb for his typical keen insight and patience.

[zmbd]

I suggest that the following summary of posts that helped to solve be set as best answer:
----------

ADezii Post #22

I made 2 minor corrections. Copy-N-Paste the following assignment to strsql exactly, completely overwriting your Statement.

strsql = "SELECT sales1.naam_klant, sales1.woonplaats, sales1.date, tblVisitType.Type, " & _
         "tblVisitType.Code, sales1.time " & _
         "FROM tblVisitType INNER JOIN sales1 ON tblVisitType.TypeID = sales1.TypeID " & _
         "WHERE sales1.date Between #" & CDate(lngFirstOfMonth) & "# And #" & CDate(lngLastOfMonth) & "# " & _
         "ORDER BY  sales1.time, sales1.naam_klant, sales1.woonplaats;"

----------

----------

PPelle Post #45

(...)
In the working strSQL2, replace ctlDayBlock.Tag with one of these, try one at a time:
1. Format(ctlDayBlock.Tag,"YYYY-MM-DD")
2. Format(ctlDayBlock.Tag,"\#YYYY-MM-DD\#")
3. Format(ctlDayBlock.Tag,"\#mm\/dd\/yyyy hh\:nn\:ss\#;;;\N\u\l\l")
(...)

----------

With OP's kind permision I can reset the "Best Answer" and set this post, or make any suggested modifications.
(Note: I set the order arbitraily by post# (^_^) )
-z