By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,040 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,040 IT Pros & Developers. It's quick & easy.

Acces calander with mysql

P: 27
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
Dec 2 '13 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. strsql = "SELECT sales1.naam_klant, sales1.woonplaats, sales1.date, tblVisitType.Type, " & _
  2.          "tblVisitType.Code, sales1.time " & _
  3.          "FROM tblVisitType INNER JOIN sales1 ON tblVisitType.TypeID = sales1.TypeID " & _
  4.          "WHERE sales1.date Between #" & CDate(lngFirstOfMonth) & "# And #" & CDate(lngLastOfMonth) & "# " & _
  5.          "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

Share this Question
Share on Google+
49 Replies


zmbd
Expert Mod 5K+
P: 5,397
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.
Dec 2 '13 #2

P: 27
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub PopulateCalendar()
  2. On Error GoTo Err_PopulateCalendar
  3. Dim strFirstOfMonth As String, bytFirstWeekdayOfMonth As Byte, bytBlockCounter As Byte
  4. Dim bytBlockDayOfMonth As Byte, lngBlockDate As Long, ctlDayBlock As TextBox
  5. Dim bytDaysInMonth As Byte, bytEventDayOfMonth As Byte, lngFirstOfMonth As Long
  6. Dim lngLastOfMonth As Long, lngFirstOfNextMonth As Long, lngLastOfPreviousMonth As Long
  7. Dim lngEventDate As Long, bytBlankBlocksBefore As Byte, bytBlankBlocksAfter As Byte
  8. Dim astrCalendarBlocks(1 To 42) As String, db As DAO.Database, rstEvents As DAO.Recordset
  9. Dim strEvent As String
  10. Dim lngSystemDate As Long   'CFB added 1-25-08
  11. Dim ctlSystemDateBlock As TextBox, blnSystemDateIsShown As Boolean  'CFB added 1-25-08
  12. Dim strSQL As String        'Added 4/16/2008
  13. Dim lngFirstDateInRange As Long     'CFB added 2-18-10
  14. Dim lngLastDateInRange As Long      '
  15. Dim lngEachDateInRange As Long      '
  16. Dim strStartTime As String          '
  17.  
  18. lngSystemDate = Date        'CFB added 1-25-08
  19. intMonth = objCurrentDate.Month
  20. intYear = objCurrentDate.Year
  21. lstEvents.Visible = False
  22. lblEventsOnDate.Visible = False
  23. lblMonth.Caption = MonthAndYear(intMonth, intYear)
  24. strFirstOfMonth = Str(intMonth) & "/1/" & Str(intYear)
  25.  
  26. '*************************************************************************
  27.   'ADezii
  28.   'NOTE: Will work in the UK (United Kingdom) and other European Nations
  29.   'strFirstOfMonth = "1/" & Str(intMonth) & Str(intYear)
  30.  
  31.  
  32. bytFirstWeekdayOfMonth = WeekDay(strFirstOfMonth)
  33. lngFirstOfMonth = DateSerial(intYear, intMonth, 1)
  34. lngFirstOfNextMonth = DateSerial(intYear, intMonth + 1, 1)
  35. lngLastOfMonth = lngFirstOfNextMonth - 1
  36. lngLastOfPreviousMonth = lngFirstOfMonth - 1
  37. bytDaysInMonth = lngFirstOfNextMonth - lngFirstOfMonth
  38. bytBlankBlocksBefore = bytFirstWeekdayOfMonth - 1
  39. bytBlankBlocksAfter = 42 - (bytBlankBlocksBefore + bytDaysInMonth)
  40.  
  41. Set db = CurrentDb
  42.  
  43. strSQL = "SELECT sales1.naam_klant, sales1.woonplaats, sales1.date, tblVisitType.Type, tblVisitType.Code, sales1.time " & _
  44.          "FROM tblVisitType INNER JOIN sales1 ON tblVisitType.TypeID = sales1.TypeID " & _
  45.          "WHERE sales1.date Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
  46.          " ORDER BY sales1.time, sales1.naam_klant, sales1.woonplaats;"
  47.  
  48. Set rstEvents = db.OpenRecordset(strSQL)    'Added 4/16/2008
  49.  
  50. Do While Not rstEvents.EOF
  51.   'CFB added 2-18-10
  52.   lngFirstDateInRange = rstEvents![Date]      '<Substitute for [Start Date]>
  53.   If lngFirstDateInRange < lngFirstOfMonth Then
  54.     lngFirstDateInRange = lngFirstOfMonth
  55.   End If
  56.   lngLastDateInRange = rstEvents![Date]         '<Substitute for [End Date]>
  57.   If lngLastDateInRange > lngLastOfMonth Then
  58.     lngLastDateInRange = lngLastOfMonth
  59.   End If
  60.  
  61.   For lngEachDateInRange = lngFirstDateInRange To lngLastDateInRange
  62.     bytEventDayOfMonth = (lngEachDateInRange - lngLastOfPreviousMonth)
  63.     bytBlockCounter = bytEventDayOfMonth + bytBlankBlocksBefore
  64.                                               '<Substitute for [Title]>
  65.       If astrCalendarBlocks(bytBlockCounter) = "" Then
  66.         astrCalendarBlocks(bytBlockCounter) = Format$(rstEvents![Time], "hh:nn AM/PM") & vbCrLf & rstEvents![naam_klant] & ", " & _
  67.                                               Left$(rstEvents![Woonplaats], 1) & "." & " [" & rstEvents! & "]"
  68.       Else                                    '<Substitute for [Title]>
  69.         astrCalendarBlocks(bytBlockCounter) = astrCalendarBlocks(bytBlockCounter) & vbNewLine & _
  70.                                               Format$(rstEvents![Time], "hh:nn AM/PM") & vbCrLf & rstEvents![naam_klant] & ", " & _
  71.                                               Left$(rstEvents![Woonplaats], 1) & "." & " [" & rstEvents! & "]"
  72.  
  73.       End If
  74.   Next lngEachDateInRange
  75.   'End of CFB added 2-18-10
  76.  
  77.     rstEvents.MoveNext
  78. Loop
  79.  
  80. For bytBlockCounter = 1 To 42                           'blank blocks at start of month
  81.   Select Case bytBlockCounter
  82.     Case Is < bytFirstWeekdayOfMonth
  83.       astrCalendarBlocks(bytBlockCounter) = ""
  84.       ReferenceABlock ctlDayBlock, bytBlockCounter
  85.       'ctlDayBlock.BackColor = 12632256
  86.       ctlDayBlock.BackColor = 8421440
  87.       ctlDayBlock = ""
  88.       ctlDayBlock.Enabled = False
  89.       ctlDayBlock.Tag = ""
  90.     Case Is > bytBlankBlocksBefore + bytDaysInMonth     'blank blocks at end of month
  91.       astrCalendarBlocks(bytBlockCounter) = ""
  92.       ReferenceABlock ctlDayBlock, bytBlockCounter
  93.       'ctlDayBlock.BackColor = 12632256
  94.       ctlDayBlock.BackColor = 8421440
  95.       ctlDayBlock = ""
  96.       ctlDayBlock.Enabled = False
  97.       ctlDayBlock.Tag = ""
  98.         ctlDayBlock.Visible = Not (bytBlankBlocksAfter > 6 And bytBlockCounter > 35)
  99.     Case Else   'blocks that hold days of the month
  100.       bytBlockDayOfMonth = bytBlockCounter - bytBlankBlocksBefore
  101.       ReferenceABlock ctlDayBlock, bytBlockCounter
  102.       lngBlockDate = lngLastOfPreviousMonth + bytBlockDayOfMonth 'block's date
  103.         If bytBlockDayOfMonth < 10 Then
  104.           ctlDayBlock = Space(2) & bytBlockDayOfMonth & _
  105.                         vbNewLine & astrCalendarBlocks(bytBlockCounter)
  106.         Else
  107.           ctlDayBlock = bytBlockDayOfMonth & _
  108.                         vbNewLine & astrCalendarBlocks(bytBlockCounter)
  109.         End If
  110.  
  111.         'If this block is the system date, change its color (CFB 1-25-08)
  112.         If lngBlockDate = lngSystemDate Then
  113.           ctlDayBlock.BackColor = RGB(0, 0, 255)
  114.           ctlDayBlock.ForeColor = QBColor(15)
  115.           Set ctlSystemDateBlock = ctlDayBlock
  116.           blnSystemDateIsShown = True
  117.         Else
  118.           ctlDayBlock.BackColor = QBColor(15)
  119.           ctlDayBlock.ForeColor = 8388608 '====> Added by ADezii on 1/28/2008 (Date
  120.         End If                                  'Text was essentially invisible without it for
  121.           ctlDayBlock.Visible = True            'Block representing current day position)
  122.           ctlDayBlock.Enabled = True
  123.           ctlDayBlock.Tag = lngBlockDate
  124.   End Select
  125. Next
  126.  
  127. 'If the system date is in this month, show its events (CFB added 1-25-08)
  128. If blnSystemDateIsShown Then
  129.   PopulateEventsList ctlSystemDateBlock
  130. End If
  131.  
  132. Call PopulateYearListBox    'Added by ADezii on 1/28/2008 - suggested by CFB
  133.  
  134. Exit_PopulateCalendar:
  135.   Exit Sub
  136. Err_PopulateCalendar:
  137.   MsgBox Err.Description, vbExclamation, "Error in PopulateCalendar()"
  138.   Call LogErrors(Err.Number, Err.Description, "frmCalendar", "PopulateCalendar() Sub-Routine", "Called from Multiple Locations")
  139.     Resume Exit_PopulateCalendar
  140. End Sub
