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

 

[sbecker61]

Hi,

Me again. Your function works wonderful inside of Access. When I use Excel 2007 to run the query that contains the function, the function doesn't work on the Excel side. I've checked and re-checked and the function isn't working in Excel. Like I said, it works great when I am in Access 2007. It just doesn't run in Excel. Any idea as to why? I've googled galore with no answers.

Basically what I get back is the entire value of "Status". Its like the function never executed.

I am doing nothing fancy in Excel. I just run the query from Excel.

Thanks.

Steve

 

[james_lankford]

change the both * to %

 

[sbecker61]

Here is my code:

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

This messes up the Access query now. Isn't parsing the "/" anymore.

Thx.

Steve

 

[james_lankford]

% is the standard way of doing it
its the way oracle, mysql and ms sql server do it

* is non standard
its the way access does it

excel follows the standard

that's just the way it is

you could try this instead

IIf
(
  InStr(1, [Status], "/") > 0 
  Left
  (
    Trim([Status]),
    InStr(1, [Status], "/") - 1
  ),
  [status]
)