do i use validation rule or input mask for this??

[buddythebest]

I want a customer ID field which only allows initials (3 letters) or admission number (4 numbers) and do not allow a mixture of letters and numbers. What input mask or validation rul do i use for this.

i tried: (validation rule) Like "???" Or "0000" but of course it didnt work, as the ??? represents ANY three characters (text or numbers) and "0000" is only used in input mask to represent numbers.
i also tried the input mask: "LLL" or "0000" but it also doesnt work because it takes "or" as one of the field data.

please offer your help if you can,
thank you

 

[Pat Hartman]

You will need to put code in the control's BeforeUpdate event to validate it. You can't use either an input mask or a validation rule.

 

[buddythebest]

so that means i can only validate in a form, not in the table

 

[Pat Hartman]

Jet does not support triggers as SQL Server does so there is a limit to what validation can be done at the table level. You can use validation rules but they cannot reference other fields and only the functions supported by SQL are supported so most VBA functions are disallowed.

 

[buddythebest]

ok i have no clue how to do that.
I tried this but it does not really validate it... it accepts anything in the field.... it only works if the customer ID was then changed.

Private Sub tbxCustomerID_BeforeUpdate(Cancel As Integer)
Dim intLength As Integer

intLength = Len(tbxCustomerID.Value)
Select Case intLength
Case 3
tbxCustomerID.InputMask = "LLL"

Case 4
tbxCustomerID.InputMask = "0000"

End Select

End Sub

is there a function to find the datatype of text? (like there is the Len function to find the length of text)

 

[RoyVidar]

This is a quite old post, but I think this can be validated at the engine level. Try the following in the Validation rule property of the field

like "[a-z][a-z][a-z]" or like "####"

 

[RoyVidar]

Was just thinking, if you want to do it in form events, you could do pretty much the same

if me!tbxCustomerID.value like "[a-z][a-z][a-z]" or me!tbxCustomerID.value like "####" then
msgbox "yow - that's right"
else
msgbox "ouch, ouch, ouch..."
cancel = true
end if

 

[buddythebest]

ok thank you very much i'll try that


edit: Yes it wooorrkeed!!
thanks

 

[RoyVidar]

You are welcome!