This code is used to pouplate the eventlist:
Expand|Select|Wrap|Line Numbers
  1. Private Sub PopulateEventsList(ctlDayBlock As Control)
  2. On Error GoTo Err_PopulateEventsList
  3. Dim strSQL2 As String
  4.  
  5. strSQL2 = "SELECT sales1.id, sales1.naam_klant, sales1.woonplaats, sales1.date, sales1.time, " & _
  6.           "tblVisitType.Type, tblVisitType.Code, sales1.adres FROM tblVisitType INNER JOIN sales1 ON " & _
  7.           "tblVisitType.TypeID = sales1.TypeID " & _
  8.           "WHERE sales1.date = " & ctlDayBlock.Tag & _
  9.           " ORDER BY sales1.time, sales1.naam_klant, sales1.woonplaats;"
  10.  
  11. lstEvents.RowSource = strSQL2
  12.  
  13. lblEventsOnDate.Caption = Format(ctlDayBlock.Tag, "m-dd-yyyy")
  14.  
  15. If DCount("*", "sales1", "[date] = #" & CDate(ctlDayBlock.Tag) & "#") > 0 Then
  16.   lstEvents.Visible = True
  17.   lblEventsOnDate.Visible = True
  18. Else
  19.   lstEvents.Visible = True
  20.   lblEventsOnDate.Visible = False
  21. End If
  22.  
  23. Exit_PopulateEventsList:
  24.   Exit Sub
  25.  
  26. Err_PopulateEventsList:
  27.   MsgBox Err.Description, vbExclamation, "Error in PopulateEventsList()"
  28.   Call LogErrors(Err.Number, Err.Description, "frmCalendar", "PopulateEventsList() Sub-Routine", _
  29.                  "Called from PopulateCalendar() and all Text Boxes GotFocus() Events")
  30.     Resume Exit_PopulateEventsList
  31. 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.)
