filtering records

[Guest]

Hello
i have a recordset i would like to filter
This recordset is from a table where a field named "codcommessa" has value like "1-dg-2003", "2-dg-2003" and so on.
to assign a new code i need to know which is the higher digits on the right of the previous code, keeping in count also the year to which the code reffers (last 4 digits)
So, first of all i would filter the recordset to have only records for a certain year (the one we are when code runs)
i have builded several string expression for the .filter proprierties of the recordset and nothing works.... there's something wrong with how i create this string... can someone check it

here is the code
Dim data As Strin
data = Format(Date, "yyyy"
Dim rst As ADODB.Recordse
Set rst = New ADODB.Recordse
rst.Open "tblcommesse", CurrentProject.Connection, adOpenStatic, adLockOptimisti
With rs
.Filter = "Right(.Fields(""codcommessa"").Value, 4)='" & data & "'

on the last line code stops for bug

thanks in advance
Rocco

 

[Wayne Morgan]

.Filter = "Right(.Fields(""codcommessa"").Value, 4)='" & data &
"'"

Your .Fields is inside the quotes to be passed to the .Filter. This won't be
expanded by the With statement in this location. You need to pass the text
exactly as you want the filter to see it, not as VBA would see it.

..Filter = "Right([codcommessa], 4)='" & data & "'"

--
Wayne Morgan
Microsoft Access MVP

Hello,
i have a recordset i would like to filter.
This recordset is from a table where a field named "codcommessa" has value

like "1-dg-2003", "2-dg-2003" and so on..

to assign a new code i need to know which is the higher digits on the

right of the previous code, keeping in count also the year to which the code
reffers (last 4 digits).

So, first of all i would filter the recordset to have only records for a

certain year (the one we are when code runs).

i have builded several string expression for the .filter proprierties of

the recordset and nothing works.... there's something wrong with how i
create this string... can someone check it?

 

[Guest]

Already tried: doesn't work
i still receive msg saying
"Run-time err nr 3001: arguments are of the wrong type, are out of acceptable range, or are in cnflict with one other
code was
-----------------------------------------------------------------------------
Dim data As Strin
Dim numero As Intege
data = Format(Date, "yyyy"
Dim rst As ADODB.Recordse
Set rst = New ADODB.Recordse
rst.Open "tblmanutenzioni", CurrentProject.Connection, adOpenStatic, adLockOptimisti
With rs
.Filter = "Right([codcommessa], 4)='" & data & "'

 

[Wayne Morgan]

Looking through the help file, I don't see where an ADO recordset will
accept an expression, such as the Right statement, as a filter.

See if this will work instead.
..Filter = "[codcommessa] Like '*" & data & "'"

--
Wayne Morgan
Microsoft Access MVP

Already tried: doesn't work.
i still receive msg saying:
"Run-time err nr 3001: arguments are of the wrong type, are out of

acceptable range, or are in cnflict with one other"

code was:
----------------------------------------------------------------------- -------
Dim data As String
Dim numero As Integer
data = Format(Date, "yyyy")
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "tblmanutenzioni", CurrentProject.Connection, adOpenStatic, adLockOptimistic
With rst
.Filter = "Right([codcommessa], 4)='" & data & "'"
----------------------------------------------------------------------- --------
...where codcommesa is the name of a field in tblmanutenzioni


thanks, anyway
Rocco