InStr and ADO

[Al]

When I use the Instring function (instr) in Access, it
works fine, however, when I use it in the following Excel
procedure, it says "invalid" function or procedure.
The Excel Procedure creates a table in an Access Database
from another table.

Is the Instr function supposed to be spelled differently
in ADO? Or do I need to create this function in Access?
Thanks in advance.


Sub ExtractUniqueStylesByClass()
Dim cn As ADODB.Connection, RS As ADODB.Recordset, r As
Long
Dim Targetrange As Range
Dim intColIndex As Integer

Select Case TableXists("UNIQUESTYLESBYCLASS")
Case "TRUE"
DeleteAccessTable ("UNIQUESTYLESBYCLASS")
Case Else
End Select

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=K:\MPS\DATA\GMDATABASE.mdb;"
' open a recordset
Set RS = New ADODB.Recordset

'Create Table for Weekly Receipts By Class Report

SELECTOR = "SELECT RESVDATA.CLASS,(Left(RESVDATA.[MFG
STYLE],((InString(RESVDATA.[MFG STYLE],' * '))-1))) AS
STYLE INTO UNIQUESTYLESBYCLASS"

FROMCLAUSE = "FROM RESVDATA"

GROUPCLAUSE = "GROUP BY RESVDATA.CLASS, ((InStr(RESVDATA.
[MFG STYLE],' * '))-1)));"

strsql = SELECTOR & " " & FROMCLAUSE '& " " & GROUPCLAUSE

With RS
RS.Open strsql, cn, , , -1
End With

Set RS = Nothing
Set RS = New ADODB.Recordset

cn.Close
Set cn = Nothing

End Sub

 

[Al]

The Instr works fine within ADO. Once I corrected the
parentheses, the sub worked fine.

Sub ExtractUniqueStylesByClass()
Dim cn As ADODB.Connection, RS As ADODB.Recordset, r As
Long
Dim Targetrange As Range
Dim intColIndex As Integer

Select Case TableXists("UNIQUESTYLESBYCLASS")
Case "TRUE"
DeleteAccessTable ("UNIQUESTYLESBYCLASS")
Case Else
End Select

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=K:\MPS\DATA\GMDATABASE.mdb;"
' open a recordset
Set RS = New ADODB.Recordset

'Create Table for Weekly Receipts By Class Report

SELECTOR = "SELECT RESVDATA.CLASS,(LEFT([RESVDATA].[MFG
STYLE],((InStr([RESVDATA].[MFG STYLE],'*'))-1))) AS STYLE
INTO UNIQUESTYLESBYCLASS"

FROMCLAUSE = "FROM RESVDATA"

GROUPCLAUSE = "GROUP BY RESVDATA.CLASS, (LEFT([RESVDATA].
[MFG STYLE],((InStr([RESVDATA].[MFG STYLE],'*'))-1)));"

strsql = SELECTOR & " " & FROMCLAUSE & " " & GROUPCLAUSE

With RS
RS.Open strsql, cn, , , -1
End With

Set RS = Nothing
Set RS = New ADODB.Recordset

cn.Close
Set cn = Nothing

End Sub

 

[Robin Hammond]

Not sure about ADO with Access, but with SQL an equivalent string I use is
something like

LEFT(M.ItemName, CHARINDEX('*',M.ItemName)-1)

I haven't bother changing the variables to match your query.

Might as well give it a try.

Robin Hammond
www.enhanceddatasystems.com