Avatar of LoveToSpod
Flag for United Kingdom of Great Britain and Northern Ireland

asked on 

Using FIND in Query Expression

Hi there!

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.

Cheers, LoveToSpod
Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon