Replace #Error with NULL in Mid and InStr query Access 2007

0
votes

I have written a query in Access 2007 to find the position of a string and then output said string. I have achieved this by first finding the starting position of "99" using Instr, then I use this result with Mid to extract the required string length.

The problem I have is when the result of Instr is "0" the Mid function will output #Error. I am trying to find a way to replace any instance of #Error with NULL

Sample Data:

TotalRef
111111 - 99222222 test line number 1
222222 - 94444444 test line number 2
333333 - test line number 3 99888888
444444 - test line number 4 95555555

Query:

SELECT 
    A.TotalRef,
    Mid ([A].[TotalRef], Pos,8) AS Acc,
    InStr(9,[A].[TotalRef],"99")AS Pos
FROM A;

Actual result:

TotalRef | Acc | Pos
111111 - 99222222 test line number 1 | 99222222 | 10
222222 - 94444444 test line number 2 | #Error | 0
333333 - test line number 3 99888888 | 99888888 | 29
444444 - test line number 4 95555555 | #Error | 0

Required result:

TotalRef | Acc | Pos
111111 - 99222222 test line number 1 | 99222222 | 10
222222 - 94444444 test line number 2 | NULL | 0
333333 - test line number 3 99888888 | 99888888 | 29
444444 - test line number 4 95555555 | NULL | 0

I have tried using IIF with no luck and have been unable to find a solution on here or with google. Any assistance with this problem would be greatly appreciated.

Thanks.

2
sqlms-accessms-access-2007

2 Answers

1
votes

Using IIF to exclude Pos = 0:

SELECT 
    A.TotalRef,
    iif(Pos > 0, Mid([A].[TotalRef], Pos,8), null) AS Acc,
    InStr(9, [A].[TotalRef], "99") AS Pos
FROM A;
1
votes

you almost had it, just needed a little IIF to tweak it like this

SELECT 
    A.TotalRef, 
    InStr(8,[A].[TotalRef],"99") AS Pos, 
    IIf([Pos]>0,Mid([A].[TotalRef],[Pos],8),Null) AS Acc

Ah, Alex beat me to it! :)