Validation rule to prevent overlapping time periods

I have a table:

InactID-AutoNumber
PreceptorID-Number
InactStart-Date/Time
InactEnd-Date/Time

I don't want records that have overlapping time periods for a 
PreceptorId. I tried:


[InactStart] < (DLookUp("[InactEnd]","tblInactive","[PreceptorID]=" & 
[PreceptorID] & " and [InactID]=" & [InactID]-1))

but found that the DLOOKUP function was not permitted in a table 
validation rule.

Any suggestions?
0
Stace
12/19/2007 10:48:46 PM
access 16762 articles. 3 followers. Follow

5 Replies
1960 Views

Similar Articles

[PageSpeed] 36

You won't be able to use a table validation rule here. Instead, use the 
BeforeUpdate event procedure of the *form* where the data is entered. Cancel 
the event if there is a clash.

The logic for a clash is:
- Event A starts before Event B ends, AND
- Event B starts before Event A ends.
- Event A and Event B are not the same thing.

You don't need to perform this when editing a record where the time did not 
change, and you can't run the test unless you have both time values. So, the 
event procedure will be something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strWhere As String
    Dim varResult As Variant
    Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#"

    If (Me.[InactStart-Date/Time] = Me.[InactStart-Date/Time].OldValue _
        AND Me.[InactEnd-Date/Time] = Me.[InactEnd-Date/Time].OldValue) _
        OR IsNull(Me.[InactStart-Date/Time]) _
        OR IsNull(Me.[InactEnd-Date/Time]) Then
            'Do nothing
    Else
        strWhere = "(" & Format(Me.[InactStart-Date/Time], strcJetDateTime) 
& _
            " < [InactEnd-Date/Time]) AND (InactStart-Date/Time] < " & _
            Format(Me.[InactEnd-Date/Time], strcJetDateTime) & _
            ") AND ([InactID-AutoNumber] <> " & 
Nz(Me.[InactID-AutoNumber],0) & ")"
        varResult = DLookup("[InactID-AutoNumber]", "Table1", strWhere)
        If Not IsNull(varResult) Then
            Cancel = True
            MsgBox "Clash wiht InactID " & varResult
            'Me.Undo
        End If
    End If
End Sub

-- 
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.

"Stace Cameron" <scameron@siumed.edu> wrote in message
news:Ow9$TFpQIHA.4196@TK2MSFTNGP04.phx.gbl...
>I have a table:
>
> InactID-AutoNumber
> PreceptorID-Number
> InactStart-Date/Time
> InactEnd-Date/Time
>
> I don't want records that have overlapping time periods for a PreceptorId. 
> I tried:
>
>
> [InactStart] < (DLookUp("[InactEnd]","tblInactive","[PreceptorID]=" & 
> [PreceptorID] & " and [InactID]=" & [InactID]-1))
>
> but found that the DLOOKUP function was not permitted in a table 
> validation rule.
>
> Any suggestions? 

0
Allen
12/20/2007 12:17:54 AM
On Dec 19, 10:48 pm, Stace Cameron <scame...@siumed.edu> wrote:
> I have a table:
>
> InactID-AutoNumber
> PreceptorID-Number
> InactStart-Date/Time
> InactEnd-Date/Time
>
> I don't want records that have overlapping time periods for a
> PreceptorId. I tried:
>
> [InactStart] < (DLookUp("[InactEnd]","tblInactive","[PreceptorID]=" &
> [PreceptorID] & " and [InactID]=" & [InactID]-1))
>
> but found that the DLOOKUP function was not permitted in a table validation rule.

The "table validation rule" is a misnomer (IMO) because it is actually
at the row level. You can use a Jet CHECK constraint (not well exposed
in the Access UI) which is truly table level.

If you periods are whole days then you could use the standard trick of
a Calendar table, then in your CHECK constraint join to this table
using GROUP BY on the calendar date e.g.

CHECK (NOT EXISTS (
SELECT C1.calendar_date
FROM EarningsHistory AS E1,
Calendar AS C1
WHERE C1.calendar_date BETWEEN E1.start_date
AND E1.end_date
GROUP BY E1.employee_id, C1.calendar_date
HAVING COUNT(*) > 1))

For a full demonstration see:

http://groups.google.com/group/microsoft.public.access.tablesdbdesign/msg/0fb58491ff039e1c

Otherwise, use a regular query in a table level CHECK constraint to
test for overlapping periods. The 'textbook' (Snodgrass's -- google
it) query for this (using closed-open representation) in Jet SQL
syntax:

SELECT S1.ssn
FROM SalaryHistory AS S1, Incumbents AS I1
WHERE S1.ssn = I1.ssn
AND IIF(S1.start_date > I1.start_date, S1.start_date, I1.start_date)
< IIF(S1.end_date > I1.end_date, I1.end_date, S1.end_date);

