Very Computer

Run-time error '3265':

Item cannot be found in the collection corresponding to the requested
name or ordinal.

I create the recordset in question by:

Calling the function shown below to retrieve an ADO Recordset from an Excel
worksheet range.

Set rsRecordset = OpenRecordsetFromExcelWorkbook(sFilePath, "C4:N100", "NOMS
<> ''", "#" & Format(Me.Fields("Date").Value, "mm/dd/yyyy") & "# AS
'DATE',HEURES,ITEM,OPERATION,`/MILLE` AS 'GDES/MILLE',`/HEURE` AS
'CADENCE/HEURE',`/JOUR` AS 'QTE/JOUR',PIECE AS GDES_PIECE,REGIE AS
HEURES_REGIE,REGIE1 AS GDES_REGIE,JOUR AS GAIN_JOUR,NOMS")

Public Function OpenRecordsetFromExcelWorkbook(ByVal sPath As String, ByVal
sRangeName As String, Optional ByVal vFilter As Variant, Optional ByVal
sSqlSelectColumns As String = "*") As ADODB.Recordset
    Dim oConn As ADODB.Connection
    Dim oCmd As ADODB.Command
    Dim oRS As ADODB.Recordset

    ' Open a connection to the Excel workbook.
    Set oConn = New ADODB.Connection
    oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                             "Data Source=" & sPath & ";" & _
                             "Extended Properties=""Excel 8.0;HDR=Yes;"";"
    oConn.Open

    ' Create a command object and set its ActiveConnection
    Set oCmd = New ADODB.Command
    oCmd.ActiveConnection = oConn

    ' This SQL statement selects a cell range in a particular worksheet.
    oCmd.CommandText = "SELECT " & sSqlSelectColumns & " from `" &
sRangeName & "`"
    If IsMissing(vFilter) = False And IsEmpty(vFilter) = False And
TypeName(vFilter) = "String" Then
        oCmd.CommandText = oCmd.CommandText & " WHERE " & vFilter
    End If

    ' Open a recordset containing the worksheet data.
    Set oRS = New ADODB.Recordset
    oRS.CursorLocation = adUseClient

    oRS.Open oCmd, , adOpenKeyset, adLockOptimistic

    Set OpenRecordsetFromExcelWorkbook = oRS

End Function

When I use code below to sort the Recordset there is NO Error?

rsRecordset.Sort = "NOMS ASC"

However, when I try to use similar code to sort the Recordset using the DATE
field as show below I get  "Run-time error '3265'".

rsRecordset.Sort = "DATE ASC"

DATE is reserved word. You cannot use it as a filed name.
Try to wrap it into []

rsRecordset.Sort = "[DATE] ASC"

or, better, to give another alias to that field

Val

Quote:>-----Original Message-----
>Can someone help me understand why I am getting the

Quote:>me fix it?

>Run-time error '3265':

>Item cannot be found in the collection corresponding to
the requested
>name or ordinal.

>I create the recordset in question by:

>Calling the function shown below to retrieve an ADO

Quote:>worksheet range.

>Set rsRecordset = OpenRecordsetFromExcelWorkbook

Quote:><> ''", "#" & Format(Me.Fields

Quote:>'DATE',HEURES,ITEM,OPERATION,`/MILLE`

Quote:>'CADENCE/HEURE',`/JOUR` AS 'QTE/JOUR',PIECE AS
GDES_PIECE,REGIE AS
>HEURES_REGIE,REGIE1 AS GDES_REGIE,JOUR AS GAIN_JOUR,NOMS")

>Public Function OpenRecordsetFromExcelWorkbook(ByVal

Quote:>sRangeName As String, Optional ByVal vFilter As Variant,
Optional ByVal
>sSqlSelectColumns As String = "*") As ADODB.Recordset
>    Dim oConn As ADODB.Connection
>    Dim oCmd As ADODB.Command
>    Dim oRS As ADODB.Recordset

>    ' Open a connection to the Excel workbook.
>    Set oConn = New ADODB.Connection
>    oConn.ConnectionString

Quote:>                             "Data Source=" & sPath & ";"
& _
>                             "Extended Properties=""Excel
8.0;HDR=Yes;"";"
>    oConn.Open

>    ' Create a command object and set its ActiveConnection
>    Set oCmd = New ADODB.Command
>    oCmd.ActiveConnection = oConn

>    ' This SQL statement selects a cell range in a

Quote:>    oCmd.CommandText = "SELECT " & sSqlSelectColumns & "
from `" &
>sRangeName & "`"
>    If IsMissing(vFilter) = False And IsEmpty(vFilter) =
False And
>TypeName(vFilter) = "String" Then
>        oCmd.CommandText = oCmd.CommandText & " WHERE " &
vFilter
>    End If

>    ' Open a recordset containing the worksheet data.
>    Set oRS = New ADODB.Recordset
>    oRS.CursorLocation = adUseClient

>    oRS.Open oCmd, , adOpenKeyset, adLockOptimistic

>    Set OpenRecordsetFromExcelWorkbook = oRS

>End Function

>When I use code below to sort the Recordset there is NO
Error?

>rsRecordset.Sort = "NOMS ASC"

>However, when I try to use similar code to sort the

Quote:>field as show below I get  "Run-time error '3265'".

>rsRecordset.Sort = "DATE ASC"

>.

 I still have the same error.

Quote:> Hi,

> DATE is reserved word. You cannot use it as a filed name.
> Try to wrap it into []

> rsRecordset.Sort = "[DATE] ASC"

> or, better, to give another alias to that field

> Val

> >-----Original Message-----
> >Can someone help me understand why I am getting the
> following error & help
> >me fix it?

> >Run-time error '3265':

> >Item cannot be found in the collection corresponding to
> the requested
> >name or ordinal.

> >I create the recordset in question by:

> >Calling the function shown below to retrieve an ADO
> Recordset from an Excel
> >worksheet range.

> >Set rsRecordset = OpenRecordsetFromExcelWorkbook
> (sFilePath, "C4:N100", "NOMS
> ><> ''", "#" & Format(Me.Fields
> ("Date").Value, "mm/dd/yyyy") & "# AS
> >'DATE',HEURES,ITEM,OPERATION,`/MILLE`
> AS 'GDES/MILLE',`/HEURE` AS
> >'CADENCE/HEURE',`/JOUR` AS 'QTE/JOUR',PIECE AS
> GDES_PIECE,REGIE AS
> >HEURES_REGIE,REGIE1 AS GDES_REGIE,JOUR AS GAIN_JOUR,NOMS")

> >Public Function OpenRecordsetFromExcelWorkbook(ByVal
> sPath As String, ByVal
> >sRangeName As String, Optional ByVal vFilter As Variant,
> Optional ByVal
> >sSqlSelectColumns As String = "*") As ADODB.Recordset
> >    Dim oConn As ADODB.Connection
> >    Dim oCmd As ADODB.Command
> >    Dim oRS As ADODB.Recordset

> >    ' Open a connection to the Excel workbook.
> >    Set oConn = New ADODB.Connection
> >    oConn.ConnectionString
> = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> >                             "Data Source=" & sPath & ";"
> & _
> >                             "Extended Properties=""Excel
> 8.0;HDR=Yes;"";"
> >    oConn.Open

> >    ' Create a command object and set its ActiveConnection
> >    Set oCmd = New ADODB.Command
> >    oCmd.ActiveConnection = oConn

> >    ' This SQL statement selects a cell range in a
> particular worksheet.
> >    oCmd.CommandText = "SELECT " & sSqlSelectColumns & "
> from `" &
> >sRangeName & "`"
> >    If IsMissing(vFilter) = False And IsEmpty(vFilter) =
> False And
> >TypeName(vFilter) = "String" Then
> >        oCmd.CommandText = oCmd.CommandText & " WHERE " &
> vFilter
> >    End If

> >    ' Open a recordset containing the worksheet data.
> >    Set oRS = New ADODB.Recordset
> >    oRS.CursorLocation = adUseClient

> >    oRS.Open oCmd, , adOpenKeyset, adLockOptimistic

> >    Set OpenRecordsetFromExcelWorkbook = oRS

> >End Function

> >When I use code below to sort the Recordset there is NO
> Error?

> >rsRecordset.Sort = "NOMS ASC"

> >However, when I try to use similar code to sort the
> Recordset using the DATE
> >field as show below I get  "Run-time error '3265'".

> >rsRecordset.Sort = "DATE ASC"

> >.

Okay. You get that error message in case if you are trying
to use field name, which does not exist in recordset.
Check which names of fileds you get after recordset is
opened. Put breakpoint after you opened recordset and
check Name property of each field (and particularly which
causes problem) in recordset

Val

Quote:>-----Original Message-----
>I changed field 'DATE' to field 'MyDATE' in the SQL
argument of method
>OpenRecordsetFromExcelWorkbook & in the sort string