Table Validation Rule for a Field Name

  • Thread starter Thread starter yourmomentofzen
  • Start date Start date
Y

yourmomentofzen

Hi all-

Maybe i'm just a little lousy with this, but i'm having an issue with
the validation rule.

right now i have a field entitled "rainincr" for rain increment and
i'm trying to do a range check, because occasionally there'll be
glitches where it shows the rain at 100" for the day and of course
that's not right, since we don't get monsoons in florida. so in the
validation rule in the properties menu, i put <=10 Or Is Null" but
when i run the table, it still shows up with values such as 30 and
what not still.

any ideas? thanks in advance.

-ymoz-
 
Validation rules run on new data. You'll have to fix any existing problems.

Is the field a number or text datatype? If text, all bets are off.

Are you talking about a table or query here?
 
Validation rules run on new data. You'll have to fix any existing problems.

Is the field a number or text datatype? If text, all bets are off.

Are you talking about a table or query here?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.









- Show quoted text -
Click to expand...

It's a table, and the fields are numbers.
 
Hi,

Using Access 2003 I created a similar validation rule in a table. When I
saved the table design change, Access checked the Data Integrity rules. It
found data that didn't meet the validation rule but I said to continue. It
did not delete any existing records nor change any data. In other words,
Access did not mess with existing data.

However when I tried to enter a new record that did not meet the validation
rule, it wouldn't let me save the record. This is how Access works. It will
keep existing data that doesn't meet the validation rule; however, it will
not allow any new records nor updates to existing records that doesn't match
the validation rule.

If this is different than what you experienced, I'd like to know more.
 
Using Access 2003 I created a similarvalidationrule in a table. When I
saved the table design change, Access checked the Data Integrity rules. It
found data that didn't meet the validation rule but I said to continue. It
did not delete any existing records nor change any data. In other words,
Access did not mess with existing data.

However when I tried to enter a new record that did not meet thevalidation
rule, it wouldn't let me save the record. This is how Access works. It will
keep existing data that doesn't meet the validation rule; however, it will
not allow any new records nor updates to existing records that doesn't match
the validation rule.

If this is different than what you experienced, I'd like to know more.
Click to expand...

You used the Access user interface. FWIW the Jet/ACE engine seems to
behave differently i.e. exising rows are not checked against
Validation Rules when a row is inserted e.g.

Sub TestInvalid()
Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE Test (" & _
"col1 INTEGER, " & _
"col2 INTEGER);"

' Add data that will be FALSE when
' tested against forthcoming Validation
' Rules
.Execute _
"INSERT INTO Test VALUES (55, 99);"

End With

' Create Validation Rules
Dim jeng
Set jeng = CreateObject("JRO.JetEngine")
jeng.RefreshCache .ActiveConnection

.Tables("Test").Columns("col1") _
.Properties("Jet OLEDB:Column Validation Rule") _
.value = _
"= 1"

' This succeeds i.e. no checking of existing
' data for Column Validation Rule
.ActiveConnection.Execute _
"INSERT INTO Test VALUES (1, 2);"

.Tables("Test") _
.Properties("Jet OLEDB:Table Validation Rule") _
.value = _
"col1 + col2 = 3"

jeng.RefreshCache .ActiveConnection

' This succeeds i.e. no checking of existing
' data for Table Validation Rule
.ActiveConnection.Execute _
"INSERT INTO Test VALUES (1, 2);"

' This fails, just to prove the
' rules actually work ;-)
.ActiveConnection.Execute _
"INSERT INTO Test VALUES (1, 99);"

Set .ActiveConnection = Nothing

End With
End Sub

Jamie.

--
 
Back
Top