Results 1 to 13 of 13
  1. #1
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25

    How can a flag a value if validation rule failed.

    I am trying to append data from one table to another in Access . The destination table has some validation rule imposed in some of the columns, I want when the append query fails due to validation for some particular records then i want to copy that record to a different table and that filed should be highlight in that table so user can rectify the bad data.



    How to do this??
    Any suggestions??

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Create a query with just the primary key containing all the validation rules set in the criteria, using OR. This can be a select query, you do not need to save the data to a table.

    In your append query, bring in this query and exclude these records (a join which includes all records from your table and only those records in the query that match, then set the query's primary key criteria to IsNull).

  3. #3
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25
    Sorry i am very in Access, so can you please explain me in details i am not able understand this. For your first point.
    1.Create a query with just the primary key containing all the validation rules set in the criteria, using OR. This can be a select query, you do not need to save the data to a table.

    Suppose below is my table structure for destination table:
    Click image for larger version. 

Name:	Capture.PNG 
Views:	19 
Size:	37.2 KB 
ID:	29919

    Z_UPDT is my primary key.

    I want to impose validation rule on MRCHNT_ID, CHAIN_ID, DIV_ID and some other column also.
    suppose MRCHNT_ID validation rule should be : Like "[R-S][0-9][0-9][0-9]" or [Y-Z][0-9][0-9][0-9]
    DIV_ID validation should be :Like "[0-9][0-9][0-9]"

    and like wise then how to create the query means what should be in the criteria and what should be in the or field in the query design.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I changed my mind - better to use AND.

    Query 1 - select primary key, MRCHNT_ID, CHAIN_ID, etc - all fields with validation rules
    in first line of criteria on each field you have selected, copy and paste the validation rule from the table to the query

    Query 2 - bring in table and query 1, join on primary key, this will be your append query

    Query 3 - use the query wizard and create an unmatched query - all the records in your table that do not have a matching record in query 1 - this will be the query to use to show the user the records that have failed the validation.

  5. #5
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25
    ok i will try this, And if i want to highlight the field value for which it failed in the unmatched query records, can i do that -- because user will not be able to find out in which column value the rule failed.
    If i will be able to highlight then they can rectify that field value only.

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Try this (untested) in query 3, add a field for each validation:
    MRCHNT_IDisOK:IIf(MRCHNT_ID Like "[R-S][0-9][0-9][0-9]" or [Y-Z][0-9][0-9][0-9],True,False)

  7. #7
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25
    Quote Originally Posted by aytee111 View Post
    Try this (untested) in query 3, add a field for each validation:
    MRCHNT_IDisOK:IIf(MRCHNT_ID Like "[R-S][0-9][0-9][0-9]" or [Y-Z][0-9][0-9][0-9],True,False)
    Tried this but its not working..Can i write a vba code to read each cell value and highlight if it does not follow validation rule?

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Sure you can write code - if you are doing this from a form. You will use the BeforeUpdate event. But your first query says nothing about it being in a form and this can be done in queries. Or you can write a stand-alone routine which writes to a temporary table.

    "Does not work" tells us nothing. Please provide details.

    The validation rule as shown above does not look as tho the syntax is correct. First, test the validation rules for each field on the table. Open the table in datasheet view and test each and every scenario you have on the validation rule for each field. Once those are all correct then you can copy and paste them to a query criteria.

  9. #9
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25
    I am sorry if i did not provide proper details. I am trying to do in the easiest way. so i thought if i can highlight the cell value in the query itself if it fails then i will use in the query.So i tried the method in post#6 for one of column as

    I_DIVisOK:IIF(I_DIV Like "[0-9][0-9][0-9]",True,False) but it shows error as " The expression you entered has an invalid dot or ! operator or invalid parenthesis"

    So i thought of writing a code which can read each cell value in the Query 3(Post #4) and highlight the column value for which the validation rule failed.

    So please guide which is the best way to implement this??

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Your validation rules are not set up properly in the destination table. First get those working correctly. Whether you do this in code or in a query, it makes no difference if the syntax on the table is incorrect. The rules must be exactly the same throughout your database - on the table, in a query, on a form, in VBA, otherwise you will be knocking yourself over the head!

  11. #11
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25
    But i tried the validation rules are working fine in the table. For column which should accept 3 digits numeric value only i used Like "[0-9][0-9][0-9]" and its giving correct result.
    Is there anything wrong in this?

  12. #12
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    My instructions don't seem to be working, sorry! Let me play around with this.

  13. #13
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    This is the field in my query which is working:
    Expr1: IIf([MRCHNT_ID] Like "[R-S][0-9][0-9][0-9]" Or [MRCHNT_ID] Like "[Y-Z][0-9][0-9][0-9]",True)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 06-16-2015, 03:52 AM
  2. Validation Rule
    By Sanjo in forum Forms
    Replies: 2
    Last Post: 12-10-2013, 11:02 PM
  3. Validation Rule Help!
    By confidego in forum Access
    Replies: 8
    Last Post: 07-20-2011, 12:54 PM
  4. Validation Rule
    By robomose in forum Access
    Replies: 1
    Last Post: 09-09-2009, 05:59 AM
  5. Validation Rule
    By smitstev in forum Access
    Replies: 5
    Last Post: 06-30-2009, 09:58 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums