asked on

Access SQL qurey error

Hello I get this error on the below select statment. Your help is apperciated in advance.

Undefined function 'chatindex' in expression

select LEFT(products.number, charindex('.',products.number)) from products

Torrwin

Try:

SELECT LEFT(Products.Number, InStr(1, Products.Number, '.'))
FROM Products

Torrwin

Actually, that may include the period in the selection, so you may need to add a -1 to the position:

SELECT LEFT(Products.Number, (InStr(1, Products.Number, '.')-1))
FROM Products

ubsmail

ASKER

Looks good any way to handel the error if it does not have a "."

Expr1000
#Error
1ABMJK0005
1ABMJK0005
1ABMJK0005
1ABMJK0005
#Error
1ABMJK0006
1ABMJK0006
1ABMJK0006
1ABMJK0006

Torrwin

You could use a CASE statement, something like:

SELECT CASE WHEN (InStr(1, Products.Number, '.') > 0) THEN LEFT(Products.Number, (InStr(1, Products.Number, '.')-1)) ELSE Products.Number) END

ubsmail

ASKER

Thanks Torrwin

I got this error:

Syntax error (missing operator) in qury experrsion 'CASE WHEN (Instr(1, Products.Number, '.') >0)........

Torrwin

Oops, looks like a missing parenthesis:

SELECT CASE WHEN ((InStr(1, Products.Number, '.') > 0) THEN LEFT(Products.Number, (InStr(1, Products.Number, '.')-1)) ELSE Products.Number) END

ubsmail

ASKER

Same error accesss is picky

ASKER CERTIFIED SOLUTION

Torrwin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ubsmail

ASKER

Thanks!