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
ubsmailAsked:
Who is Participating?
 
TorrwinConnect With a Mentor Commented:
If you're running it in Access,  you could use the IIF function instead:

SELECT IIF(InStr(1, Products.Number, '.') > 0, LEFT(Products.Number, (InStr(1, Products.Number, '.')-1)), Products.Number)
FROM Products
0
 
TorrwinCommented:
Try:

SELECT LEFT(Products.Number, InStr(1, Products.Number, '.'))
FROM Products
0
 
TorrwinCommented:
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
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
ubsmailAuthor Commented:
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
0
 
TorrwinCommented:
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
0
 
ubsmailAuthor Commented:
Thanks Torrwin

I got this error:

Syntax error (missing operator) in qury experrsion 'CASE WHEN (Instr(1, Products.Number, '.') >0)........
0
 
TorrwinCommented:
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
0
 
ubsmailAuthor Commented:
Same error accesss is picky
0
 
ubsmailAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.