Dec 2 '13 #3

zmbd
Expert Mod 5K+
P: 5,397
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
Dec 2 '13 #4

P: 27
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.
Dec 2 '13 #5

zmbd
Expert Mod 5K+
P: 5,397
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)
Dec 2 '13 #6

P: 27
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
Dec 2 '13 #7

ADezii
Expert 5K+
P: 8,638
Try changing
Expand|Select|Wrap|Line Numbers
  1. '************** Code Intentionally Removed **************
  2. strFirstOfMonth = Str(intMonth) & "/1/" & Str(intYear)
  3.  
  4. 'COMMENTS intentianally removed
  5. 'strFirstOfMonth = "1/" & Str(intMonth) & Str(intYear)
  6. '************** Code Intentionally Removed **************
  7.  
to
Expand|Select|Wrap|Line Numbers
  1. '************** Code Intentionally Removed **************
  2. 'strFirstOfMonth = Str(intMonth) & "/1/" & Str(intYear)
  3.  
  4. 'COMMENTS intentianally removed
  5. strFirstOfMonth = "1/" & Str(intMonth) & Str(intYear)
  6. '************** Code Intentionally Removed **************
  7.  
in the PopulateCalendar() Sub-Routine. If this is not successful, try an explicit Date Conversion (Code Lines 4 & 8)
Expand|Select|Wrap|Line Numbers
  1. '************** Code Intentionally Removed **************
  2. Do While Not rstEvents.EOF
  3.   'CFB added 2-18-10
  4.   lngFirstDateInRange = CDate(rstEvents![Date])
  5.     If lngFirstDateInRange < lngFirstOfMonth Then
  6.       lngFirstDateInRange = lngFirstOfMonth
  7.     End If
  8.       lngLastDateInRange = CDate(rstEvents![Date])
  9.     If lngLastDateInRange > lngLastOfMonth Then
  10.       lngLastDateInRange = lngLastOfMonth
  11.     End If
  12. '************** Code Intentionally Removed **************
