By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,474 Members | 1,514 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,474 IT Pros & Developers. It's quick & easy.

Run-time error '13': Type mismatch

100+
P: 675
I have a form which is opened by with
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "fEditTitle", acNormal, , , , acDialog, "Key=" & txtKey
The Form Load for fEditTitle is
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     Me.Filter = OpenArgs
  3.     Me.FilterOn = True
  4. End Sub
So far, so good. The form opens with all controls displayed correctly. When I attempt to change a textbox, the following fires
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtTitle_Change()
  2.     Call TidyForm(txtTitle.Text)
  3. End Sub
and the code for TidyForm is
Expand|Select|Wrap|Line Numbers
  1. Private Sub TidyForm(strTitle As String)
  2. Dim curValue As Currency
  3. Dim wkTitle As String
  4.     wkTitle = strTitle
  5.     curValue = Val(wk)
  6.     ...
  7. End Sub
In the immediate window, I then try
Expand|Select|Wrap|Line Numbers
  1. ?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?
Jul 25 '09 #1
Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,679
@OldBirdman
Hello OldBirdman, where is the Declaration for wk, where is it Initialized, and what does it Represent?
Jul 25 '09 #2

100+
P: 675
Error in moving relevant code to forum. Code should have read
Expand|Select|Wrap|Line Numbers
  1. Private Sub TidyForm(strTitle As String) 
  2. Dim curValue As Currency 
  3. Dim wkTitle As String 
  4.     wkTitle = strTitle 
  5.     curValue = Val(wkTitle) 
  6.     ... 
  7. 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.
Jul 26 '09 #3

ADezii
Expert 5K+
P: 8,679
@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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub TidyForm(strTitle As String)
  2. On Error GoTo Err_Tidyform
  3. Dim curValue As Currency
  4.  
  5. 'Will produce Type Mismatch Error (13) if the Vale contained in the Text
  6. 'Property cannot be converted to Currency
  7. curValue = CCur(strTitle)
  8.  
  9. Exit_Tidyform:
  10.   Exit Sub
  11.  
  12. Err_Tidyform:
  13.   Select Case Err.Number
  14.     Case 13     'Type Mismatch - remove entry since it cannot be
  15.       'converted to Currency
  16.       Me![txtTitle] = Null
  17.     Case Else
  18.       MsgBox Err.Description & Err.Number, vbExclamation, "Error in Tidyform()"
  19.     End Select
  20.       Resume Exit_Tidyform
  21. End Sub
Jul 26 '09 #4

100+
P: 675
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.
Jul 27 '09 #5

Expert 100+
P: 344
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.
Jul 27 '09 #6

100+
P: 675
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.
Jul 27 '09 #7

100+
P: 675
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.
Jul 27 '09 #8

Expert 100+
P: 344
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.
Jul 27 '09 #9

100+
P: 675
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 ...
Jul 27 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.