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

Chrome loading cached Javascript despite clearing cache, delete/reinstall, new MAMP host setup, etc

javascript google-chrome caching developer-tools

I have tried numerous items to clear the Javascript cache for a Sencha Touch 2 app I am developing using MAMP on my Mac. I have deleted the Chrome cache, disabled/enabled and toggled 'Disable Cache...

Remove elements from in memory element in js

javascript prototypejs

I have a java script file that is used in several places. It has this code:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.