Obviously, you need to write this as a 'self join' for your
constraint. There are some decision to make along the way e.g.
representation (e.g. closed-closed or closed-open or something else),
the candidate keys on your table, ensuring end_date does not occur
before start_date, how to model the period in the current state
(end_date = NULL or end_date = <far future date representing infinity>
or something else), etc. Here's a long post from the google groups
archive which considers such issues:

http://groups.google.com/group/microsoft.public.access.forms/msg/04c3f233ba3336cc

Jamie.

--

0
Jamie
12/20/2007 10:20:55 AM
On Dec 20, 12:17 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
> You won't be able to use a table validation rule here.
> Instead, use the
> BeforeUpdate event procedure of the *form* where the data is entered.

I don't think it is right to encourage the OP to move constraints from
the engine to the front end (or rather, each and every front end that
will ever access the data). I think the OP's request for a table
constraint is worthy of consideration. IMO data validation rules
should be defined as close to the data as possible and in this case an
engine level CHECK constraint could work.

Anyhow, isn't a forms solution OT for the 'tables' group <g>?

Jamie.

--

0
Jamie
12/20/2007 10:30:53 AM
On Dec 20, 10:30 am, Jamie Collins <jamiecoll...@xsmail.com> wrote:
> Anyhow, isn't a forms solution OT for the 'tables' group <g>?

Ignore this (why have I strayed outside the 'tables' group <g>?!) I
still think a engine/table level constraint should be preferred over a
'front end' implementation when it comes to data validation.

Jamie.

--

0
Jamie
12/20/2007 10:34:23 AM
"(not well exposed in the Access UI)" is quite an understatement.

Thanks to your help, I believe I have solved my problem with the 
following code:

