How to debug validation rule failure?

[zkarj]

I have an append query which fails due to validation rules. There are no field validation rules, only enforced table relationships. There is an autonumber field which I am omitting from the INSERT and SELECT clauses in the hopes that it will take its next value.

How do I determine which fields are causing the problem? I have changed the query to a simple SELECT and manually typed in the resulting values with no problems. Note that ALL records I am attempting to add fail the rules.

 

[QTNM]

Naturally the Select statement would work ... the integrity rules do not apply if you are only view data already resident in the table.

How many fields are you trying to update ??

It's a little pain in the arse, but I would create a copy of the query, remove all but the primary key that you are updating ... then one by one add your new query item until you find the culprit.

That's my suggestion .... good luck!

QT

 

[zkarj]

Thanks QTNM.

That's pretty much what I did. The offending field was "". I replaced it with ' ' and it works. The "Allow zero length" property is the default "No".

I'm still annoyed that Access won't tell you which fields are in error. It either needs to give better errors or get some real help text. Does anybody else think the help text in Access is more akin to a cryptic crossword? It is stuff all use for anyone who knows what a database should be able to do. I can't even locate a list of functions to use in the SQL statements.

 

[QTNM]

All Good .....

Does anybody else think the help text in Access is more akin to a cryptic crossword? It is stuff all use for anyone who knows what a database should be able to do. I can't even locate a list of functions to use in the SQL statements.

HAhah ah ahaha ! I agree 110% !!!! Out of school I started working on some make shift db's and I tell ya ... I was PISSED at the MS's mediocre attempt at content help.

This is why I found SysOpt.com ( I use mostly for tech ) and AWF.com ( founded recently via Google ) ..... it's a great community to share ideas and allow some of us programmers to sleep well at night with out queries floating around with the sheep in our heads!

Cheers to having a good nights rest !

QT