Dec 3 '13 #8

P: 27
Adezii,

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

ADezii
Expert 5K+
P: 8,638
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:
    Expand|Select|Wrap|Line Numbers
    1. '*********************** Code Intentionally Omitted ***********************
    2. Set rstEvents = db.OpenRecordset(strSQL)    'Added 4/16/2008
    3.  
    4. With rstEvents
    5.   If .BOF And .EOF Then         'NO Records
    6.     MsgBox "rstEvents contains 0 Records"
    7.   Else
    8.     .MoveLast: .MoveFirst       'for accurate Record Count
    9.       MsgBox "rstEvents consists of " & .RecordCount & " Records"
    10.         MsgBox "[Date] " & IIf(IsDate(![Date]), " IS ", " IS NOT ") & _
    11.                "recognized by Access as a Valid Date Field"
    12.   End If
    13.     .Close: Set rstEvents = Nothing
    14. End With
    15.  
    16. Exit Sub
    17. '*********************** Code Intentionally Omitted ***********************
  4. These questions will be answered, then the Routine will be gracefully exited.
  5. We'll take it from here on.
Dec 3 '13 #10

zmbd
Expert Mod 5K+
P: 5,397
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.
Dec 3 '13 #11

ADezii
Expert 5K+
P: 8,638
@zmbd:
I thought of that also, and I always discourage its use, but the Field is always qualified by the Recordset Object, namely:
Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 3 '13 #12

P: 27
@Adezii

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

@zmbd
I've used the same fieldname as in the original calender.
Dec 3 '13 #13

ADezii
Expert 5K+
P: 8,638
rstEvents contains 0 records
That would explain the blank Calendar.

What does Msgbox display (True/False) after executing the below?
Expand|Select|Wrap|Line Numbers
  1. Set rstEvents = db.OpenRecordset(strSQL)
  2. Msgbox IsDate(rstEvents![Date])
Dec 3 '13 #14

P: 27
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?
Dec 3 '13 #15

