Invalid Procedure Call - Trying to join using Mid and Instr

[bravura]

tblA has commodities such as ME Milled Rice, and tblB has just ME and second field with load factor

=Mid(tblA.Commodity, 1, InStr(1, " ", tblA.Commodity) - 1) = tblB.Commodity

[TABLE="width: 260"]
<tbody>[TR]
[TD]tblA[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]field1[/TD]
[TD]field2[/TD]
[TD]Commodity[/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD]123[/TD]
[TD]MR - Milled Rice[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]tblB[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Commodity[/TD]
[TD]LF[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MR[/TD]
[TD]22[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[xenou]

Looks like you have the arguments to instr() in the wrong order: it should be (1, searched, sought). So ....

=

Mid(tblA.Commodity, 1, InStr(1, tblA.Commodity, " ") - 1) = tblB.Commodity

 

[Micron]

not much here to go on, but one thing I'm pretty sure of is that you'll get that message if you try to pass a negative value to the Mid function. This could occur if the Instr function doesn't find the character that you're searching for, because you are subtracting 1 regardless. Are you certain the error occurs with the data you've shown?