filtering a table with many criteria

[lanelouna]

hello everybody
I have a table with fields such as ifClosed_A ifClosed_B ifClosed_C... as well as fields called CRV and CRC
my aim is to obtain from this table, only the fields where:
ifClosed_A or IfClosed_B or ifClosed_C = 1 and CRV or CRC < 94
for example
Ident ifClosed_A ifClosed_B ifClosed_C CRV CRC
1 1 1 93 92
2 0 1 92 95
3 1 0 0 95 96
4 0 0 0 91 92
5 0 1 0 94 92
I want it to look like this

Ident ifClosed_A ifClosed_B ifClosed_C CRV CRC
1 1 1 93 92
2 0 1 92 95
5 0 1 0 94 92


what is the best way to do that in VBA?

why in VBA, becouse the ifClosed fields are dynamic, i may ave ifclosed_D, ifClosed_M... so i have an array which i use array(A, B , C) in a forloop (this implies i have the fields ifClosed_A, ifclosed_b, ifClosed_C)
also the CRV and CRC are not always filtered according to the same value, the 94 may change, that is why it is important for me to do it in VBA

any suggestions, any ideas, i welcome them all
thank you in advance

Lina

 

[gransbpa]

Hello Lina,

You might want to try out these lines of code:

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset(&quot;Table1&quot

While Not rs.EOF
If (((rs!ifClosed_A) = False) And ((rs!ifClosed_B) = False) And ((rs!ifClosed_C) = False)) Or ((rs!CRV > 93) And (rs!CRC > 93)) Then
rs.Delete
rs.MoveNext
Else: rs.MoveNext
End If
Wend

I'm not sure if this is what you want: the records which do not fullfill your criteria will be deleted so be careful!