Invalid Procedure call

[dai_lo]

SELECT [TDPosting Table_CPG].[Trade Ref], MID([TDPosting Table_CPG].[Trade Ref], 1,INSTR([TDPosting Table_CPG].[Trade Ref], '|')-1)& LEFT([Book Name],7) & [Posting Currency] AS Product, Right([Trade Ref],Len([Trade Ref])-InStrRev([TDPosting Table_CPG].[Trade Ref],'|')) AS [Trade ID], [TDPosting Table_CPG].[Debit Gen# Acc Name]
FROM [TDPosting Table_CPG]
UNION ALL SELECT [TDPosting Table_CPG].[Trade Ref], MID([TDPosting Table_CPG].[Trade Ref], 1,INSTR([TDPosting Table_CPG].[Trade Ref], '|')-1)& LEFT([Book Name],7) & [Posting Currency] AS Product, Right([Trade Ref],Len([Trade Ref])-InStrRev([TDPosting Table_CPG].[Trade Ref],'|')) AS [Trade ID], [TDPosting Table_CPG].[Credit Gen# Acc Name] AS [Debit Gen# Acc Name]
FROM [TDPosting Table_CPG];



Hi

I got error: Invalid Procedure call when I execute above sql in access 2003
if I notice the problem due to -1 in INSTR([TDPosting Table_CPG].[Trade Ref], '|')-1)& LEFT([Book Name],7) & [Posting Currency] AS Product

please advise

Alice

 

[pbaldy]

Is '|' guaranteed to be in every string?

 

[dai_lo]

Is '|' guaranteed to be in every string?

not all strings contain '|'

 

[pbaldy]

Then that will give the last argument of the Mid() function a -1, and to be honest I'm not sure how it handles that. Can you try to run the query only on records that have the pipe in them, to see if this is the problem?