IIF & IsError in combination always give me "#Error"

[iPad]

Hi all

I want to analyse a column of a query with the following function:

@Error: Mid([tbl_Data]![Column1],InStrRev([tbl_Data]![Column1],"@"),1000)


I need to return the text string on the right of the "@".

Not every raw contains an "@" which results in an error "#Error".

I tried to fix this with the following in order to get no error if there is no "@"...


@Error: IIf(IsError(Mid([tbl_Data]![Column1],InStrRev([tbl_Data]![Column1],"@"),1000))=True,"",Mid([tbl_Data]![Column1],InStrRev([tbl_Data]![Column1],"@"),1000))

Unfortunately, it doesn't work. Anybody got an idea?

Thanks for your help!

 

[Brianwarnock]

Both the true and false parts of an IIF are evaluated therefore the false part will return the ERROR if it exists.
You should write a Function, where you will be able to test the return from the InStrRev, and use that in your query.

Brian

 

[iPad]

Both the true and false parts of an IIF are evaluated therefore the false part will return the ERROR if it exists.
You should write a Function, where you will be able to test the return from the InStrRev, and use that in your query.

Brian

Thanks for your answer!
I understand that I should try it with a step in between?
I tried this one in order to get a "true" or a "false" where I could build on the next steps.

Expr1: IsError(Mid([tbl_Data]![Column1],InStrRev([tbl_Data]![Column1],"@"),1000))

In case of a missing "@" I still get #Error here.

Is there another way to see if a certain character is contained in a field?
Such as "contains()"...

 

[iPad]

I had some hard hours with that but finaly got it...
So easy!!! I am really ashamed now.

If anybody has a similar problem this is how it works...
InStrRev([tbl_Data]![Column1],"@") this returns the place of the "@".
If there is no "@" it returns 0.
Now I wanted to have the text on the right of this "@" and used InStrRev([tbl_Data]![Column1],"@"),1000) .
Well, Access was smarter than me, because it cannot return text beginning with place 0...
I guess I learned someting today.
Thanks everybody for thinking about the problem.
Have a nice one, cheers iPad