(Msg. 9) Posted: Tue Sep 30, 2008 9:08 am
Post subject: Re: validation on fields with number ranges [Login to view extended thread Info.] Archived from groups: microsoft>public>access>tablesdbdesign (more info?)
Hi Jason,
Thank you for the update.
Based on the requirement: If they enter a range which contains any of the
numbers previously issued, I want it to not enter the data in the table but
display an error message to the effect of "Card number already issued", my
suggestion could help to resolve the issue, if entrying number numericly or
not.
The reason why the example in my previous reply is to entry number
numericly is make my suggestion easy to understand.
The step to validate new records against old ones. Let me make an example
again. For example, you would like to entry 40-45 and the existing record
is
> Min_number Max_number
> 50 60
> 20 30
> 70 80
> 31 35
To get the minimumest number which is biggerer than 40. in this example,
the number is 50
To get the maximal number which is smaller than 40. in this example, the
number is 31
So we ge the following record:
> Min_number Max_number
> 50 60
> 31 35
Since 40>35 and 45<50. the number 40-45 can be entried into the database.
If you still has question on my suggestion, please let me know.
Best regards,
Mark Han
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: msdnmg DeleteThis @microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
(Msg. 10) Posted: Fri Oct 03, 2008 11:05 am
Post subject: Re: validation on fields with number ranges [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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: msdnmg DeleteThis @microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
(Msg. 11) Posted: Sat Oct 04, 2008 10:47 am
Post subject: Re: validation on fields with number ranges [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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: msdnmg.RemoveThis@microsoft.com.
> =========================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
>
>
(Msg. 12) Posted: Sun Oct 05, 2008 8:31 am
Post subject: Re: validation on fields with number ranges [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
On Sat, 4 Oct 2008 10:47:00 -0700, Jason <Jason DeleteThis @discussions.microsoft.com> 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.
(Msg. 13) Posted: Mon Oct 06, 2008 10:16 am
Post subject: Re: validation on fields with number ranges [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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.
====================================================
(Msg. 14) Posted: Thu Oct 09, 2008 6:52 am
Post subject: Re: validation on fields with number ranges [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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: msdnmg DeleteThis @microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and co
(Msg. 15) Posted: Fri Oct 10, 2008 3:13 pm
Post subject: Re: validation on fields with number ranges [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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: msdnmg.DeleteThis@microsoft.com.
> =========================================================
> This posting is provided "AS IS" with no warranties, and co
>
>
(Msg. 16) Posted: Tue Oct 14, 2008 11:27 am
Post subject: Re: validation on fields with number ranges [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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.
====================================================
All times are: Eastern Time (US & Canada) (change) Goto page Previous1, 2
Page 2 of 2
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office Other