Invalid Procedure Call error on foreign key expression. (1 Viewer)

[brewpedals]

Hi,

I'm attempting to create a foreign key field in a sub-query by using Left() and Instr() to parse the text of my key field and return the characters prior to the second "-".

Example: 002-C100-4569 ---> 002-C100

When I build a second query with a relationship between the foreign key field previously mentioned with the key field in an associated query, I get the error "Invalid Procedure Call"

Here is my foreign key generator string:

CirNo: Left([tbl_IntExt].[ASSETID],InStr(5,[tbl_IntExt].[ASSETID],"-",1)-1)

The query data breifly displays, followed by the error messge, then all query fields display "#Name"

The overall goal is to create a query to update the values in 002-C100-4569 (Child) with values from 002-C100 (Parent).

Any help or advise is greatly appreciated.

Thanks,
brewpedals

 

[boblarson]

In your INSTR you don't have a field to compare with the "-". You have a number 1.

 

[brewpedals]

Thanks for the quick reply Bob.

My first string is the contents of [tbl_IntExt].[ASSETID]
My second string is hard to see, it is a dash "-"
The one (1) is to return text, 0 is default for binary.

CirNo: Left([tbl_IntExt].[ASSETID],InStr(5,[tbl_IntExt].[ASSETID],[B]"-"[/B],1)-1)

 

[boblarson]

Thanks for the quick reply Bob.

My first string is the contents of [tbl_IntExt].[ASSETID]
My second string is hard to see, it is a dash "-"
The one (1) is to return text, 0 is default for binary.

CirNo: Left([tbl_IntExt].[ASSETID],InStr(5,[tbl_IntExt].[ASSETID],[B]"-"[/B],1)-1)

Then I think you have the text reversed. The first one should be the "-" and THEN it is the field to look in (ASSETID)

 

[brewpedals]

Thanks again Bob,

But when I swapped them like this:

CirNo: Left([tbl_IntExt].[ASSETID],InStr(5,"-",[tbl_IntExt].[ASSETID],2)-1)

I get "#Error" in the datasheet view.

I've also tried Null, 0,1 & 2 for the compare flag with no positive results.

It's a pesky little problem.

Rick.

 

[WayneRyan]

Rick,

I'd say the problem is that *sometimes* the InStr returns a 0 when looking
for the "-" after column 5. Then, you'll be trying to take the left -1
characters from a string. You can't do that.

At least one of your fields fails on the InStr function call.

Wayne

 

[brewpedals]

Ding Ding Ding Ding - We have a winner!

That was it Wayne! Thanks. I'll account for those exceptions in my expression.

Thanks for your help too Bob!

 

[boblarson]

Ding Ding Ding Ding - We have a winner!

That was it Wayne! Thanks. I'll account for those exceptions in my expression.

Thanks for your help too Bob!

Glad that between the two of us we could get you on the right track.