ADezii
Expert 5K+
P: 8,638
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.
Expand|Select|Wrap|Line Numbers
  1. strsql = "SELECT sales1.naam_klant, sales1.woonplaats, sales1.date, tblVisitType.Type, " & _
  2.          "tblVisitType.Code, sales1.time " & _
  3.          "FROM tblVisitType INNER JOIN sales1 ON tblVisitType.TypeID = sales1.TypeID " & _
  4.          "WHERE sales1.date Between #" & lngFirstOfMonth & "# And #" & lngLastOfMonth & _
  5.          "# ORDER BY sales1.time, sales1.naam_klant, sales1.woonplaats;"
Dec 3 '13 #16

zmbd
Expert Mod 5K+
P: 5,397
Which is what I suggested in my first reply:
Make sure that you are using "#" around your dates.
Dec 3 '13 #17

P: 27
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:)
Dec 3 '13 #18

ADezii
Expert 5K+
P: 8,638
Do you have a closing '#' at the end of lngLastOfMonth with no spaces, as in
Expand|Select|Wrap|Line Numbers
  1. "Between #" & lngFirstOfMonth & "# And #" & lngLastOfMonth & "#"
which translates to
Expand|Select|Wrap|Line Numbers
  1. BETWEEN #XXXXX# AND #XXXXX#?
Try a 2 Date Converts within the SQL String along with the Delimiters ('#'), as in Code Lines 4 and 5:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT sales1.naam_klant, sales1.woonplaats, sales1.date, tblVisitType.Type, " & _
  2.          "tblVisitType.Code, sales1.time " & _
  3.          "FROM tblVisitType INNER JOIN sales1 ON tblVisitType.TypeID = sales1.TypeID " & _
  4.          "WHERE sales1.date Between #" & CDate(lngFirstOfMonth) & "# And #" & CDate(lngLastOfMonth) & _
  5.          "# ORDER BY sales1.time, sales1.naam_klant, sales1.woonplaats;"
Dec 4 '13 #19

P: 27
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:

Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT sales1.naam_klant, sales1.woonplaats, sales1.date, tblVisitType.Type, " & _
  2.          "tblVisitType.Code, sales1.time " & _
  3.          "FROM tblVisitType INNER JOIN sales1 ON tblVisitType.TypeID = sales1.TypeID " & _
  4.          "WHERE sales1.date Between #" & lngFirstOfMonth & "# And #" & lngLastOfMonth & "#" & _
  5.          "ORDER BY  sales1.time, sales1.naam_klant, sales1.woonplaats;"
And it still gives the same error
Dec 4 '13 #20

zmbd
Expert Mod 5K+
P: 5,397
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.
Dec 4 '13 #21

ADezii
Expert 5K+
P: 8,638
I made 2 minor corrections. Copy-N-Paste the following assignment to strsql exactly, completely overwriting your Statement.
Expand|Select|Wrap|Line Numbers
  1. strsql = "SELECT sales1.naam_klant, sales1.woonplaats, sales1.date, tblVisitType.Type, " & _
  2.          "tblVisitType.Code, sales1.time " & _
  3.          "FROM tblVisitType INNER JOIN sales1 ON tblVisitType.TypeID = sales1.TypeID " & _
  4.          "WHERE sales1.date Between #" & CDate(lngFirstOfMonth) & "# And #" & CDate(lngLastOfMonth) & "# " & _
  5.          "ORDER BY  sales1.time, sales1.naam_klant, sales1.woonplaats;"
Dec 4 '13 #22

ADezii
Expert 5K+
P: 8,638
@zmbd:
Tough one, hey zmdb! (LOL).
Dec 4 '13 #23

P: 27
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?
Dec 4 '13 #24

P: 27
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:
Expand|Select|Wrap|Line Numbers
  1. lblEventsOnDate.Caption = Format(ctlDayBlock.Tag, "mm-dd-yyyy")
to:
Expand|Select|Wrap|Line Numbers
  1. 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?
Dec 4 '13 #25

