SubString

Question

User-592488039 posted

All,

Trying to extract information using substring and it is comming back undefined function:

SELECT substring([Node],1,charIndex([Node],':')-1)
FROM [EMS Year];

The data looks like:     21:2:189

Trying to extract the numbers to the left of the first :

Help,

David / 2007 Access

Answer 1

User-1360095595 posted
Try using: charIndex(':', [Node])-1

Answer 2

User-592488039 posted

Here is what i put in:

SELECT charIndex(':', [Node])-1
FROM [EMS Year];

Result: Undefined function in expression

Answer 3

User-1360095595 posted
Just realized you're trying to do this in access not SQL server. It looks like you need to use instr & mid. Refer: http://www.accessmonster.com/Uwe/Forum.aspx/access-queries/12824/Substring-and-Charindex-fuctions-in-Access