Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
InStr and ADO
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
InStr Solution
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
InStr and ADO
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 "Al" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with VBA InStr() function | Excel Discussion (Misc queries) | |||
instr isn't finding all the matches | Excel Programming | |||
instr isn't finding all the matches | Excel Programming | |||
Combine two macros using InStr? | Excel Programming | |||
InStr used in SQL query | Excel Programming |