validation on fields with number ranges

Thanks so much for your help. I'm hesitant about asking for more help because
it is simply my lack of experience that is keeping me from fully
understanding.

I think I've got the concept of what you're doing but don't really know how
to write the validation rule for it.

Lets start fresh. I now have two columns in the database ("Start#" and
"End#"). Lets assume the following values are already in that table:

Start End
5 10
15 20

Now, I want to add validation that disallows any new ranges which would
overlap as follows:
1-4 would be valid
21-25 would be valid
9-11 would not be valid
etc.

I assume I need to add the validation rule to the "Start" and "End" fields
in the table. I'm just not sure how to write it. If you can write the example
validation rule for me I'm sure I would then understand.

"Mark Han[MSFT]" wrote:

Hi Jason,

I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: .
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

On Sat, 4 Oct 2008 10:47:00 -0700, Jason wrote:

Thanks so much for your help. I'm hesitant about asking for more help because
it is simply my lack of experience that is keeping me from fully
understanding.

I think I've got the concept of what you're doing but don't really know how
to write the validation rule for it.

Lets start fresh. I now have two columns in the database ("Start#" and
"End#"). Lets assume the following values are already in that table:

Start End
5 10
15 20

Now, I want to add validation that disallows any new ranges which would
overlap as follows:
1-4 would be valid
21-25 would be valid
9-11 would not be valid
etc.

I assume I need to add the validation rule to the "Start" and "End" fields
in the table. I'm just not sure how to write it. If you can write the example
validation rule for me I'm sure I would then understand.

It is possible to have a CHECK constraint to do this, but most people would do the overlaps check in
the BeforeUpdate event of a Form. A Check constraint is part of the table definition and would work
even when values are added directly into a table. There are problems with Check constraints, in
that even after almost four versions, the Access interface has not been changed to handle them.

Here is an example just to give you the general idea:

Sub CreateConstraint()

With CurrentProject.Connection

..Execute "ALTER TABLE ProductPriceHistory ADD CONSTRAINT" & _
" Overlapping_periods_not_allowed CHECK (NOT EXISTS( SELECT *" _
& " FROM ProductPriceHistory AS P1 WHERE 1 (SELECT COUNT(*)" _
& " FROM ProductPriceHistory AS P2 WHERE P1.upc = P2.upc" _
& " AND P1.start_time = P2.end_time" _
& " AND P2.start_time = NZ(P1.end_time,'3000-01-01'))));"

End With
End Sub

Google for a newsgroup message "Check Constraint Usage" by Jamie Collins for all the details.

Hi Jason,

Thank you for the update.

To do you a favor, I write a general sample for you:

set a=21
set b=25

set A= select min(start) where start @a
set B=select max(start) where stat@a
set C=select End where start=@B

If (@a@C and @b@A)
insert ......

Hope the above is helpful

Best regards,
Mark Han
Microsoft Online Community Support
Get Secure! - www.microsoft.com/security
================================================== ==
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ==
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ==

Hi Jason,

What is this issue going on?

If there is any issue, please feel free to post back. We are very glad for
further assistance.

Have a good day!

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: .
================================================== =======
This posting is provided "AS IS" with no warranties, and co

Thanks for the reply. I'm not explaining it correctly or something because I
cant seem to put Mark's answers together to help me out. I simply want a
validation (beforeupdate on the form is fine) to ensure that I dont send out
duplicate card numbers. For instance:

I may have sent in the past:

1-10
25-50
12
14
70-80

I cant send any of these again. So, 11-24 would be valid. Any single number
between 11-24 would be valid, etc. 25-50 would not be valid, 40-50 would not
be valid, etc.

The start and end numbers will be stored in separate columns. The form will
have entries for each as well. I need the entry on the form to validate the
range being sent to all other ranges or single cards to verify no duplicates.

"Mark Han[MSFT]" wrote:

Hi Jason,

What is this issue going on?

If there is any issue, please feel free to post back. We are very glad for
further assistance.

Have a good day!

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: .
================================================== =======
This posting is provided "AS IS" with no warranties, and co

Hi Jason,

Thank you for the update.

I completely understand your concern. However, our newsgroup only focus on
broken-fix issue. For your convenience, I have provided a suggestion with
a simple example already.

The validation rule is according to your detail requirement. I am able to
give you a general suggestion. if you need assistance beyond incident based
break-fix product maintenance, such as product migration, code review, or
new program development, you may select Microsoft Advisory Services option.
Information about the types of Advisory Services available, visit the
http://support.microsoft.com/gp/advisoryservice. I appreciate your
understanding and cooperation.

Best regards,
Mark Han
Microsoft Online Community Support
Get Secure! - www.microsoft.com/security
================================================== ==
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ==
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ==