Check date range against multiple ranges in table 1

[nq]

I have a subform with multiple bound fields, 3 of these bound fields are:

Code StartDate EndDate

I also have a table with a similar structure. This "check" table contains a code and the subsequent start and end dates for that code. There will be several instances of that code with different start and end dates throughout the year.

eg.

Code StartDate EndDate
ED1001 01/01/2006 10/01/2006
ED1001 05/03/2006 15/03/2006
ED2047 15/01/2006 19/01/2006
ED2047 11/08/2006 21/08/2006

etc.

A user enters a code, a start and an end date into the subform. At this point (afterupdate on EndDate) I need to check that the entered dates are within the dates for that code in the "check" table.

This requires reading the "check" table for all instances of the code and comparing the start and end date against those entered on the subform. If either or both of the subform dates lie outside of the "check" dates, then an error is flagged.

Several situations could occur.

1. The subform dates fall outside of all bracketed dates - an error

2. One of the dates will fall inside a bracket and one will be outside - an error

3. Both dates fall within (or equal to) a bracket of dates - no error

The mental logic here is simple. I am not an expert in VBA but I can visualise a cumbersome and complex solution, although I am not sure how to code at this point.

I would appreciate anyone providing me with a relatively simple solution or a direction to investigate.

Nigel.

 

[MajP]

Public Function checkDate(theStartDate As Date, theEndDate As Date, theCode As String) As Boolean
  Dim strWhere As String
  Dim intID As Integer
  strWhere = "dtmStartDate <= " & SQLDate(theStartDate)
  strWhere = strWhere & " AND dtmEndDate >= " & SQLDate(theEndDate)
  strWhere = strWhere & " AND strCode = '" & theCode & "'"
  intID = Nz(DLookup("autoCheckID", "tblCheckDates", strWhere))
  If Not intID = 0 Then
    checkDate = True
  End If
End Function

Function SQLDate(varDate As Variant) As String
   If IsDate(varDate) Then
      SQLDate = "#" & Format$(varDate, "mm\/dd\/yyyy") & "#"
   End If
 End Function


Public Sub testdate()
  MsgBox checkDate(#1/1/2006#, #1/10/2006#, "ED1001")
End Sub

 

[TheAceMan1]

How are ya nq . . .

Here's a sample SQL you could use (just paste into SQL view of a new query and enter the proper [blue]FormName[/blue]).

[blue]SELECT tblCheck.CheckID, tblCheck.Code, tblCheck.StartDate, tblCheck.EndDate
FROM tblCheck
WHERE (([Code]=[Forms]![FormName]![Code] And ([StartDate]>=[Forms]![FormName]![StartDate] Or [Enddate]<=[Forms]![FormName]![EndDate])));[/blue]

If any records are returned then StartDate,EndDate,or both are outside the check . . .

Your thoughts . . .

See Ya! . . . . . .

 

[nq]

Thanks for the ideas guys.

I tied Aceman's code first and had a minor problem. So, before I go too much further, a question about DATE formats. The US uses mm/dd/yyyy, a lot of the world uses dd/mm/yyyy

I remember having problems with DLookup regarding this.
I live in Australia and the regional settings set accordingly. Before using DLookup, I had to convert my date format to US format. It seems that Access ignores the regional settings. Sample code below.

Will either of your solutions work correctly with AU or UK date formats?

Sample code:

tmpDate = Me!Date
USDate = (Mid([tmpDate], 4, 2) & "/" & Left([tmpDate], 2) & "/" & Right([tmpDate], 4))
tmpProvince = Me!Province

If Not IsNull(DLookup("[ID]", "SeriousCrime", "Date = #" & USDate & "# AND [Province] = '" & Me!tmpProvince & "'")) Then

 

[nq]

I have tried MajP's code and found a solution.
It does indeed take care of the regional settings problem.

Many thanks for your help.

 

[MajP]

Yes, I always use the SQLDate function I included or a variation of it for such a purpose.

I wrote the solution as a function because it will give you far more flexibility than a query. You can use that function in code, a calculated control, query etc. My test example may have been bad since I passed literals instead of control values which would have been more descriptive.