Converting Characters

[Dartagnan]

I have 2 tables I want to work with. Both tables have a loan number field. However, one tables loan number field has a text format and the other has a number format. When I try to run the query I receive "Type Mismatch in expression". Is there a way to convert the loan number, from either table, so the query can run?

Thank you

 

[Parra]

Why don't you just change the Loan Number field in table 1 from text to number? You do this in the table design view.

Just make a duplicate of the table, just in case.

 

[Dartagnan]

don't have that option. I did not create the table. The tables are from a database.

 

[Tanis]

Here is a function that will convert a string to a number.

David

 

[Tanis]

Sorry, forgot to post the function!!!

Public Function StrToNumber(StringIn As String) As Variant
' Accepts a string and returns any numbers

On Error GoTo ErrorProcedure

Dim posn As Integer ' char position in string
Dim str As String ' holds numbers as we find them

For posn = 1 To Len(StringIn)
If IsNumeric(Mid(StringIn, posn, 1)) Then
str = str & Mid(StringIn, posn, 1)
End If
Next posn

If Len(str) > 0 Then
'Only for testing
Debug.Print str
StrToNumber = str
'Only for testing
Debug.Print StrToNumber
End If

ExitProcedure:
Exit Function
ErrorProcedure:
MsgBox "Error in function." & Err.number & Err.Description
Resume ErrorProcedure
End Function

David