Using validation rule to control input strings in form

[Jimbo2112]

Hi All,

I am a complete newbie at MS Access (2003) and I am creating a database. I want to control what people enter into the forms to ensure accurate data. So, these are the conditions I need to build into my form entry:

* ISBN entry needs to be either 0 10 or 13 numbers in length

I have tried using the validation rule in the form field properties and build a condition. It looks like this so far:

=((Len([Print ISBN])=10) Or (Len([Print ISBN])=0) Or (Len([Print ISBN])=13))

Do I need all the paranthesis? The error I am getting is that it always thinks there are not the correct amount of chars unless I put a zero in there. The cell is formatted as a general number cell.

Cheers!

Jimbo

 

[Remou]

ISBNs have a check digit, so if you are anxious to ensure accuracy, you should examine the check digit.

http://en.wikipedia.org/wiki/International_Standard_Book_Number

 

[Crowley16]

I generally use code to do validation, purely because it lets me control the behaviour more and is easier to do more complicated validation...

e.g. in the afterUpdate event, I'd put something like:

if Len([Print ISBN]) <> 0 OR ... then
msgbox "ISBN number not the right length"
'[Print ISBN] = ""
[Print ISBN].setfocus
exit sub
endif

--------------------
Procrastinate Now!

 

[Jimbo2112]

Thanks for the responses guys ...

Crowley, can you tell me if my code should work where it is? Currently it does not! In the mean time I will use your code in the afterUpdate event

Cheers

Jimbo

 

[Crowley16]

what you have should work, unless you've got nulls, can the isbn be empty?

if so, you'll need to deal with nulls, maybe use nz()

--------------------
Procrastinate Now!

 

[Remou]

I strongly recommend using check digits when they are available.

It is best not to have spaces in field names; it is best to use a coherent naming system for controls on forms.

Null is not the same as an empty string.

intISBN = Len(Trim(PrintISBN & ""))
If intISBN = 0 Or intISBN = 10 Or intISBN = 13 Then
    Debug.Print "Ok"
Else
    Debug.Print "Not Ok"
End If

 

[Jimbo2112]

Remou,

Can I put this code into the Validation Text field via the expression builder? I tried this and it did not like the returns in the code.

Or should I be putting the code in a different place?

Jimbo

 

[Remou]

Put it in the Before Update event of the relevant control.

 

[Jimbo2112]

Thanks for the help guys ...

Jimbo

 

[Crowley16]

if you use the beforeUpdate event, don't you have to use the .text property of the control?

--------------------
Procrastinate Now!