ValidationRule query

[Ebeneezer]

In Access 2000 (Table Design View)I am trying to write a
Validation Rule so that only the letters A,C,G and T are
accepted not only as ACGT but in any combination and
up to say 10 sequences (40 letters) e.g -
CGATGTACCAGT etc etc.- so far without success. Can it be
done ? Any help appreciated

 

[John Vinson]

In Access 2000 (Table Design View)I am trying to write a
Validation Rule so that only the letters A,C,G and T are
accepted not only as ACGT but in any combination and
up to say 10 sequences (40 letters) e.g -
CGATGTACCAGT etc etc.- so far without success. Can it be
done ? Any help appreciated

DNA sequences, eh?

That's tough to do with a validation rule. For fixed length strings,
e.g. a triplet, you could use

LIKE [ACGT][ACGT][ACGT]

but that falls over when you have variable length strings. You may
need to leave off the validation rule on the Table and do your
validation in VBA code in the Form where these values are being
entered, unless someone else has a bright idea!

 

[John Nurick]

I haven't tested this thoroughly but it ought to work:

Validation rule for field F:
Is Null Or Not Like "*[!ACGT]*"

Validation rule for table:
(Len([F])<=40) And ((Len([F]) Mod 4)=0)

Or in a form I'd use my regular expression validation function
(http://www.mvps.org/access/modules/mdl0063.htm) with a pattern
something like
[ACGT]{,10}

In Access 2000 (Table Design View)I am trying to write a
Validation Rule so that only the letters A,C,G and T are
accepted not only as ACGT but in any combination and
up to say 10 sequences (40 letters) e.g -
CGATGTACCAGT etc etc.- so far without success. Can it be
done ? Any help appreciated

DNA sequences, eh?

That's tough to do with a validation rule. For fixed length strings,
e.g. a triplet, you could use

LIKE [ACGT][ACGT][ACGT]

but that falls over when you have variable length strings. You may
need to leave off the validation rule on the Table and do your
validation in VBA code in the Form where these values are being
entered, unless someone else has a bright idea!

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.