ADezii
Expert 5K+
P: 8,638
Try the following SQL in order to populate the Event List Box:
Expand|Select|Wrap|Line Numbers
  1. strSQL2 = "SELECT sales1.id, sales1.naam_klant, sales1.woonplaats, sales1.date, sales1.time, " & _
  2.           "tblVisitType.Type, tblVisitType.Code, sales1.adres FROM tblVisitType INNER JOIN sales1 ON " & _
  3.           "tblVisitType.TypeID = sales1.TypeID " & _
  4.           "WHERE sales1.date = #" & CDate(ctlDayBlock.Tag) & _
  5.           "# ORDER BY sales1.time, sales1.naam_klant, sales1.woonplaats;"
  6.  
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.
Dec 4 '13 #26

P: 27
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:

Expand|Select|Wrap|Line Numbers
  1. strSQL2 = "SELECT sales1.id, sales1.naam_klant, sales1.woonplaats, sales1.adres , sales1.date, sales1.time,  " & _
  2.           "tblVisitType.Type, tblVisitType.Code FROM tblVisitType INNER JOIN sales1 ON " & _
  3.           "tblVisitType.TypeID = sales1.TypeID " & _
  4.           "WHERE sales1.date = #" & CDate(ctlDayBlock.Tag) & "#" & _
  5.           " 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.
Dec 4 '13 #27

ADezii
Expert 5K+
P: 8,638
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).
Dec 4 '13 #28

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

Got any suggestions on the eventlist problem?
Dec 5 '13 #29

ADezii
Expert 5K+
P: 8,638
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?
Dec 5 '13 #30

P: 27
Yes,

It gives the same result
Dec 5 '13 #31

ADezii
Expert 5K+
P: 8,638
Execute the SQL in a Saved Query and see what happens.
Dec 6 '13 #32

P: 27
nope,

still the same. Very strange right?
Dec 6 '13 #33

ADezii
Expert 5K+
P: 8,638
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.
Dec 6 '13 #34

P: 27
@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
Dec 6 '13 #35

P: 27
Adezii,

You've got mail:)
Dec 7 '13 #36

ADezii
Expert 5K+
P: 8,638
Got it and already looked at it. From everything that I can see the Event List is populated exactly as it should be.
Dec 8 '13 #37

P: 27
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?
Dec 8 '13 #38

ADezii
Expert 5K+
P: 8,638
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.
Expand|Select|Wrap|Line Numbers
  1. strSQL2 = "SELECT sales1.id, sales1.naam_klant, sales1.woonplaats, sales1.date, sales1.time, " & _
  2.           "tblVisitType.Type, tblVisitType.Code, sales1.adres FROM tblVisitType INNER JOIN sales1 ON " & _
  3.            "tblVisitType.TypeID = sales1.TypeID " & _
  4.            "WHERE sales1.date = #12/9/2013# ORDER BY sales1.time, " & _
  5.            "sales1.naam_klant, sales1.woonplaats;"
Dec 8 '13 #39

P: 27
Yes that works perfectly
Dec 9 '13 #40

ADezii
Expert 5K+
P: 8,638
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtDayBlockXX_GotFocus()
  2.   PopulateEventsList Me.ActiveControl
  3.   If CDate(Screen.ActiveControl.Tag) <> Date Then Me.ActiveControl.BackColor = 16777164
  4. End Sub
Dec 9 '13 #41

P: 27
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?
Dec 9 '13 #42

