DAO recordset with substring function

Question

i have a vb6 application that i am using DAO to create a connection to a database and trying to open a recordset. the database is a foxpro database and i have refernece to Microsoft DAO 2.5/3.5 Compatibility Library.

my code is as follows

  Dim gdbSMS As Database
  If gdbSMS Is Nothing Then
     Set gdbSMS = OpenDatabase("C:\Work\M2M Test\DATA", False, False, "Foxpro 2.6;")
  End If
  Dim sql As String
  sql = "select *, substr(lineitem,8,6)  as aa from shippers where shipper = '001322' order by aa"

  Dim rsShipper As DAO.Recordset
  Set rsShipper = gdbSMS.OpenRecordset(sql)
  Do While Not rsShipper.EOF
      Beep
      rsShipper.MoveNext
  Loop
  rsShipper.Close

when i execute teh line for openrecordset i get an error "undefinied function 'substr' in expression i run the exact same query in foxpro and it works fine. any thoughts on what i need to do to get this to work with substring functions? thanks

Did you try using `Mid` instead of `substr`? – wqw Aug 24 '11 at 20:17 — wqw, Aug 24 '11 at 20:17

score 0 · Answer 1 · answered Aug 24 '11 at 15:42

0

Try using the T-SQL SUBSTRING function instead.

SUBSTRING ( value_expression , start_expression , length_expression )

answered Aug 24 '11 at 15:42

jac

9,666
2
34
63

score 0 · Answer 2 · answered Aug 26 '11 at 13:43

Without using DAO recordsets, but instead using DataTables, and data adapters using OleDBProvider for Foxpro data (definitely not going back to Fox 2.x) gives you more current flexibility in querying....

That said, you could try by doing what SUBSTR() actually does... Try changing to

RIGHT( LEFT( LineItem, 14 ), 6 ) as AA

DAO recordset with substring function

2 Answers2