SQL = "ALTER TABLE tblInactive ADD CONSTRAINT no_overlapping_periods 
CHECK (NOT EXISTS (" & _
  "SELECT *" & _
  " FROM tblInactive A " & _
  "where  PreceptorID = A.PreceptorID and " & _
  "exists( select * from tblInactive B where " & _
  "(InactStart BETWEEN A.InactStart AND A.InactEnd) and " & _
  "(PreceptorID =  A.PreceptorID) and " & _
  "(InactID     <> A.InactID))));"

  CurrentProject.Connection.Execute SQL
0
Stace
12/21/2007 4:33:46 PM
Reply:

Similar Artilces:

Problem with komma versus period in Excel
Hej I am have problems with the use of period in Excel. I Denmark komma is the decimal seperator. If I convert (from within VB Editor) the content of a cell from "6340,00" to text and replace the komma with period I get "6340" (I wanted "6340.00). If I make the convertion in Excel and record the macro at the same time I get the desired result i my sheet. BUT when I put in my code in VBA and run it from here I still get the wrong result, "6340" and not "6340.00". I know Excel (VBA) transform "." to "," probably acording to...

Is it possible to generate non-technical schema validation errors?
With the 1.0 Framework, I've worked out using the XmlValidatingReader. Since I'm using the validation errors as feedback to the end user, I'm hoping to get away from techy messages such as "The 'http://tempuri.org/XMLFile1.xsd:MaxDependents' element has an invalid value according to its data type. An error occurred at file:///c:/work/prodika/main/code/apps/schemavalidation/XMLFile1.xml(8, 25)." and go with a user friendly message of "Max Dependents must be between 0 and 10". I've scoured the newsgroups, MSDN and docs for creating custom valid...

How to create an "and" rule in Query Based Distribution Groups
Hi, With Exchange 2003 Query Based Distribution groups, is it possible to create an "and" rule? ie, all users who are based in "London" "and" have the first name "John"? Thanks, Curtis. -- Please reply to news group only. Thank you. Sure. (&(attribute1=blah)(attribute2=blah)) http://msdn.microsoft.com/library/en-us/adsi/adsi/search_filter_syntax.asp?frame=true -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "Curtis Fray" <xxx@xxx.com> wrote in message news:OjVc...

Time performance Chart
Greetings. (help) I need to develop a chart that graphically depicts an employees start time and end time each day. This chart would be a bar chart. The bar chart would have days of the month along the bottom, and the side would start at 00:00 at the bottom and end at 24:00 at the top. I need to be able to enter an employees start time and end time and have it charted along the bar. So if the employee started at 07:00 and ended work at 18:00, the chart would show a colored bar that corresponds to the start time and the end time. Also, if an employee came back to work the chart would refl...

date/time formatting
I download a csv report from a web based program. Everything works great but the date comes through as "Jul 21 2009 1:51pm". I do not need the time in my report. I have tried reformatting the cells, tried to copy to a new file with the cells already formatted to "7/21/09", opening the cell format using the F2 key and deleting the time (time will still not be formatted correctly). The only way I get get just the time is to retype every cell. What am I doing wrong? I am using Excel 2002 SP3 TIA, Cindy When data is downloaded from the web, a lot of "other&quo...

Time Conversion
I need help in converting time. I am using data from a time clock which currently formats time as 1.5. When I input this data into a cell how can I have it formated to read 1 hour and 30 minutes (1:30) T.I.A. Ed What you could do in an adjacent row or column is format them as Time and then using a formula, divide the input time by 24(hours in a day). >-----Original Message----- >I need help in converting time. I am using data from a time clock which >currently formats time as 1.5. When I input this data into a cell how can I >have it formated to read 1 hour and 30 minut...

Prevent users from saving music and pictures to their hard drives without folder redirection?
Hi there. I'm wondering if there is a way via GPO or anything else to prevent users from saving music and pictures to their local hard drives without using any type of profile or folder redirection. Thanks. ...

Sum Times
I have four fields on a form to show time. I want a seprate "Total" field to add the time between the first two fields and then add the time between the second two fields. Like this: In LunchOut LunchIn Out Total 6:00am 12:00pm 12:30pm 4:30pm 10 hours The first four fields are stored as medium times. Can someone let me know how to do this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200707/1 "ladybug via AccessMonster.com&quo...

time formats #3
I have an Excel sheet with a long list of times spent on various projects. The times should all be in minutes and seconds. The first time reads 2:29 and I know that is accurate, for 2 min, 29 seconds. In the formula bar, it reads, 2:29:00 AM. Another...the cell displays 0:40 and I know that is right, for 40 seconds. But in the formula bar, I see 12:40:00 AM I want numbers, but apparently I am looking at times. I tried changing the format. When I check the format for 2:29, it comes up Custom and says it is hh:mm, not mm:ss. I tried changing it to mm:ss, but that changes the displa...

Winmail takes 4 minutes to load at boot time
Running Windows Vista Home Premium on Lenovo Laptop 2 gig mem. After logging in after rebooting, it takes 4 minutes to load Win mail. I used the "event Viewer" to see what was happening. It showed ESENT with ID 102--start Win mail, then the next event is ESENT ID 103 4 minutes later--terminate Win mail. Anyone have a clue why it is taking Win mail so long to load? Win mail works fine after computer loads. Fred Dwight Fred wrote: > Running Windows Vista Home Premium on Lenovo Laptop 2 gig mem. After > logging in after rebooting, it takes 4 minutes ...

Preventing changes in the Options
Hello, Is there a way in Excel to prevent users from making any changes in the Options (ie protect or disable the Options Dialog box). Thanks in advance, brgds, Chantal ...

Taking a loooong time for new mailboxes to appear.
Hello, We have a tool which creates a batch of mailboxes, then logs into each one via MAPI to migrate old messages. Today we ran the tool and mailbox-enabled 300+ mailboxes in one go. Although the Exchange tabs were present and the SMTP address was filled out on each user in ADUC, the mailboxes didn't appear in ESM until about an hour later. How is that possible? Manually running RUS didn't help either. We've got one DC and a separate E2k3 server. Thanks, - Alan. Any RED errors in the event logs on that Exchange box during that time> "Alan" wrote: > Hello, ...

Rules gone crazy!
When I create a rule to move mail sent to a second e-mail account into a specified folder I follow the steps listed at the end of this post. However, when I choose the email acount I'm interested in (we'll use New email and Old email as the accounts) it changes on me. In other words, let's say I want a rule that sends my Old email messages to an Old Email folder, I follow the correct steps and then press ok. When I go back to look at the rule afterwards, the email account shows New email, instead of Old email. No matter how many times I change it back or punch the wal...

how do I format cells to change date and time to just date
I want to format a column that contains date and time and I want it to show just the date and not the time. Going into format and clicking on the date and changing doesnt work. Probably the cells aren't really dates, but text. You can check with the ISTEXT() function. If they are text, formatting doesn't have any effect. -- Kind regards, Niek Otten Microsoft MVP - Excel "bondam" <bondam@discussions.microsoft.com> wrote in message news:F4D61EC1-30E2-4723-89E1-F47B545818EE@microsoft.com... >I want to format a column that contains date and time and I want it t...

Prevent copy and paste in one column
I am having trouble trying to prevent copying and pasting in one specific column. The code refers to the specific range, but yet it prevents copying and pasting on the whole worksheet. Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Columns("H:H")) Is Nothing Then Application.CellDragAndDrop = False Application.CutCopyMode = False Else Application.CellDragAndDrop = True End If End Sub Works for me in Excel 2003 Gord Dibben MS Excel MVP On Fri, 30 Apr 2010 11:42:01...

How to prevent line breaks in XML file?
I am creating XML from SQL2000 using a vb.net page, then posting it to a remote server as a string. The remote server requires that the xml contain no line breaks, but there are some ntext fields in the database that contain long text with line breaks. How do I get rid of the line breaks? Can I do this with the XmlTextWriter, or do I need to handle each affected field seperately before handing it to the XmlTextWriter? Thanks Leslie les wrote: > How do I get rid of the line breaks? Can I do this with the > XmlTextWriter, or do I need to handle each affected field seperately > bef...

Counting the number of times more than 1 variable occurs
I need to determine how many Separations were processed by a particular salesperson. A | B 1 MIKE | SEPARATION 2 MIKE | LEAVE 3 SARA | SEPARATION 4 JOE | SEPARATION 5 JAMIE | LEAVE 6 MIKE | LEAVE 7 JOE | LEAVE 8 SARA | SEPARATION The only way I can think of is the COUNTIF Funtion *COUNTIF(-range,criteria-)* =COUNTIF(A1:A8,(A1:A8="MIKE")*(B1:B8="SEPARATION")) This does not work though. The results return 0 with no errors when the answer should be 1. does anyone else know of a wa...

Validation Problem #2
I have a schema file PDDSch.xsd which validates the PDD.xml file. the rootnodes are as shown below PDD.xml <PDD xmlns="http://tempuri.org/PDDSch.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-Instance" > PDDSch.xsd <xs:schema id="PDD" targetNamespace="http://tempuri.org/PDDSch.xsd" xmlns="http://tempuri.org/PDDSch.xsd" xmlns:mstns="http://tempuri.org/PDDSch.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="quali...

Want to modify this procedure to have a range of periods rather than a single period
This is a multi-part message in MIME format. ------=_NextPart_000_00BF_01CB206B.89E3AC60 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have the procedure below I want simply modify the periods procedure below to be -- perpost >=3D = '201001' and <=3D '201012' so I want a range of periods rather than a = single period as in the example below I would really appreciate it if someone can help me out on this. Thanks=20 Sammy USE DEMOAPP Declare @perpost nvarchar(8) /* Do not edit Above=20 ...

How to prevent users sending mail on Internet ?
Hello all, Do you know how to prevent certain users (NOT all users) to sending mail on Internet ? I am using Exchange 2003 with Outlook 2000 clients Thank you in advance, Denis PS : french translation of this message is also posted on this group you could try permissions on the default server object. Not tried this though. Just had a play with my exchange setup. If your using Connectors, you can set who can access through it. Under delivery restrictions select who can access the net through this connector. Then reject all others. If the address space on this connector is set to * ...

Date/Time stamp in Memo Field
Hi all..is it possible to programmatically insert Now() when text in a Memo field becomes edited? Thanks for all help! If it has to be entered directly in the memo field tetxbox: Private Sub MemoFieldName_AfterUpdate() If IsNull(Me.MemoFieldName.OldValue) Then Me.MemoFieldName = Now & " " & Me.MemoFieldName Else Me.MemoFieldName = Left(Me.MemoFieldName, Len(Me.MemoFieldName.OldValue)) & " " & Now & " " & Right(Me.MemoFieldName, Len(Me.MemoFieldName) - Len(Me. MemoFieldName.OldValue)) End If Me.Dirty = False End...

Run-time error 432
I'm getting a run-time error 432 when trying to open some areas of a VB program in excel. This program works on some machines and not others. We all have the same version of excel and our settings seem to be the same. Does anyone have any idea where I can start to look for the reason this is happening? Thanks Kevin After doing a search of Google, you may like to try this solution... Go to... http://www.dlldump.com/download-dll-files_new.php/dllfiles/D/dao360.dll/03.60.8618.0/download.html download and install file.... Then go to RUN and type: Regsvr32C:\WINDOWS\SYSTEM\Dao360.dll ...

Rules Wizard #53
After installing office 2003 which connects to MS small biz server when i select the Rules Wizard its just flashes up and disapears again, i have tried the repair option and even installed a new copy of Office on this XP machine and it still wont work, it works ok on the other PC's in the office, thanks in advance of any help i can get ...

Outlook Filter Mail
Hi, I would like to know if it is possible to direct mail from a particular domain (e.g. @yahoo.com) to a particular folder using Rules & Alerts or VBA Macro. Thanks for your help! Regards Wings Sure. Use the condition "with specific words in the sender's address" = and enter the domain name.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx...

Validate if a mail user exsits
Can Exchange 2003 SP2 be configured to just drop any email that it receives for and @company.com address that doesn't have a valid username? I am looking for a built in option or relativley easy/inexpensive option, nothing like GFI Mailessentilas or the like, but preferably built in to Exch 2003. Thanks, Christopher Parrish Yes. ESM/Message Delivery/Properties/Recipient Filtering. Check the box "Filter recipients who are not in the Directory" Now you need to enable Recipient Filtering. ESM/servers/Servername/Protocols/SMTP/Default SMTP Virtual Server/Properties. On the...