Finding Duplciate or overlapping date ranges

[reddwarf]

HI guys,

I have a head scratcher here and i need a little help. if you have a few mins i would really appreciate your help.

I have a small DB that stores 4 fields only ( its a test database so i can understand the concept )

its about hire cars and when they were hired.

it holds

Date the car was hired – [Hire on]
date car was returned – [Hire Off]
Registration number of car – [Reg]
Name of hirer – [Name]

what I’m trying to do is look for duplicate and overlapping hire( this is where the same car has been hired out at the same time - which should be impossible - i.e. Car reg AB1 1AA was hired on 01/02/10 to 10/02/10 to a Mr smith and hired to a Mrs. price on 09/02/10 to 20/02/10. You can see it has over lapped.

What i have is a simple form which allows you to enter a new case. then i have a button which goes into the DB and checks to see if there is any over lapping hire. I have used code to do this on the button click event. When the buttin is pressed it will open a new form which shows you all of the other hires which overlap the one you have open.

the code is : ( sorry about it's rubbish code, i am self taught and new to this )

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim RegMatch As String
Dim MIDDLE As String
Dim EITHER As String
Dim Before As String
Dim After As String


stDocName = "dupes"

RegMatch = "[Reg]=" & "'" & Me![Reg] & "'"
MIDDLE = "(([Find duplicates for main].[hire on]>#" & Me.hire_on & "# and [Find duplicates for main].[hire off]<#" & Me.hire_off & "#))"
EITHER = "(([Find duplicates for main].[hire on]<#" & Me.hire_on & "# and [Find duplicates for main].[hire off]>#" & Me.hire_off & "#))"
Before = "(([Find duplicates for main].[hire on]<#" & Me.hire_on & "# and [Find duplicates for main].[hire off]<#" & Me.hire_off & "#and [Find duplicates for main].[hire off]>#" & Me.hire_on & "#))"
After = "(([Find duplicates for main].[hire on]>#" & Me.hire_on & "# and [Find duplicates for main].[hire off]>#" & Me.hire_off & "# and [Find duplicates for main].[hire on]<#" & Me.hire_off & "#))"

stLinkCriteria = RegMatch & " and (" & MIDDLE & " or " & EITHER & " or " & Before & " or " & After & ")"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command9_Click:
Exit Sub
Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub


This code adds this to the form filter : ( this is what it looks like formatted when returning a result to the filter on the new form which shows the duplciates.

[Reg]='123456' and

((([Find duplicates for main].[hire on]>#01/01/2001#
and
[Find duplicates for main].[hire off]<#10/01/2001#))

or

(([Find duplicates for main].[hire on]<#01/01/2001#
and
[Find duplicates for main].[hire off]>#10/01/2001#))

or

(([Find duplicates for main].[hire on]<#01/01/2001#
and
[Find duplicates for main].[hire off]<#10/01/2001#
and
[Find duplicates for main].[hire off]>#01/01/2001#))

Or

(([Find duplicates for main].[hire on]>#01/01/2001#
and
[Find duplicates for main].[hire off]>#10/01/2001#
and
[Find duplicates for main].[hire on]<#10/01/2001#)))

Pretty sure it has something to do with the way I am bracketing it up with the "and" and "or" statements – but I cant figure it out. Is it something obvious I am missing >?

there is a possiblulity i am going about this all wrong - as i say, i am new to this. But if i can just get the filter working then it will suit needs perfectly.

fingers crossed

 

[JANR]

Allen Brown has an excelent article on EventClashing using a Query, perhaps it might be of interest: http://allenbrowne.com/appevent.html

It might shed some light on your problem, at least it is easier to implement than VBA.

JR

 

[JamesMcS]

Someone will correct me I'm sure but do you not have to put [reg]=.... AND [reg]=..., i.e. every AND/OR condition needs the field name and an operator in it (=,<,>,<>)

 

[DCrake]

Is this not a case of GIGO (Garbage In Garbage Out) or SISO (leave that to you to decypher). If the data collection form prevented the user from entering overlapping dates then there would be no need to test for it would they? Get it right in the first place and then you do not have to worry about errors afterwards.

 

[reddwarf]

hi guys - thanks for yoru feedback

the system is a validaiton tool - the hires are controlled by another company and entered into our system to validate/control. So - as new hires are entered they need to be checked with this to make sure there are no duplaicate hires. if there are - they still need to be added to the system to be managed.

am checking the article our now - so far its a good read. but still would like to get my method working iof possible.

also will look at using the [reg] field in every clause of code to see if that works.

cheers folks

 

[JamesMcS]

So Mr. C - if the date you want to avoid overlapping is in another record, how would you refer to it in VBA? That's the bit I'm struggling with at the moment, and liquid lunch has produced massive Access block for me....

 

[reddwarf]

still cant get it to work if anyone has any opther ideas they would be gratefully received.

d

 

[gemma-the-husky]

given a single hire, you are looking for hires for the same car where

a) the startdate is between the start and finish dates for your hire OR
b) the enddate is between the start and finish dates for your hire

so you could iterate the recordset of your new hires, and see if any fall into that category, one at a time.

i am sure you could do the comparison with a fairly simple visual query, checking the dates - then you could see what the SQL looked like

I would be surprised if you could check every vehicle at the same time with a single query - but it may be possible.

 

[boblarson]

You might take a look at this by Access MVP Pbaldy.