Validation Rule Help

[Guest]

I am trying to create a formula for validation Value of a field called
sngPatientAccount. It is a number(single) field. The number can be a length
of 4-10 but those of length 9 or 10 must begin with a 3. Here is what I am
using and it is not working.

((Len([lngzPatientNumber]) Between 9 And 10 And
Left([lngzPatientNumber],1)=3) Or (Len([lngzPatientNumber]) Between 4 And 8))

Can anyone help me figure out what I am doing wrong?

 

[Jeff Boyce]

Mary

Single data type is a "decimal" number. Are you planning to do math with
the PatientAccount value? If you are only using a series of characters that
happen to be digits as an identifier, then don't use the number data types.
Instead, use the "text" data type.

Next, where are you using this? If you are trying to do this in the
Validation Rule of the table, have you tried using the IIF() function? I'd
recommend that for a more-complex validation rule like this, that you use
the BeforeUpdate event of a form, and do this in a form, not directly in the
table.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[missinglinq via AccessMonster.com]

I think you need to start over again remembering that you should only use
numerical datatypes (long, single, double) if you're going to be performing
calculations with the data. ID Numbers, Account Numbers and so forth should
always be text, even if they are comprised of all "numbers!" And the Len
function returns the number of characters in a string. I think if you redo
your table along these lines you'll find that things will go much easier.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com