Validation Rule Violation

[mcalex]

Can't insert records - Validation Rules

Hi, I'm trying to insert data and am getting the following error:
'Microsoft Office Access can't append all the records in the append query.
blah, blah ... and 1 record(s) due to validation rule violations'

The problem is due to a field (that I'm not setting in the insert query) that has a Validation Rule in the table design. The values in this field are only allowed to be 2011, 2013, or <null> (as in nothing; not a 6 character string including angle brackets). When I remove the rule, the insert works fine, when it is there, the error appears.

I have 2 questions:

1) Having been bitten by table lookup fields in table designs, I'm wary of any shortcuts gained by using the features in the table design area, but setting the validation rule seemed good practise. Should I keep the rule here, or just enforce it in vba at insert time?

2) Assuming i keep the rule, what is the correct syntax to type in the Field Properties section to allow the field in question to not have any data following an insert. I've tried:

2011 Or 2013 Or Is Null
=2011 Or 2013 Or Is Null
=2011 Or =2013 Or Is Null
=(2011 Or 2013) Or Is Null

with no success

{ok 2-1/2 questions}. Following on from q2, is there a way to print the make table sql (ddl?) that would create the table so I can see the syntax generated by my fumbling in the property box?

cheers & tia
mcalex

 

[wazz]

i think the first syntax is correct.
not positive but it seems the problem is not the validation rule but the data. so yes, you have to enforce the rule at insert time by actually checking the data before attempting to insert it.

as for seeing the sql, if it's an access query, you can see the sql from design view of the query by going to view->sql. where is this query coming from?

 

[gemma-the-husky]

well if you have a field that can ONLY be 2011 or 2013 - then what do you WANT to happen if you get data that isnt either.

access will reject the entire row

are you just trying to blank the invalid data - if so, you cant use a normal import procedure to do this.

---------
what you could do (which i do) is import the data into a temporary table, and then validate the data first BEFORE adding it to the real data - ie find any data that doesnt fit - you need to be slightly careful with this because it depends how the field is defined (number or text) as you might get mismatch messages

ie testing numbers for "" will give you a mismatch

 

[mcalex]

@dave
If I get wrong data, I've got Validation Text that (i assume) will pop up as a message box. Any wrong (non-null) data will be an input error. But at the moment, I'm not putting any data in that field. I want my append query to only insert a foreign key, at this point. The rest of the data (including the field being Validated) will be populated in another process.

@wazz
Not the query sql, I want to know the sql that is created by access to make the table when i put my Validation clause attempts in the field design properties box ie, i add Field Names, Data Types and configure the properties (Default Value, Required, Indexed etc) in Table Design. Behind the scenes, Access does something like: CREATE TABLE myTableName (ID int not null, Descrip varchar(255) ...) etc etc. How do i get a look at that?

I'm looking at the temp table suggestion, because it should work, though it seems a bit kludgy in this circumstance - I do similar where i think incoming data might be suss. In this situation, however, i'm really not doing validation, i just want blank data to be accepted.

thanks again
mcalex

 

[wazz]

i just want blank data to be accepted.

do you have a default value set that fits the criteria? also, what's the data type (not sure it matters though)?

 

[mcalex]

Hi Wazz, Dave,

the default value 'set' is 2013. Basically the field represents an expiry year which is either 4 years away or 2 years away. The column data type is number (Field Size is set to Long Integer which is overkill, but won't hurt, Decimal Places is Auto and Required is No).

Going to give up guessing why it's not working and just go ahead with one of the above solutions as i think both will work (throwing the default value at the column and having the user correct it when the value is known will be easiest given ~90% of entries will be the default but the temp table method is probably more secure.

Thanks for the help

PS: would still like to know how to generate the 'Create Table' code that Access uses when I use the Table Design View, if this is possible?

cheers

 

[wazz]

if you remove the default value does the problem go away?

 

[mcalex]

Hi Wazz,

it's when i _don't_ put a value in that i get the problem. I think that if i DO insert a default value - as I thought you had suggested - then I won't get the problem.