CODEWITHSUNDEEP
ms-accessvba
user2204315
user2204315

Reputation:

Left Function Returns 'Type Mismatch'

I have a database containing several tables which have fields to record phone numbers. Their data type is Short Text, allow zero length - No. I have set up Input Masks as follows:

If Left(Phone, 3) = "011" Or Left(Phone, 3) = "030" Then
    Me.Phone.InputMask = "0000 000 0000;;' '"
ElseIf Left(Phone, 3) = "020" Or Left(Phone, 4) = "0121" Then
    Me.Phone.InputMask = "000 0000 0000;;' '"
ElseIf Left(Phone, 4) = "0191" Then
    Me.Phone.InputMask = "000 000 0000;;' '"
Else
    Me.Phone.InputMask = "00000 000000;;' '"
End If

On all tables except one this works fine. But, for one table I get an error message 'Run-time error 13 Type Mismatch'. What is even more strange is that the input mask works fine if placed in an unbound control on the same form, e.g. data = 'Left(Phone,3)'.

Upvotes: 1

Views: 1357

Answers (2)

Andre
Andre

Reputation: 27634

Have you tried using a string variable for the Left function?

Dim strPhone As String
strPhone = Nz(Me!Phone, "")

If Left(strPhone, 3) = "011" Or Left(strPhone, 3) = "030" Then
    Me!Phone.InputMask = "0000 000 0000;;' '"
ElseIf Left(strPhone, 3) = ' etc.

Solution:

It turned out there was a text field called Left on the form, which caused this odd error.

If renaming the field is impractical, you can avoid the error by using the qualifier VBA:

If VBA.Left(strPhone, 3) = "011" Then

but not using reserved words for field names is better. :)

Upvotes: 1

Gustav
Gustav

Reputation: 55841

[Phone] can be Null, so apply Nz:

If Left(Nz(Phone), 3) = "011" Or Left(Nz(Phone), 3) = "030" Then
' etc.

Upvotes: 0

Related Questions