Is there a function like Substring?

J

Jennifer

I've got an Access 2000 database. Trying to make this query work:

SELECT
substring(AreaMgrs.ManagerName, charindex(' ', AreaMgrs.ManagerName,
1) + 1, 40) +
', ' + substring(AreaMgrs.ManagerName, 1, charindex(' ',
AreaMgrs.ManagerName, 1) - 1)
AS Name, Areas.VMX
FROM AreaMgrs INNER JOIN Areas ON AreaMgrs.AreaID = Areas.AreaID
where charindex(' ',AreaMgrs.ManagerName, 1) > 0

Access doesn't like CharIndex or Substring. Are there functions in
Access that perform these functions?

Thanks,
Jennifer
 
P

Peter Doering

I've got an Access 2000 database. Trying to make this query work:
SELECT
substring(AreaMgrs.ManagerName, charindex(' ', AreaMgrs.ManagerName,
1) + 1, 40) +
', ' + substring(AreaMgrs.ManagerName, 1, charindex(' ',
AreaMgrs.ManagerName, 1) - 1)
AS Name, Areas.VMX
FROM AreaMgrs INNER JOIN Areas ON AreaMgrs.AreaID = Areas.AreaID
where charindex(' ',AreaMgrs.ManagerName, 1) > 0

Charindex = InStr
Substring = Mid

HTH - Peter
 
J

Jennifer

Thanks. That would work if I were writing VBA code, but I'm not.
This is in a query, using SQL.

Thanks,
Jennifer
 
R

Rick Brandt

Jennifer said:
Thanks. That would work if I were writing VBA code, but I'm not.
This is in a query, using SQL.

Most VBA functions work in Access queries just fine.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top