Compare date range with dates in table VBA
I am using MS Access as front-end for a MySQL server database. In database i have a table called PubHol (meaning Public Holidays) listing the dates dd/mm/yyyy of public holidays. I have a form (linked to a separate table) where i enter course_start date and course_end date. I would like to create a function which triggers itself on lostfocus of a field where the function checks whether the date range entered course_start and course_end coincides with a day listed in PubHol. For example if a client has course start 01/01/2012 and course end 15/01/2012 and there is a date in PubHol falling between for ex 10/01/2012 I want to receive a MsgBox saying something. Thank you
It sounds like you just want to issue a BETWEEN query to MySQL:
SELECT HolidayDate FROM PubHol WHERE HolidayDate BETWEEN ? AND ?
... and fill in the parameters for the query with the Course_Start and Course_End values you're interested in. (I'm assuming VBA can handle parameterized queries.)
Assuming that you used a date data type and not a string data type in your db, testing holidays which fall into a date range would look like this in VBA
Dim condition As String Dim course_start As Date, course_end As Date course_start = ... course_end = ... condition = "[Holiday] Between #" & Format$(course_start,"yyyy\/mm\/dd") & _ "# AND #" & Format$(course_end,"yyyy\/mm\/dd") & "#" If DCount("*", "PubHol", condition) > 0 Then MsgBox ... End If
I found this function in my Access libraries
Public Function JetSqlDate(ByVal d As Variant) As String If IsNull(d) Then JetSqlDate = "NULL" Else JetSqlDate = Format$(d, "\#mm\/dd\/yyyy hh\:nn\:ss\#") End If End Function
Using it simplifies making the condition
condition = "[Holiday] Between " & JetSqlDate(course_start) & _ " AND " & JetSqlDate(course_end)
Note: If the MySQL tables are linked into the Access DB with ODBC you can just use the Access (Jet) SQL syntax. Access (or the ODBC-Driver to be precise) will translate it to MySQL syntax automatically.