Algebraic Validation

[Mac]

Dear All,

Is it possible to have a validation rule for a table field that will
only allow a number that is a multiple of something Eg: ((n)*50)+1
where n would be any value >=0?

Thanks in advance.

Andy

 

[Jeff Boyce]

Andy

To do something like that, consider using a form, and the BeforeUpdate event
for that form. I think you'll find you have much more control working in
the form than trying to use table/field validation.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tim Ferguson]

Is it possible to have a validation rule for a table field that will
only allow a number that is a multiple of something Eg: ((n)*50)+1
where n would be any value >=0?

Where I come from this is O-level maths...

([MyField] IS NULL) OR
(([MyField] MOD 50) = 1)


Some of those brackets might be strictly unneccessary, but I can never
remember operator priority these days


Hope it helps


Tim F

 

[John Nurick]

Hi Andy,

In ordinary Access circumstances, use the Table's validation rule and
not the field's. An expression like this should do the job:

([FieldName] Mod 50=0) Or [FieldName]] Is Null

If you enjoy a challenge, look up the SQL CONSTRAINT syntax.

 

[Jamie Collins]

Is it possible to have a validation rule for a table field that will
only allow a number that is a multiple of something Eg: ((n)*50)+1
where n would be any value >=0?

Where I come from this is O-level maths...

([MyField] IS NULL) OR
(([MyField] MOD 50) = 1)

Tim, my O Level maths tells me minus 49 will erroneously pass your
rule.

Some of those brackets might be strictly unnecessary

The test for NULL values is unnecessary because of the inherent
behaviour of NULLs in SQL DDL: a NULL cannot be 'known' to fail a rule
therefore it is allowed to pass. This is convenient because we can let
the NOT NULL declaration (or its absence) on a column handle NULLs in
SQL DDL.

Jamie.

--

 

[Mac]

Dear All,

Thanks for the pointers, however it doesn't seem to be working, I have
a form attahced to the table with the table validation (([StartNumber]
Mod 50=1) Or [startnumber] Is Null). There is also a similar validation
(=([Startnumber] Mod 50=1)) in the validation for the startnumber field
on the form. The problem is that instead of accepting a value of say
6801, I'm getting my validation text (suggesting that I'm doing
something wrong somewhere). My understanding of the argument is that
for any number where the remainder after division by 50 is not 1, is a
fail. Am I correct in this interpretation and if so where am I going
wrong?

Regards

Andy

 

[Tim Ferguson]

I have
a form attahced to the table with the table validation (([StartNumber]
Mod 50=1) Or [startnumber] Is Null). There is also a similar validation
(=([Startnumber] Mod 50=1)) in the validation for the startnumber field
on the form.

1) move the validation rule from the Table to the Field

2) remove the validation rule from the form control; it's unneccessary and
if you want to do things to the user, the BeforeUpdate event gives you far
more options

3) what happens when you enter 6801 into the table directly? Is this a form
problem or a db engine problem?

4) just reassure us... StartNumber is a numeric field, not char?<g>


Hope that helps


Tim F

 

[Mac]

Dear Tim,

I'd just figured out the problem when you posted, thanks anyway. I've
removed the validation from the form and left it in the table, it now
works, all I need to figure out is how to override the error message
that access provides with incorrect data with my own validation text
(it's ignoring anything put in the form validation). As for your
question, if I enter 6801 directly to the table it works, if I enter
6802 I get an access error message telling me that it contravenes the
field validation. As for the startnumber field, yes it is a (double)
numeric field.

Regards

Andy

 

[Tim Ferguson]

all I need to figure out is how to override the error message
that access provides with incorrect data with my own validation text
(it's ignoring anything put in the form validation).

Use the OnBeforeUpdate event:

private sub txtStartNumber_BeforeUpdate(cancel as integer)

dim errorMessage as string

if isNull(txtStartNumber.Value) then
' missing values, no good
errorMessage = "Please make sure you fill in a number!"

elseif not isnumeric(txtStartNumber.Value) then
' have to be a decent number
errorMessage = "Doh! Please make it a proper number."

elseif (cInt(txtStartNumber.Value) mod 50)<> 1 Then
errorMessage = "It must be something times fifty plus one"

else
' must be okay
errorMessage = ""

end if

if len(errorMessage) > 0 Then
' display the error
msgbox errorMessage, vbExclamation, "Start Number error"
' and refuse the update
Cancel = True

else
' this is unneccessary, but you see the point
Cancel = False

End if
End Sub

As for the startnumber field, yes it is a (double)
numeric field.

Ugh! Why not an integer or a long? There is a chance that 6801 can
6800.99998 depending on where the number comes from. Integers are smaller
and faster too.


Hope that helps


Tim F

 

[Mac]

Dear Tim,

Many thanks for the script, unfortunately the number format is an
external constraint, but I may try to ignore it if I can.

Regards

Andy