''' <summary>
''' Check a Date Range exist in a Table's 2 date columns
''' </summary>
''' <param name="ToTestFromDate">From Date value from User Form</param>
''' <param name="ToTestToDate">To Date value from User Form</param>
''' <param name="FromDateColumnName">From Date Column Name in Table</param>
''' <param name="ToDateColumnName">To Date Column Name in Table</param>
''' <param name="TableName">Table Name of where From,To Date Column Exists</param>
''' <param name="DateParamBraces">"#" for Access, "'" for SQL</param>
''' <param name="Condition">Give your Where condition here. "" for no condition. ELSE - " and FieldName = Value"</param>
''' <returns>Boolean</returns>
''' <remarks></remarks>
Public Function DateRangeFallsBetween2FldsOfaTable(ByVal ToTestFromDate As DateTime, ByVal ToTestToDate As DateTime, _
ByVal FromDateColumnName As String, ByVal ToDateColumnName As String, ByVal TableName As String, _
ByVal DateParamBraces As String, Optional ByVal Condition As String = "") As Boolean
If Not Condition = String.Empty Then
Condition = " and " & Condition
End If
Dim Query As String
Dim dtTemp As New DataTable
Query = "SELECT " & FromDateColumnName & ", " & ToDateColumnName & ", count(*) as TotalRecords FROM " & TableName & " " & _
" where (((" & FromDateColumnName & " between " & DateParamBraces & ToTestFromDate & DateParamBraces & _
" and " & DateParamBraces & ToTestToDate & DateParamBraces & ") or (" & ToDateColumnName & " between " & DateParamBraces & ToTestFromDate & DateParamBraces & _
" and " & DateParamBraces & ToTestToDate & DateParamBraces & ")) or ((" & DateParamBraces & ToTestFromDate.ToString("dd/MMM/yyyy") & DateParamBraces & " between " & FromDateColumnName & " " & _
" and " & ToDateColumnName & " " & ") or (" & DateParamBraces & ToTestToDate.ToString("dd/MMM/yyyy") & DateParamBraces & " between " & FromDateColumnName & " " & _
" and " & ToDateColumnName & " " & ")))" & _
Condition & _
" Group by " & FromDateColumnName & ", " & ToDateColumnName & " " & _
" order by " & FromDateColumnName & ""
dtTemp = CType(FetchData(Query, 0, "tblStaffMaster"), DataSet).Tables(0).Copy
DateRangeFallsBetween2FldsOfaTable = If(dtTemp.Rows.Count > 0, True, False)
If DateRangeFallsBetween2FldsOfaTable Then
MsgBox("Date Range Already Exist FOR " & Condition)
End If
End Function