Run-time error '13': Type mismatch

[OldBirdman]

I have a form which is opened by with

DoCmd.OpenForm "fEditTitle", acNormal, , , , acDialog, "Key=" & txtKey

The Form Load for fEditTitle is

Private Sub Form_Load()
    Me.Filter = OpenArgs
    Me.FilterOn = True
End Sub

So far, so good. The form opens with all controls displayed correctly. When I attempt to change a textbox, the following fires

Private Sub txtTitle_Change()
    Call TidyForm(txtTitle.Text)
End Sub

and the code for TidyForm is

Private Sub TidyForm(strTitle As String)
Dim curValue As Currency
Dim wkTitle As String
    wkTitle = strTitle
    curValue = Val(wk)
    ...
End Sub

In the immediate window, I then try

?Val("12 xy")

and get the same error '13'. Much is locked because the form is modal. So I close the form, and the immediate window works, but the code, now stopped, will not get by the statement "curValue = Val(wk)". "CurValue = CInt(wk)" is OK if wk is numeric, so VBA can find the function libraries.
I've done a Compact and Repair, and a Decompile. I've done Debug->Compile and there are no errors. I changed the offending statement to assign into a Variant instead of Currency, but no good.
What is wrong?

[ADezii]

@OldBirdman
Hello OldBirdman, where is the Declaration for wk, where is it Initialized, and what does it Represent?

[OldBirdman]

Error in moving relevant code to forum. Code should have read

Private Sub TidyForm(strTitle As String) 
Dim curValue As Currency 
Dim wkTitle As String 
    wkTitle = strTitle 
    curValue = Val(wkTitle) 
    ... 
End Sub 

I always have "Option Explicit" and run Debug->Compile, so it can't be that easy an error. I'm sorry about the typo, but if that were my error, I would have expected unknown variable and not error 13.

[ADezii]

@OldBirdman
The following code will work nicely, and will always ensure that the Value typed into [txtTitle] can always be converted to Currency. You will always get a Type Mismatch on any Non-Numeric Value entered into [txtTitle], except a (.), since you are attempting to Coerce it into a Currency data Type, namely curValue.

Private Sub TidyForm(strTitle As String)
On Error GoTo Err_Tidyform
Dim curValue As Currency
 
'Will produce Type Mismatch Error (13) if the Vale contained in the Text
'Property cannot be converted to Currency
curValue = CCur(strTitle)
 
Exit_Tidyform:
  Exit Sub
 
Err_Tidyform:
  Select Case Err.Number
    Case 13     'Type Mismatch - remove entry since it cannot be
      'converted to Currency
      Me![txtTitle] = Null
    Case Else
      MsgBox Err.Description & Err.Number, vbExclamation, "Error in Tidyform()"
    End Select
      Resume Exit_Tidyform
End Sub

[OldBirdman]

I don't want to know whether txtTitle is a number, I want the value of the leading digits, if any. This is so I can sort the titles using the value of the leading numbers instead of the text.

According to Access Help, Val() always returns a number (except if argument is null). If no leading digits, a zero is returned, which I test for and change to 9999999999, so the number sort first.

Even ignoring the currency, this fails. Assignment to a Long or Variant, I get error 13.

[Lysander]

I have just run your code (in Access 2003) and it works fine if I supply strTitle with text, numeric or mixed.

Try putting a breakpoint at "wkTitle = strTitle" and see what value of strTitle is accutally getting to the Val(wkTitle) statement.

[OldBirdman]

wkTitle has the value "3 Way", the name of a movie. I know this because the code stops with error 13, and I can mouseover or ?wkTitle. Notice from my original post I tried the same function in the immediate window with the same result. ?Val("12 Dozen") gets error 13 in immediate window, but ?CInt("12.22") returns a 12, which is correct.
I suspect that the library reference is incorrect.
Typing this response, I realize that I do have a field in a table called Val. Is this my problem? It is never referenced within VBA as a variable. It does occur within query statements, within quotes, in many places in VBA. strSQL = "... ORDER BY Val, Title" for example.

[OldBirdman]

Forget it. This is the problem. I was using the name Val for a field in a table, and the error 13 / type mismatch was that Val() conflicts with rst.Val, even though Val doesn't appear outside quotes in the VBA code.

Thanks ADezii and Lysander for your time. I wouldn't have found it without your help. In typing my previous response, I started to become aware when I realized maybe a library reference was bad, but then other references were OK.

[Lysander]

Checking for a field or function called Val was my next thought.

The most common occurence of this is having a field called "Name" which is another reservered word. It would be nice if Access could enforce the reservered words and stopped us using them as variables or fields.

Glad it is all fixed now.

[OldBirdman]

Yes, and it would also be nice if this possibility were documented for this error. A Google search to try to solve this always assumes a typo or other 'stupid' error, whereas avoiding keywords is a real hassle. There are too many to remember. In this case, I should have remembered, but variables in VBA can't be keywords, but I don't remember reading that table field names can't be VBA keywords. Doesn't matter until you start using VBA, then it bytes you in the ...