Attn: Allen Browne - Old Question - Verify If Start Date Exists In Another Record Before Write

[Jeff Garrison]

Allen -

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]

Something along these lines:

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.

 

[Jamie Collins]

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]

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.

 

[Jamie Collins]

I believe Allen's correct in what he states.

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]

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]

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
(e-mail address removed)

 

[Jamie Collins]

On Aug 13, 5:10 pm, "James A. Fortune" <[email protected]>
wrote:

Good post.

I think it's great that Jamie is helping
prepare Access programmers for a more standardized future

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.

--

 

[Jeff Garrison]

Allen -

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

Something along these lines:

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

Reply to group, rather than allenbrowne at mvps dot org.

Allen -

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.

 

[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
(e-mail address removed)