Attn: Allen Browne - Old Question - Verify If Start Date Exists In Another Record Before Write
Jeff Garrison
I haven't tried doing the response that you gave me regarding the question
below. I'm having a major senior moment and can't figure out what the
DLookup syntax would be. Any help?
BTW the table name is tblContractorProjects...
Thanks
Jeff
-----------------------------------------
Use the BeforeUpdate event procedure of the form to perform the validation.
Use DLookup() to see if an overlapping entry exists in the table.
Assuming contractStartDate and contractEndDate are required fields (so you
don't have to handle overlapping dates when one of the fields is blank), the
dates overlap if:
A starts before B ends, AND
B starts before A ends.
and presumably it's the same contractor and/or project number as well.
So, the Criteria for your DLookup() will contain several phrases. Don't
forget ot exclude the contractID (i.e. an existing record does not clash
with itself.)
It may help to draw example events on paper like this:
A: StartDate-----------EndDate
B: StartDate-----------EndDate
C: StartDate------EndDate
to get the idea of how they overlap.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Jeff Garrison" <JGar...@bmcsolutions.com> wrote in message
news:Ol9%23sNHy...@TK2MSFTNGP06.phx.gbl...
> Hello all...
>
> I trying to do some validation of a record before it writes to be
> database. Here's the scenario:
>
> My table includes the following fields:
>
> contractID Autonumber Field
> contractContractor The Contractor's ID
> contractProjectNumber The Project Number
> contractStartDate The Start Date of the Project
> contractEndDate The End Date of the Project
>
>
> I have the entry done on a form with those fields. What I want to do is
> when the next record is entered, BEFORE the record is written to the
> database, check to see if the new record falls within any previous Start
> or End Dates.
>
> Any help would be much appreciated.
>
> Jeff G
Allen Browne
DLookup("ContractID", "tblContract",
"(contractStartDate < " & Format([contractEndDate], "\#mm\/dd\/yyyy\#") &
") AND (" & Format([contractStartDate], "\#mm\/dd\/yyyy\#") &
" < contractEndDate) AND (contractContractor = " & [contractContractor] &
")")
You may need to match the quotes/brackets etc.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Jeff Garrison" <JGar...@bmcsolutions.com> wrote in message
news:%23sg4Iyp...@TK2MSFTNGP06.phx.gbl...
Jamie Collins
> > Use DLookup() to see if an overlapping entry exists in the table.
>
> > Assuming contractStartDate and contractEndDate are required fields (so you
> > don't have to handle overlapping dates when one of the fields is blank),
> > the
> > dates overlap if:
> > A starts before B ends, AND
> > B starts before A ends.
That is correct only when A containts B. For A overIaps B you surely
meant 'OR'. It makes a big difference! :)
Also note it is common to use the NULL value (presumably what you mean
by 'blank date') for an end date to signify the period in the current
state, when would be appropriate to use the current timestamp NOW() in
place of the NULL value i.e. NULLs should indeed be considered when
testing for overlapping periods. Again, this is a significant point.
> > and presumably it's the same contractor and/or project number as well.
>
> > So, the Criteria for your DLookup() will contain several phrases. Don't
> > forget ot exclude the contractID (i.e. an existing record does not clash
> > with itself.)
>
> > It may help to draw example events on paper like this:
> > A: StartDate-----------EndDate
> > B: StartDate-----------EndDate
> > C: StartDate------EndDate
> > to get the idea of how they overlap.
>
> Something along these lines:
>
> DLookup("ContractID", "tblContract",
> "(contractStartDate < " & Format([contractEndDate], "\#mm\/dd\/yyyy\#") &
> ") AND (" & Format([contractStartDate], "\#mm\/dd\/yyyy\#") &
> " < contractEndDate) AND (contractContractor = " & [contractContractor] &
> ")")
>
Another approach which is IMO more intuitive is to use a Calendar
table with one row per day (within a large range) and use GROUP BY to
count the clashes between key value and day e.g. someting like:
... WHERE NOT EXISTS
(
SELECT E1.employee_number, C1.dt
FROM EarningsHistory AS E1, Calendar AS C1
WHERE C1.dt BETWEEN E1.start_date AND E1.end_date
GROUP BY E1.employee_number, C1.dt
HAVING COUNT(*) > 1
);
Jamie.
--
Douglas J. Steele
news:1187004806.6...@r34g2000hsd.googlegroups.com...
> On 10 Aug, 02:04, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
>> > Use DLookup() to see if an overlapping entry exists in the table.
>>
>> > Assuming contractStartDate and contractEndDate are required fields (so
>> > you
>> > don't have to handle overlapping dates when one of the fields is
>> > blank),
>> > the
>> > dates overlap if:
>> > A starts before B ends, AND
>> > B starts before A ends.
>
> That is correct only when A containts B. For A overIaps B you surely
> meant 'OR'. It makes a big difference! :)
I believe Allen's correct in what he states.
Let's say A starts 2007-08-13 at 09:00 and ends 2007-08-13 at 12:00.
If B starts at 2007-08-13 at 10:00 and ends 2007-08-13 at 14:00, you've got
A starting before B ends and B starting before A ends, but A does not
contain B.
Heck, A starting before B ends would catch any B starting after 2007-08-13
09:00. In other words, using OR would result in a B starting and ending on
2007-08-15 being flagged as an overlap.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
Jamie Collins
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:
> > > > the
> > > > dates overlap if:
> > > > A starts before B ends, AND
> > > > B starts before A ends.
>
Yes, you're correct of course. Thanks for picking me up on the point!
> Let's say A starts 2007-08-13 at 09:00 and ends 2007-08-13 at 12:00.
>
> If B starts at 2007-08-13 at 10:00 and ends 2007-08-13 at 14:00, you've got
> A starting before B ends and B starting before A ends, but A does not
> contain B.
>
> Heck, A starting before B ends would catch any B starting after 2007-08-13
> 09:00. In other words, using OR would result in a B starting and ending on
> 2007-08-15 being flagged as an overlap.
Question: if A starts #2007-08-13 09:00:00# and ends #2007-08-13
12:00:00#
and B starts #2007-08-13 12:00:00# and ends #2007-08-13 14:00:00#,
is this an overlap?
Jamie.
--
Douglas J. Steele
>
> Question: if A starts #2007-08-13 09:00:00# and ends #2007-08-13
> 12:00:00#
> and B starts #2007-08-13 12:00:00# and ends #2007-08-13 14:00:00#,
> is this an overlap?
My answer is that's a business rule that needs to be set.
Sometimes consecutive events like that are fine, sometimes they aren't.
If you're in a situation where you need to do maintenance between events
(such as a meeting facility where seating setup is required), then it's
possible that you'd want A ending at #2007-08-13 12:00:00# and B starting
#2007-08-13 14:00:00# considered to be an overlap.
Jamie Collins
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:
> > Question: if A starts #2007-08-13 09:00:00# and ends #2007-08-13
> > 12:00:00#
> > and B starts #2007-08-13 12:00:00# and ends #2007-08-13 14:00:00#,
> > is this an overlap?
>
> My answer is that's a business rule that needs to be set.
>
> Sometimes consecutive events like that are fine, sometimes they aren't.
>
> If you're in a situation where you need to do maintenance between events
> (such as a meeting facility where seating setup is required), then it's
> possible that you'd want A ending at #2007-08-13 12:00:00# and B starting
> #2007-08-13 14:00:00# considered to be an overlap.
It wasn't a trick question :)
Note that in my question (different from the OP's), period A ends
#2007-08-13 12:00:00# and B starts #2007-08-13 12:00:00# i.e. the same
DATETIME value
I could have asked whether you use closed-closed representation (A
overlaps B by almost a second) or closed-open representation (A meets
B i.e. are contiguous periods) or something else, but I was trying to
avoid jargon.
Rather than 'business rules', it is a question on how to handle the
nature of time. The way I see it, closed-open representation fits the
floating point nature of Access/Jet's DATETIME, however I personally
find it unintuitive e.g. if you told me the end date for submitting my
homework was 14 August I'd turn up on the 14 August, homework in hand,
to learn I was late. Tell me the last available time granule for
submission is #2007-08-13 23:59:59# and I'd be in no doubt :)
PS Who am I kidding? I *always* hand in my homework late. Miss, the
dog ate my end date, Miss!
Jamie.
--
James A. Fortune
> "Jamie Collins" <jamiec...@xsmail.com> wrote in message
> news:1187014686.2...@g4g2000hsf.googlegroups.com...
>
>>Question: if A starts #2007-08-13 09:00:00# and ends #2007-08-13
>>12:00:00#
>>and B starts #2007-08-13 12:00:00# and ends #2007-08-13 14:00:00#,
>>is this an overlap?
>
>
> My answer is that's a business rule that needs to be set.
>
> Sometimes consecutive events like that are fine, sometimes they aren't.
>
> If you're in a situation where you need to do maintenance between events
> (such as a meeting facility where seating setup is required), then it's
> possible that you'd want A ending at #2007-08-13 12:00:00# and B starting
> #2007-08-13 14:00:00# considered to be an overlap.
>
If you can calculate the amount of overlap precisely then your seating
setup example just needs enough overlap for the seating setup. After
reading the Snodgrass' pdf article posted by Jamie
(http://www.cs.arizona.edu/people/rts/tdbbook.pdf), I adopted the
convention that all time endpoints are at the left-hand side of the
second interval. I often use a TimeIntersection function to hide some
of the details until I get the SQL running, then replace the
TimeIntersection with equivalent SQL.
My TimeIntersection function is shown here:
http://groups.google.com/group/microsoft.public.access/msg/9ce7997df8677fca
I'm posting the TimeIntersection function again in the hope that it will
highlight some of the issues. Note: It returns the number of hours
overlapping two time intervals (including decimal part), but can be
changed easily to display minutes or seconds of overlap. This doesn't
address Null values. It's not even my answer to the problem.
The seemingly small issue of checking if dates overlap masks a larger
one. I believe that someday, when :-) (if) Access catches up to the
latest SQL standards, that separate tables will be the norm for handling
temporal data because the built-in mechanisms for handling them that way
will greatly simplify everything. Under current conditions, without
those advanced SQL capabilites present in Access, the SQL required to
maintain a separate temporal table suggested by Jamie can get way out of
hand quickly with only a few slight changes to the requirements. Only a
few Access programmers can keep up with the complexity of the queries
shown in Snodgrass' book until Access gains newer SQL capabilities. In
the meantime, we have to get by in a way that can't quickly exceed our
SQL capacities. Handling temporal data properly is much tougher than it
looks. My advice for anyone venturing down the temporal table path is
to warn their employers that small design changes can potentially create
a huge amount of effort. I think it's great that Jamie is helping
prepare Access programmers for a more standardized future, but I do not
believe that Access provides us with a realistic means to achieve that
vision yet.
James A. Fortune
MPAP...@FortuneJames.com
Jamie Collins
wrote:
Good post.
> I think it's great that Jamie is helping
> prepare Access programmers for a more standardized future
Erm, not intentionally <g>. I'm trying to get Access programmers to
face reality.
> I believe that someday, when :-) (if) Access catches up to the
> latest SQL standards, that separate tables will be the norm for handling
> temporal data because the built-in mechanisms for handling them that way
> will greatly simplify everything.
>
> without
> those advanced SQL capabilites present in Access, the SQL required to
> maintain a separate temporal table suggested by Jamie can get way out of
> hand quickly with only a few slight changes to the requirements.
I don't know what "advanced SQL capabilities" you are hoping for
(SQL-92's OVERLAPS operator? SQL3's period constructor?) but I know
don't share your optimism about future change. I think what we today
have is a good as we'll ever get in this product.
We will not get improved temporal functionality because Access is in
maintenance mode. Sorry! but it is. What did we get as new features in
the Access 2007 engine? Multi-valued data types (a.k.a. First Normal
Form violation) without the multi-valued operators etc to go with it;
I don't think even Albert D. Kallal Access MVP(MPV = Multi-Value
Promoter <g>) had multi-valued data types on his wish list. Similarly,
Attachment fields are document management without the operators, full
text search etc. With the new MEMO columns you can "view a history" of
revisions: I hope this is not the advanced capabilities you wish for.
Spot the missing article in this series:
New Features in Microsoft Jet Version 3.0:
http://support.microsoft.com/kb/137039
Description of the new features that are included in Microsoft Jet
4.0:
http://support.microsoft.com/kb/275561
New features in Microsoft Access 2007 engine:
[don't bother looking, you won't find one; it would be an
embarrassingly short piece]
SQL Server 2008 will be getting four new temporal data types DATE,
TIME, DATETIME2 (scaled decimal accurate to 100 nanoseconds) and
DATETIMEOFFSET (time zones etc). AFAIK no new operators, constructors,
etc for the new temporal types (probably because they are merely
variations on the DATETIME and SMALLDATETIME theme). SQL Server seems
to be giving a node to the standards but is picking and choosing e.g.
implementing 'full SQL-99' features before 'core SQL-99' compliance
has been achieved :(
In the good old days, the Access engine (Jet) was owned by the SQL
Server team and we often saw compatible functionality 'migrating'
between products. These days, no such luck: the Access team now own a
'private fork' of the code base.
But I don't think the current situation is all that bad. I think valid-
time state models are within the capability of the *product*. We have
table-level CHECK constraints in the engine. Using two instants
(DATETIME) to model a period works well enough. Sure, the SQL is a bit
more complex than a simple JOIN on two INTEGER columns (as I
demonstrated in this thread with my unsound challenge!) but I think it
is within the capability of most SQL coders. "I'm not saying that
you'd be able to do it without some hard work, some honest hard
work ...and possibly some medicine" (Nicholas Cage in 'Family Man').
The problem is one of awareness: the need for a sequenced key, the
existence of engine-level functionality and how to use it. However,
the average Access user doesn't give a hoot about engine-level
constraints and logical keys. How do you convey the concept of a
sequenced primary key to a group who thinks, "I've added an AutoNumber
and given it the PRIMARY KEY designation for the table. My work here
is done."
Jamie.
--
> After
> reading the Snodgrass' pdf article posted by Jamie
> (http://www.cs.arizona.edu/people/rts/tdbbook.pdf), I adopted the
> convention that all time endpoints are at the left-hand side of the
> second interval. I often use a TimeIntersection function to hide some
> of the details until I get the SQL running, then replace the
> TimeIntersection with equivalent SQL.
>
> My TimeIntersection function is shown here:
>
> http://groups.google.com/group/microsoft.public.access/msg/9ce7997df8...
Jeff Garrison
Thanks for the reply...I didn't realize that it was going to set off a
firestorm of discussions.
My question is...
Now that I have the syntax, I put it in the Before Update on the form, but
is there something I need to do via VBA, such as If...Then? Also, how do I
exclude the current record? I plugged in the dlookup and made the changes
needed for table name, etc., and did an If...Then by saying If
dlookup...then msgbox "Overlapping Projects", End If. When I opened an item
the had only 1 record, I cycled through the record and when I exited the
item and went to the new one, the Message Box popped up. Am I correct in
the logic, especially the VBA part? As far as the exclusion, there won't be
an autonumber assinged to it to be used for exclusion because the record
hasn't been written to the table and therefore isn't a record ID assgined to
it.
Thanks.
Jeff
"Allen Browne" <Allen...@SeeSig.Invalid> wrote in message
news:euFxXpu2...@TK2MSFTNGP03.phx.gbl...
Jamie Collins
> I don't think the current situation is all that bad. I think valid-
> time state models are within the capability of the *product*. We have
> table-level CHECK constraints in the engine. Using two instants
> (DATETIME) to model a period works well enough.
Since posting last, a number of significant limitations have sprung to
mind:
Sequenced operations (update, insert, delete) involve multiple SQL
statements and in a Access/Jet PROCEDURE can only execute a single SQL
statements, therefore you must code your sequenced operations in VBA.
Access/Jet SQL does not support deferrable constraints so those VBA
procedures will have to DROP the sequenced key then recreate them in a
transaction that will inevitably cause lock the table for the
duration.
Perhaps Access/Jet isn't fit for purpose after all. How to prevent it
being used for these purposes <g>?
Jamie.
--
James A. Fortune
> I don't know what "advanced SQL capabilities" you are hoping for
> (SQL-92's OVERLAPS operator? SQL3's period constructor?) but I know
> don't share your optimism about future change. I think what we today
> have is a good as we'll ever get in this product.
I was referring to SQL3. SQL-92 compliance is not enough. Without it,
Snodgrass' SQL will be a nightmare to create and maintain.
James A. Fortune
MPAP...@FortuneJames.com