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

Answers


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.


Need Your Help

ios alarm functionality limited?

android ios notifications uilocalnotification alarm

I have an app which is on Android and iOS. I have added a local notification to fire every 24 hours at a time specified by the user of the app. In Android, the local notification functionality is

How to create a generator/iterator with the Python C API?

python c iterator generator python-c-api

How do I replicate the following Python code with the Python C API?