If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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. ================================================== ======= |
#12
|
|||
|
|||
validation on fields with number ranges
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. |
#13
|
|||
|
|||
validation on fields with number ranges
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. ================================================== == |
#14
|
|||
|
|||
validation on fields with number ranges
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 |
#15
|
|||
|
|||
validation on fields with number ranges
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 |
#16
|
|||
|
|||
validation on fields with number ranges
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. ================================================== == |
|
Thread Tools | |
Display Modes | |
|
|