(more readable) 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]

Hi Lana,

You might want to try this 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

This actually deletes the records which don't fullfill your criteria, so be careful!

 

[lanelouna]

thank you for your answer, i did as u told me, however it worked partly, i am not sure to know why
in fact it only supressed line which CRs are >93, but it didn t delete the lines with an ifclosed = false
i tried (ifClosedA = 0 or ifClosedA = null ) and (ifclosedB = 0 or ifclosedB = null) ...and it also didn t work
i don t get it
it should work though
well can you help on this?
Lina

 

[gransbpa]

Hello Lina,

I first assumed that we were talking about booleans. It seems however, that these fields are text fields. If that's the case (is it?) you should take the liteal values which are in the fields. You might also consider changing the datatype of these fields to yes/no (boolean). You can always do this if the field value is limited to two possible values.

I'm going home right now, but if don't get it working I'll take another look at it on Monday morning.

Have a nice weekend, Paul

 

[lanelouna]

Hello Paul
Thank you ofr your quick Response
in fact i checked the type and it is numeric
wellabout the boolean i don t knowi will check it
well i am working on it
keep you updated
thank you again, and have a great weekend as well
Lina