I am trying to write an expression in a MS Access query that of returns the text to the right of "prod/" in the field value. If the field value is: "location/server/prod/script12=s" then I need it to return, (in this case): "script12=s". Note the length of the string to the right of "Prod" can vary, hence I'm not using "Right([DepDesc],10)"
I can achieve the same desired result in MS Excel using: RIGHT(B4,LEN(B4)-FIND("prod/",B4)) - but this is not Excel!
I have tried using: DepDesc: Right([DepDesc],Len([DepDesc])-FIND("iw",[DepDesc])) - but doesn;t recognise FIND
I have tried using: DepDesc: Right([DepDesc],Len([DepDesc])-InStr("iw",[DepDesc])) - but says the expression contains invalid syntax
Further to the above I need the expression to search for the existence of "prod/" in the field value. If it does exist then return the value to the right of "prod/" (as above), else just return the field value as is.