Text parsing in an Append Query returns Errors

[gwill23]

I am using this function

Expr2: Mid$([ItemInventoryAssemblyRefFullName],(InStrRev([ItemInventoryAssemblyRefFullName],"-")+1),(InStr([ItemInventoryAssemblyRefFullName],":"))-(InStrRev([ItemInventoryAssemblyRefFullName],"-"))-1)

to parse this string

4150-ATH:GRP_54X8/BL

into this

ATH

It works great until I get to a string that looks like this

4110-SPECIAL:OLY:LONG-TRACK:REPAIRS

Which I need to return the word SPECIAL but it gives me #Error in the Query.


It would seem that when there are multiple : it is giving the error but when there is only one it works fine. Is there anyther way to atchieve what I am doing?

I am doing this in an update query to get data from Quickbooks into a table in Access.

 

[pbaldy]

If the string you want is after the first "-", why are you using InStrRev to find it? It would seem like having two of those is the problem since you're getting the position of the last one, not the first.

 

[gwill23]

Boy howdy! That is what I get for letting people interupt me when I am programming. I copied it from another function and forgot to remove the rev.

Thanks a bunch.