Function returns Datatype Mismatch

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am attempting to split a part an account code to extract part of it, and
some great people here have given me some code to aid in this endeavor. (I
have now read a few chapters about Visual Basic), however I am encountering
the following error:

Run-time Error '13':
Type Mismatch

The Accounting_Code in my table has a Text datatype.

VB Procedure:

Public Function Extract(Account_Code As String) As String
Dim Tokens() As String
Tokens = Split(Account_Code, ".", -1)
Extract = Mid(Tokens, 4, 4)
End Function

Update Query:

UPDATE SUB_1 SET SUB_1.Clearing_Account = Extract([Clearing_Account]);

Visual Basic Editor is highlighting the "Extract = Mid(Tokens, 4, 4)" line
of code from my procedure, but as far as I know everything is setup as text
so there should not be a problem. ???

Thanks for your time,
David
 
davidg2356 said:
Hi,

I am attempting to split a part an account code to extract part of
it, and some great people here have given me some code to aid in this
endeavor. (I have now read a few chapters about Visual Basic),
however I am encountering the following error:

Run-time Error '13':
Type Mismatch

The Accounting_Code in my table has a Text datatype.

VB Procedure:

Public Function Extract(Account_Code As String) As String
Dim Tokens() As String
Tokens = Split(Account_Code, ".", -1)
Extract = Mid(Tokens, 4, 4)
End Function

Update Query:

UPDATE SUB_1 SET SUB_1.Clearing_Account = Extract([Clearing_Account]);

Visual Basic Editor is highlighting the "Extract = Mid(Tokens, 4, 4)"
line of code from my procedure, but as far as I know everything is
setup as text so there should not be a problem. ???

Thanks for your time,
David
Click to expand...

Your code doesn't quite make sense. Tokens is an array of strings,
which you are creating by splitting Account_Code on the "." character.
Then you're trying to use the Mid function on the array itself -- but
Mid() requires a string argument, not an array argument. Which part of
Account_Code do you want? If you want the fourth element, you would
just right

Extract = Tokens(3)

because the array is zero-based. Suppose you had an Account_Code of
"A.B.C.D.E". Then

Tokens(0) yields "A"
Tokens(1) yields "B"
Tokens(2) yields "C"
Tokens(3) yields "D"
Tokens(4) yields "E"

If that explanation doesn't help, please tell us what you actually want
Extract to return.
 
Your problem is that Tokens is an array. You are treating it like text.
Tokens will contain as many elemets as groups of characters between your
delimiter. For example, assume account_code contains "abc.123.xzy.AaA.???"
then Tokens will contain 5 elements. To determine how many elements, use
z = Ubound(tokens)
In this case (assuming you are using option base 0) it will return 4. Then
you need to address each element of Tokens like:

x=Tokens(0)
x will then contain "abc"
y=Tokens(4)
y will contain "???"
 
It's not clear in your message if you are working in VBA or VB, and in
either case, I have to assume that the update query is being sent over as a
string. If all that is true, then if you look at the query you are saying
essentially this (assuming Extract returns the string "1234"):

UPDATE SUB_1
SET SUB_1.Clearing_Account = 1234

You need to put quotes around the <1234>. So do this:

"UPDATE SUB_1 " + _
"SET SUB_1.Clearing_Account = " + """" + Extract([Clearing_Account]) + """"




--
Dominic Olivastro
ipIQ, Inc.

web: http://www.ipIQ.com
fax: 1-856-546-9633
voice: 1-856-546-0600 (ext 224)
email: (e-mail address removed)
 
Thanks for your help that works great.

Sincerely,
David Greer

Dirk Goldgar said:
davidg2356 said:
Hi,

I am attempting to split a part an account code to extract part of
it, and some great people here have given me some code to aid in this
endeavor. (I have now read a few chapters about Visual Basic),
however I am encountering the following error:

Run-time Error '13':
Type Mismatch

The Accounting_Code in my table has a Text datatype.

VB Procedure:

Public Function Extract(Account_Code As String) As String
Dim Tokens() As String
Tokens = Split(Account_Code, ".", -1)
Extract = Mid(Tokens, 4, 4)
End Function

Update Query:

UPDATE SUB_1 SET SUB_1.Clearing_Account = Extract([Clearing_Account]);

Visual Basic Editor is highlighting the "Extract = Mid(Tokens, 4, 4)"
line of code from my procedure, but as far as I know everything is
setup as text so there should not be a problem. ???

Thanks for your time,
David
Click to expand...

Your code doesn't quite make sense. Tokens is an array of strings,
which you are creating by splitting Account_Code on the "." character.
Then you're trying to use the Mid function on the array itself -- but
Mid() requires a string argument, not an array argument. Which part of
Account_Code do you want? If you want the fourth element, you would
just right

Extract = Tokens(3)

because the array is zero-based. Suppose you had an Account_Code of
"A.B.C.D.E". Then

Tokens(0) yields "A"
Tokens(1) yields "B"
Tokens(2) yields "C"
Tokens(3) yields "D"
Tokens(4) yields "E"

If that explanation doesn't help, please tell us what you actually want
Extract to return.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top