Sql数据库换成Access数据库后，查询语句出错

如下语句，Sql数据库换成Access数据库后，查询语句出错（Access不支持SUBSTRING）。SUBSTRING改成mid或left也不行。

SQL = "SELECT province.AreaName AS sheng, city.AreaName AS shi, town.AreaName AS xian, Table1.* "
SQL = SQL & " FROM Table1 "
SQL = SQL & " INNER JOIN AreaInfo AS province ON province.AreaID = SUBSTRING(Table1.ReportArea, 1, 2) + '0000'"
SQL = SQL & " INNER JOIN AreaInfo AS city ON city.AreaID = SUBSTRING(Table1.ReportArea, 1, 4) + '00'"
SQL = SQL & " INNER JOIN AreaInfo AS town ON town.AreaID = Table1.ReportArea"
SQL = SQL & " INNER JOIN ElementInfo ON Table1.ReportID = ElementInfo.ReportID AND "
SQL = SQL & " Table1.ElementName = ElementInfo.ElementName"
SQL = SQL & " WHERE (Table1.ReportID = '" & BCBiao & "')"