Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validation rule / Append Query (Access '97)

    I have an append query where the data is appended from a TABLE 1 to TABLE 2 only if the value is above 25,000. I keep breaking a validation rule.

    I changed the query to a 'make table' query and everything was fine. Then I changed the query back to an 'append' query and it actually worked.

    I then run a macro for which the append query is part of and my validation error returned.

    I have checked the design of the tables and there aren't any validation rules, required fields are set to 'No' and i'm just absolutely stumped - there aren't any null fields either.

    Help please!!!

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation rule / Append Query (Access '97)

    Did you look at the text fields to make sure the the Allow Zero length property isn't set to No?
    Are you certain that the keys or any unique indexes are not duplicated?

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation rule / Append Query (Access '97)

    The table is now fine for some unknown reason BUT i know wish to add another field, which is defined as the current data (Fieldname=OS) less the prior month's data (Fieldname=PREV_OS).

    Having added the new field to the table and the append query, my query again breaks the validation rule. The data is numeric & there aren't any validation rules set.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation rule / Append Query (Access '97)

    I realize that this may not be the answer you were looking for, but because the value of the new field can be calculated in a subsequent query, I would not recommend storing the calculated value in a table. Isn't it possible to use the table "as is" and run a query when you need to see your new field? In my experience, this is the way calculated data is typically handled.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation rule / Append Query (Access '97)

    Unfortunately the reason for having this new field is to add further criteria to the append query.

    At the moment the data only gets appended if any of the following are met:

    Field Value

    PTT >25,000
    INC >250,000
    MOVE >25,000

    The additional field/criteria I wish to add are:

    OS_MOVE >25,000

    I copied the field MOVE (within the append query) for this as that is also a calculated field in the same manner as I wish OS_MOVE to be.

    I can't add the field after the append query as OS_MOVE will for part of the criteria for appending data in the 1st place.

    Hope this has brought a bit more clarity....

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation rule / Append Query (Access '97)

    OK - now I understand that this is a criteria of the append query. If the first field you spoke of worked and this is the same type of logic, there must be something different about these fields which causes the second one to fail. What did you do to make the initial one work? And what do you mean by "I copied the field MOVE ..." - are you referring to actually copying the field to the query or the table? Sorry to ask yet more questions ... maybe someone else can see this more clearly.

  7. #7
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation rule / Append Query (Access '97)

    The table that data is appended FROM is called DAYCLAIM.
    The table that data is appended TO is called WEEKREP.
    The append query is called QA_WEEK.

    The Field 'MOVE' exists in WEEKREP but not DAYCLAIM. It is defined as:
    [dayclaim]![inc]-[dayclaim]![inc_prev]

    In the table definition of WEEKREP, I copied MOVE and pasted it on the end as OS_MOVE.

    In QA_WEEK, I copied the calculated field MOVE and pasted it at the end of the query as OS_MOVE, changing the expresion to: [dayclaim]![os]-[dayclaim]![os_prev].

    PS
    There are "IIF(IS NULL's" wrapped around the formulas to cater for null data.

    Hope this helps to clarify!!!

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Validation rule / Append Query (Access '97)

    It sounds as if you've gotten stumped at this stage, so let me ask a few questions:
    1 - what is the actual message you are getting when the append query fails - specifically is it mentioning a validation rule, or is it more generic?
    2 - do you have indexes on the WEEKREP table that are indicated as unqiue and could be giving you a failure - that is a most frequent cause for append queries failing?
    3 - would it be possible to do this as an update query on WEEKREP if the record already existed - otherwise you would do an append so it did exist?

    Hopefully this will get some thinking caps churning!
    Wendell

  9. #9
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation rule / Append Query (Access '97)

    1. It specifically states that records weren't appended due to a validation rule violation.
    2. Just rechecked & there aren't any indexes.
    3. Yes this could be done as un apdate if the record existed in weekrep but OS_MOVE > 25,000 could be the only reason that the record would get appended if all the other criteria aren't met.

    I'm thinking of changing the query to a 'Make Table' with OS_MOVE added, then changing the query back to an append - just to see what happens.

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Validation rule / Append Query (Access '97)

    Is it possible that you could end up with a duplicate record in WEEKREP because you already had appended a record in a previous process? That's what was behind my question 3. Just to be sure, do you have a primary key for table WEEKREP?

    Another possibility is that your database is actually corrupt and thus giving you a bogus error message - to test that theory, create a new empty database and then import all of the objects from your existing database. And if changing the query to a Make Table worked before, then try it again. That process actually deletes the old table, assuming it already exists, and then replaces it with a brand new one. That actually led to my theory about the existance of a duplicate record. Isn't technology fun?
    Wendell

  11. #11
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation rule / Append Query (Access '97)

    Wendell,

    When testing, I always make sure that the table WEEKREP is empty at the start & there aren't any primary keys.

    When I've tested the make table theory I'll let you know what happened - but right now, my boss awaits me!!

    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •