Parsing this field - 432-45

R

RLiss

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
[email protected]
 
J

John Spencer (MVP)

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)
 
R

RLiss

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?
 
Top