Dual Format or Input Mask???

[carlisle]

Hi,

I am creating a database where the serialID can be a number of different 'formats'. TBH, I don't know if this can even be done in the first place, but on the off chance that it can....

Firstly, i'm not sure whether to use Input Mask or Format...

But the serial may be '1234567AB', '12345678AB' or '12AB1234AB'.

Or to put another way

'7 numbers 2 letters',

'8 numbers 2 letters'

or '2 numbers, 2 letters, 4 numbers, 2 letters'

I have tried creating a mask such as '00LL0000LL' (can't remember exactly, had some '9's in there too some where...) but just keep hitting a brick wall, whereby it not satisfying all of the types.

Any help much appreciated, Ben

 

[cuttsy]

Regular Expression

I can't help on the Input mask front but this function will return True if the SerialID that you pass is in the correct format.

Public Function ValidateSerial(ByVal sSerial As String) As Boolean

    Dim oRegExp As Object
    
    If (oRegExp Is Nothing) Then
        Set oRegExp = CreateObject("vbscript.regexp")
    End If
    
    oRegExp.Pattern = "^([0-9]{2}[A-Z]{2}[0-9]{4}[A-Z]{2}|([0-9]{7}|[0-9]{8})[A-Z]{2})$"
    
    ValCode = oRegExp.test(sSerial)
    
End Function

Just Call the function in your form.

If ValidateSerial(txtSerialID) Then
    'Valid Format
Else
    'Invalid Format
End if