Getting #ERROR when extracting text string from query field

[sbecker61]

Using Access 2007. I have a field called "Status" in my DB that gives the status of an opportunity in a sales cycle. Status has values like:

Qualified
Qualifed/Swing
Commit
Commit/Swing
Trans
Trans/Commit

I want to write a simple query field to extract all text to the left of the "/" in the field. The challenge I have is that I get a #ERROR when the slash doesn't exist in the record. Here is my code:

Expr: Right(Trim([Status]),Len(Trim([Status]))-InStr(1, _
[Status],"/"))

How do I write the expression so that when the "/" isn't there, I just get the record back and not the #ERROR message.? Works great btw when the "/" is in the record.

Thanks.

Steve

 

[Phildaburn]

Expr: iif([status] like "*/*",Right(Trim([Status]),Len(Trim([Status]))-InStr(1, _
[Status],"/")), [status])

Is this what you're wanting?

Phil...

 

[sbecker61]

Hi,

Thanks for your response.

With your code I am still getting #ERROR from the fields that do not have a "/" in them.

Steve

 

[Phildaburn]

Is there perhaps another data condition that needs to be tested for? Are there any null or blank records?

Phil...

 

[sbecker61]

Hi,

No blank or null fields. Status is a required field in the DB. Its a text field (just checked). Just can't get anything to the left of the "/" when the "/" doesn't exist in the record. Like I said, function works great when the "/" is present in the record.

Thx.

Steve

 

[Phildaburn]

Using this:

Expr: IIf([status] Like "*/*",Right(Trim([Status]),Len(Trim([Status]))-InStr(1,[Status],"/")),[status])

I get this: <TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>Query1</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>Expr</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5>Qualified</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>Swing</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>Commit</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>Swing</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>Trans</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>Commit</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

Is this what you're after?

Phil...

 

[sbecker61]

ok, that got rid of the "/". Thanks ! Now there is a different problem. Any status that does have the "/" in it gives me the text to the right of the "/"

For example:

Fallout/Commit gives me "Commit"
Fallout gives me "Fallout"
Swing/Commit gives me "Commit"
Discovery gives me "Discovery"

Getting closer !!

Steve

 

[Phildaburn]

Expr: IIf([status] Like "*/*",Left(Trim([Status]),InStr(1,[Status],"/")-1),[status])

This should do it.

Phil...

 

[sbecker61]

works like a charm. You rock. Thank you !

Steve

 

[Phildaburn]

You're welcome.