Is there a function like Substring?

  • Thread starter Thread starter Jennifer
  • Start date Start date
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
 
I've got an Access 2000 database. Trying to make this query work:
Click to expand...
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
Click to expand...

Charindex = InStr
Substring = Mid

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

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

Most VBA functions work in Access queries just fine.
 
Back
Top