'Mid' is not a recognized function name


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: 'Mid' is not a recognized function name

  1. #1
    John Wood Guest

    'Mid' is not a recognized function name

    The following code that references the MID function in the sSQL string
    that is built works fine.

    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    With con
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString =
    "\\MOSQUITO\DATA\apps\database\access70\pbsites\pbsites.mdb"
    .CursorLocation = adUseNone
    .Open
    End With
    sConnect = con.ConnectionString
    sSQL = "SELECT PBSites.PkId, PBSites.SiteName, PBSites.SiteName_abbrev,
    PBSites.TwspId, Habitats.Type, Mid([SiteName_abbrev],3,2) AS SECT "
    sSQL = sSQL & "FROM Habitats RIGHT JOIN PBSites ON Habitats.PkId =
    PBSites.HabitatId "
    sSQL = sSQL & "WHERE PBSites.TwspId = " & TwspId & ""
    If oDatabaseAPI.GetRecordset(sConnect, sSQL, rst) Then

    However, it bombs on the last statement with error " 'Mid' is not a
    recognized function name"
    when the connection string is changed to connect to an SQL Server db as
    in the following:

    With con
    .ConnectionString = "Provider=SQLOLEDB.1; Data Source=MOSQ1;" & _
    "Initial Catalog=pbsites; User Id=sa; Password="

    .CursorLocation = adUseNone
    .Open
    End With

    Any ideas?

    Thanks John


  2. #2
    Scott P Guest

    Re: 'Mid' is not a recognized function name


    John,

    this is correct, SQL server will not recognise the mid function when it tries
    to parse your SQL string, Access will because it relies on a different ANSI/standard
    of SQL.

    You will need to use the substring function for your SQL server example to
    acheive this.

    HTH

    Scott P

    John Wood <jwood@co.sarasota.fl.us> wrote:
    >The following code that references the MID function in the sSQL string
    >that is built works fine.
    >
    >Dim con As ADODB.Connection
    >Set con = New ADODB.Connection
    >With con
    > .Provider = "Microsoft.Jet.OLEDB.4.0"
    > .ConnectionString =
    >"\\MOSQUITO\DATA\apps\database\access70\pbsites\pbsites.mdb"
    > .CursorLocation = adUseNone
    > .Open
    >End With
    >sConnect = con.ConnectionString
    >sSQL = "SELECT PBSites.PkId, PBSites.SiteName, PBSites.SiteName_abbrev,
    >PBSites.TwspId, Habitats.Type, Mid([SiteName_abbrev],3,2) AS SECT "
    >sSQL = sSQL & "FROM Habitats RIGHT JOIN PBSites ON Habitats.PkId =
    >PBSites.HabitatId "
    >sSQL = sSQL & "WHERE PBSites.TwspId = " & TwspId & ""
    >If oDatabaseAPI.GetRecordset(sConnect, sSQL, rst) Then
    >
    >However, it bombs on the last statement with error " 'Mid' is not a
    >recognized function name"
    >when the connection string is changed to connect to an SQL Server db as
    >in the following:
    >
    >With con
    > .ConnectionString = "Provider=SQLOLEDB.1; Data Source=MOSQ1;" & _
    > "Initial Catalog=pbsites; User Id=sa; Password="
    >
    > .CursorLocation = adUseNone
    > .Open
    >End With
    >
    >Any ideas?
    >
    >Thanks John
    >



  3. #3
    Paul Clement Guest

    Re: 'Mid' is not a recognized function name

    On Thu, 17 Aug 2000 15:25:07 -0400, John Wood <jwood@co.sarasota.fl.us> wrote:

    The following code that references the MID function in the sSQL string
    that is built works fine.

    Mid is a VBA function and Access enables the use of VBA and user defined functions as long as the
    query is executing within the Access environment.


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center