Comparing dates problem

[KingTravis via AccessMonster.com]

Hi,

I'm working on a hire program at the min and seem to be a *bit* stuck

what i need to do is avoid hire dates overlapping one another, for example,
DOA = date of agreement

DOA Serial No Hire start Hire return
01/04/2009 12345 67890 01/05/2009 - 10/05/2009 Confirmed agreement
01/04/2009 12345 67890 01/05/2009 - 05/05/2009 NO
02/04/2009 12345 67890 20/04/2009 - 01/05/2009 NO
03/04/2009 12345 67890 10/05/2009 - 12/05/2009 NO
05/04/2009 12345 67890 11/05/2009 - 14/05/2009 YES
06/04/2009 12345 67890 24/04/2009 - 30/04/2009 YES

the very first example is a saved record, the rest are different attempts at
hiring the same bit of equipment. i have an idea in my head of what to do,
the problem is getting it out of my head in actually getting it to work!!!!!

for every hire agreement record
if ((current serial no = other serial no) AND (current hire start = other
hire start))
error message
else
save record
end if
end for

any help or pointers in the right direction would be greatly appreciated,
again!!! and thanks in advance!!

 

[Arvin Meyer MVP]

You might have a look at this scheduling MDB:

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=35

 

[Steve Sanford]

I am going to need a routine like this soon, so I came up with this code. I
did a little testing, but you need to test it....... you've been warned!!!

I made 3 unbound controls on a form and a button. I named the controls:

txtSerial - for the serial number
HStart - for the new start date
HEnd - for the new return date

You need a reference to DAO......

Here is the routine:
(watch for line wrap)

'-----------------------------------------------------
Option Compare Database
Option Explicit

Private Sub cmdCheckDates_Click()
'ADD ERROR CODE!!!!

Dim sSQL As String
Dim r As DAO.Recordset
Dim d As DAO.Database
Dim tstSerialNum As String
Dim tstStartDate As Date
Dim tstReturnDate As Date

Set d = CurrentDb

tstSerialNum = Me.txtSerial
tstStartDate = CDate(Me.HStart)
tstReturnDate = CDate(Me.HEnd)

'these lines should begin with "sSQL" ----------------------
sSQL = "SELECT T.SerialNo, T.HireStart, T.HireReturn"
sSQL = sSQL & " FROM Try2 as T"
sSQL = sSQL & " WHERE T.SerialNo = '" & tstSerialNum & "'"
sSQL = sSQL & " AND ((((T.HireStart)<= #" & tstStartDate & "#)"

sSQL = sSQL & " AND ((T.HireReturn)>= #" & tstReturnDate & "#))"

sSQL = sSQL & " OR (((T.HireStart ) Between #" & tstStartDate & "# And #"
& tstReturnDate & "#))"

sSQL = sSQL & " OR (((T.HireReturn) Between #" & tstStartDate & "# And #"
& tstReturnDate & "#)));"
'-------------------------------------------

' Debug.Print sSQL

Set r = d.OpenRecordset(sSQL, dbOpenSnapshot)

If r.RecordCount > 0 Then
'equipt rented
MsgBox "ERROR!!! Equiptment Rented "
Else
MsgBox "Yes - OK to rent"

'Add code record here
sSQL = "INSERT INTO YourTable (DOA, SerialNo, HireStart, HireReturn)"
sSQL = sSQL & " VALUES ( #" & Me.DOA & "#, '" & tstSerialNum
sSQL = sSQL & "', #" & tstStartDate & "#, #" & tstReturnDate & "#)"

d.Execute sSQL, dbFailOnError

' here maybe clear unbound controls on form
'
End If

r.Close
Set r = Nothing
Set d = Nothing

End Sub
'---------------------------------------------------

HTH

 

[Peter Hibbs]

KingTravis,

The trick is to compare the Start and End dates of the new record
with the start and end dates of each existing record, if the new
start date is less than the existing record end date AND the new end
date is greater than the existing record start date then logically the
date periods must overlap.

To try it out paste the function below into a code module and
change the table and field names as appropriate.

Call it like this :-

Dim vID As Long

vID = HireCheck(txtHireStart, txtHireReturn, txtSerialNo)
If vID > 0 then
MsgBox "ERROR. Hire dates overlap."
Else
'Add new record to table here.
End If

where -
txtHireStart holds the start date of the new period and
txtHireReturn holds the end date of the new period and
txtSerialNo holds the serial number of the specified equipment.

I assume that Serial No is a Text type field.
If the time periods overlap, the function returns the AutoNumber ID of
the record which it overlaps, you could then use this information to
show the user which existing record already exists for that period.

'--------------------------------------------------------------------------------------
Public Function HireCheck(vStart As Date, vEnd As Date, vSerialNo as
String) As Long

'Checks if new hire period overlaps any existing periods
'Entry vStart = Start date of new period
' vEnd = End date of new period
' vSerialNo = Serial No of specified equipment item
'Exit HireCheck= ID of existing record if new period overlaps
'or 0 if no overlap

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT * FROM [tblHirings]" _
& " WHERE [Serial No] = '" & vSerialNo & "'")
Do Until rst.EOF
If vStart < rst![Hire Return] And vEnd > rst![Hire Start] Then
HireCheck= rst!ID
Exit Function
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

End Function
'--------------------------------------------------------------------------------------
(Watch out for word wrapping when you paste the code).

Change tblHirings to the name of your table. If you don't already have
an AutoNumber field named ID in this table you will need to add one to
identify the records. If you already have a unique Number field, you
can use that instead. Note that the two date fields MUST contain valid
dates for all records.

Also, I suggest you change your field names (if you can) to not have
any spaces in, i.e. HireStart instead of Hire Start, it will save you
a lot of trouble later.

HTH

Peter Hibbs.

 

[KingTravis via AccessMonster.com]

Hi,

just got a read through the posts there now, quite scary lol i'll give it ago
and see how it goes, thanks to everyone!

 

[KingTravis via AccessMonster.com]

Hey

just a quick word to say got the problem sorted, thanks again for the help!