Date Comparison Code Help

[jackson_jl]

What I have is a form that has five combo boxes, Customer, Resource Type, Patch, StartDate, and EndDate. On the form I select a Customer, a Resource Type, a Patch, a StartDate and an EndDate. I then have a SaveRecord Command Button. The On Click function is as follows:

[vba]Private Sub cmdSaveRecord_Click()

Dim strWhere As String
Dim stDocName As String
Dim stLinkCriteria As String
Dim cmdSave As String
Dim Patch As String
Dim ResourceType As String

stDocName = "frmScheduleResource"
stLinkCriteria = "[cmdSaveRecord]=" & "" & cmdSaveRecord & ""
Resourcetype = "qryScheduledResourceType.Resource Type"

DoCmd.OpenForm stDocName, , , stLinkCriteria

If IsNull(Me.Patch) Then
MsgBox "You must specify a Patch."
Exit Sub

Else

strWhere = "qryScheduledResourceType.Patch = " & Me.Patch
End If

If IsNull(Me.ResourceType) Then
MsgBox "You must specify a Resource Type."
Exit Sub

Else

strWhere = "qryScheduledResourceType.ResourceType = " & Me.ResourceType
End If

If IsNull(Me.StartDate) _
Or (Not IsDate(Me.StartDate)) Then
MsgBox "You must enter a start date."
Exit Sub

Else

strWhere = strWhere & _
" AND EndDate >= #" & Me.StartDate & "#"
End If

If IsNull(Me.EndDate) _
Or (Not IsDate(Me.EndDate)) Then
MsgBox "You must enter an end date."
Exit Sub

Else

If Me.StartDate > Me.EndDate Then
MsgBox "Start date cannot be greater than end date."
Exit Sub

End If

strWhere = strWhere & _
" AND EndDate <= #" & Me.EndDate & "#"

End If[/vba]

If the new Patch and Resource Type have a entry that matches dates between an old StartDate and EndDate, Then

MsgBox ?Resources Already scheduled. Pick another resource or dates?

[vba]Exit Sub

DoCmd.OpenForm "frmScheduledResource", WhereCondition:=strWhere

End Sub

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSaveRecord_Click:

Exit Sub

Err_cmdSaveRecord_Click:

MsgBox Err.Description

Resume Exit_cmdSaveRecord_Click

End Sub[/vba]

Text in Red = Code that I don't know how to express.

What I want the function to do is On Click:

• Open a query that has the five fields listed above.
• Compare the Patch, Resource Type and StartDate, EndDate fields to see if:

a: if Patch = Null, Then an error message box appears.
b: if Resource Type = Null, Then an error message box appears.
c: if StartDate = Null, Then an error message box appears.
d: if EndDate = Null, Then an error message box appears.
e: if StartDate > EndDate, Then an error message box appears.
f: if Patch and Resource Type > 0 and new StartDate and new EndDate = old StartDate and old EndDate, Then an error message box appears. (for example:

CustomerID Patch ResourceTypeID StartDate EndDate
Customer A (1) NO TEST TYPE 1 01-Mar-05 03-Mar-05
Customer B (2) NO TEST TYPE 2 02-Mar-05 04-Mar-05
Customer C (3) NO TEST TYPE 3 03-Mar-05 05-Mar-05
Customer D (4) NO TEST TYPE 4 04-Mar-05 06-Mar-05

In the table above, if I input a new record as follows:

Customer E (1) NO TEST TYPE 1 2-Mar-05 3-Mar-05

I should get an error message, because Patch: (1) NO TEST and ResourceTypeID, TYPE 1 is already being used by Customer A from
1 to 3 March 2005. Customer E should not be able to use this Patch and Resource Type until 4 March, 2005. So I need code that searches dates between the start and end dates)

f: if Patch and Resource Type = 0, Then perform save record function.

• Close the query

When I run the code above, I am getting a Compile Error: method or data member not found.

If you or someone knows what I should do, Please help.

[Groundhog]

Hi - You may need to obtain a recordset that matches your resource type and patch first. Then try looping through the recordset for your date comparisons:

[vba]Dim myDatabase As Database
Dim rs As Recordset
Dim strquery As String
Dim NewCustomer As String
Dim NewResource As String
Dim NewPatch As String
Dim NewStartDate As Date
Dim NewEndDate As Date
Dim i As Integer
Dim maxcnt As Integer
NewCustomer = Me.ComboCustomer
NewResource = Chr(34) & Me.ComboResource & Chr(34)
NewPatch = Chr(34) & Me.ComboPatch & Chr(34)
NewStartDate = Me.ComboStartDate
NewEndDate = Me.ComboEndDate

strquery = "SELECT tblCustomers.ResourceType, tblCustomers.Patch, _
tblCustomers.StartDate, tblCustomers.EndDate FROM tblCustomers" & vbCrLf

strquery = strquery & "WHERE tblCustomers.ResourceType = " & _
NewResource & " AND tblCustomers.Patch = " & NewPatch & ""

Set myDatabase = CurrentDb()
Set rs = myDatabase.OpenRecordset(strquery)
maxcnt = rs.RecordCount
i = 0
rs.MoveFirst
Do Until rs.EOF
If NewStartDate >= rs!StartDate And NewStartDate <= rs!EndDate Then
MsgBox "error"
Exit Do
Else
If NewEndDate >= rs!StartDate And NewStartDate <= rs!EndDate Then
MsgBox "error"
Exit Do
End If
End If
If i = maxcnt Then
Exit Do
End If

rs.MoveNext
Loop

set rs = Nothing
Set myDatabase = Nothing
End Sub[/vba]

[Anne Troy]

Hey, Groundhog! Great to see another helper in the Access area!

I've edited your VBA code above to use VBA tags, as described at the link in my signature.

Thanks!