ADezii
Expert 5K+
P: 8,638
  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)
    Expand|Select|Wrap|Line Numbers
    1. Private Sub PopulateEventsList(ctlDayBlock As Control)
    2. On Error GoTo Err_PopulateEventsList
    3. Dim strSQL2 As String
    4. Static intCtr As Integer
    5.  
    6. intCtr = intCtr + 1
    7.  
    8. strSQL2 = "SELECT sales1.id, sales1.naam_klant, sales1.woonplaats, sales1.adres , sales1.date, sales1.time,  " & _
    9.           "tblVisitType.Type, tblVisitType.Code FROM tblVisitType INNER JOIN sales1 ON " & _
    10.           "tblVisitType.TypeID = sales1.TypeID " & _
    11.           "WHERE sales1.date = #" & CDate(ctlDayBlock.Tag) & "#" & _
    12.           " ORDER BY sales1.time, sales1.naam_klant, sales1.woonplaats;"
    13.  
    14. 'WHERE sales1.Date = " & ctlDayBlock.Tag &" _
    15.  
    16. lstEvents.RowSource = strSQL2
    17.  
    18.  
    19. lblEventsOnDate.Caption = Format(ctlDayBlock.Tag, "dd-mm-yyyy")
    20.  
    21. If DCount("*", "sales1", "[date] = #" & CDate(ctlDayBlock.Tag) & "#") > 0 Then
    22.  Debug.Print Format$(intCtr, "00") & "-[DATA]==> " & CDate(ctlDayBlock.Tag)
    23.   lstEvents.Visible = True
    24.   lblEventsOnDate.Visible = True
    25. Else
    26.  Debug.Print Format$(intCtr, "00") & "-[NO DATA]==> " & CDate(ctlDayBlock.Tag)
    27.   lstEvents.Visible = True
    28.   lblEventsOnDate.Visible = False
    29. End If
    30.  
    31. '************************ 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.
Dec 9 '13 #43

P: 27
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:

Expand|Select|Wrap|Line Numbers
  1. strSQL2 = "SELECT sales1.id, sales1.naam_klant, sales1.woonplaats, sales1.date, sales1.time, " & _
  2.           "tblVisitType.Type, tblVisitType.Code, sales1.adres FROM tblVisitType INNER JOIN sales1 ON " & _
  3.           "tblVisitType.TypeID = sales1.TypeID " & _
  4.           "WHERE sales1.date = " & ctlDayBlock.Tag & _
  5.           " ORDER BY sales1.time, sales1.naam_klant, sales1.woonplaats;"
With the MySQL table the problem occurs on this code.

Expand|Select|Wrap|Line Numbers
  1. strSQL2 = "SELECT sales1.id, sales1.naam_klant, sales1.woonplaats, sales1.date, sales1.time, " & _
  2.           "tblVisitType.Type, tblVisitType.Code, sales1.adres FROM tblVisitType INNER JOIN sales1 ON " & _
  3.           "tblVisitType.TypeID = sales1.TypeID " & _
  4.           "WHERE sales1.date = #" & CDate(ctlDayBlock.Tag) & "#" & _
  5.           " 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.
Dec 9 '13 #44

P: 17
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:
Expand|Select|Wrap|Line Numbers
  1. strFirstOfMonth = "1/" & Str(intMonth) & "/" & Str(intYear)
  2. 'strFirstOfMonth = Str(intMonth) & "/1/" & Str(intYear)
  3.  

To:
Expand|Select|Wrap|Line Numbers
  1. strFirstOfMonth = DateSerial(intYear, intMonth, 1)

This, I believe, is more independent of regional settings.
Dec 9 '13 #45

zmbd
Expert Mod 5K+
P: 5,397
Please, hummor me,
Go back to the MySQL table.
CHeck the data type cast on the date
Dec 10 '13 #46

P: 27
@PPelle

I will try your suggestions and reply the results


@zmbd

The type is "datetime"
Dec 10 '13 #47

P: 27
@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.
Dec 10 '13 #48

ADezii
Expert 5K+
P: 8,638
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.
Dec 10 '13 #49

zmbd
Expert Mod 5K+
P: 5,397
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.
Expand|Select|Wrap|Line Numbers
  1. strsql = "SELECT sales1.naam_klant, sales1.woonplaats, sales1.date, tblVisitType.Type, " & _
  2.          "tblVisitType.Code, sales1.time " & _
  3.          "FROM tblVisitType INNER JOIN sales1 ON tblVisitType.TypeID = sales1.TypeID " & _
  4.          "WHERE sales1.date Between #" & CDate(lngFirstOfMonth) & "# And #" & CDate(lngLastOfMonth) & "# " & _
  5.          "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
Dec 10 '13 #50

Post your reply

Sign in to post your reply or Sign up for a free account.