Parsing this field - 432-45

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In an access query, i need to parse out everything to the left of the dash.
I know that it can be done using the left() function. Using a hardcoded
number to determine the length of the parse works fine

(left("432-45",3).

However i cannot assume that there will always be 3 digit to the left. I
tried embedding the instr() function

(left("432-45",instr("432-45","-")-1),

but it does not work. For some reason, access cannot use the result of the
instr() function, neither while embedded or if I store it in another
variable. It just doesn't like it....

Can You Help?

RLiss, Knowles Electronics
(e-mail address removed)
 
Well, you have unbalanced Parentheses in your posted example. 3 open and 2
close. So that could be the problem.

Also, you could be getting an error, if the field value is null or does not
contain a dash.

What is the ERROR you are getting?

TRY the following (all on one line).

IIF(Instr(1,YourField & "","-")> 1,Left(YourField,Instr(1,YourField,"-")-1),YourField)
 
The unbalanced paren is just a typo. The message that I get whenever I run
the the query is:

This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables. (Error 3071)

I get this whenever I try and use the value returned by the instr()
function, whether it is embedded or I store the value in a variable and try
to use it that way. Is it possible that the left() function does not
recognize this value as a number?
 
Thanks, your statement worked. I wish I had time to look it over to see what
exactly it is doing. You know how it is. I'm just happy that it works, and
I'll go onward. I'm sure I'll use it over again.

RLiss
 
Back
Top