How to count number of rows in a table? (1 Viewer)

shawnntjr

Registered User.
Local time
Today, 04:04
Joined
Oct 28, 2014
Messages
42
Hi all,

I need to write a code that checks if every 6 lines in a table contains a string. If it contains the string, then carry on looping, but if it does not, it will delete the current line and the previous 6 lines before it. The program should then continue looping through the entire table.

For example,
Code:
Apple
a
b
c
d
e
Apple
f
g
h
i
j
Apple
l
m
n
o
p
q
Apple
.
.
.

I need to find the string "apple" in every 6 lines. So, that means that the first 2 Apples are fine, but for the third one needs to be deleted as it contains 7 lines instead of 6.

I'm fairly new to vba, so I really have no idea how to code from scratch. If someone can help me get the ball rolling, that'll be great :)
 

bob fitz

AWF VIP
Local time
Today, 11:04
Joined
May 23, 2011
Messages
4,717
Can you tell us a little more about the data held in this table and what other fields it has.
 

shawnntjr

Registered User.
Local time
Today, 04:04
Joined
Oct 28, 2014
Messages
42
Can you tell us a little more about the data held in this table and what other fields it has.

Basically, the table only has 2 columns. ID (auto numbered) and Field1. I imported a text file containing the details of all the events that happened to a system for the month. So each line in the text file is also placed into each field of the Field1 column.

Each event in the text file is detailed within 6 lines, however, there are certain abnormal ones (that I must ignore for now) that contain 7 lines.

I already have a code that will sort every 6 lines into 6 columns, thus containing the details of each event in a row in a different table. But due to some having 7 lines, the sorting will be messed up and, for example, my date may end up in the name column.
 

bob fitz

AWF VIP
Local time
Today, 11:04
Joined
May 23, 2011
Messages
4,717
......I already have a code that will sort every 6 lines into 6 columns, thus containing the details of each event in a row in a different table. But due to some having 7 lines, the sorting will be messed up and, for example, my date may end up in the name column.[/QUOTE]So would I be correct in thinking that in the sample data given in post #1 that each "apple" would actually be a date.
 

shawnntjr

Registered User.
Local time
Today, 04:04
Joined
Oct 28, 2014
Messages
42
Yup! Treat the apple (date) as the start of a new event :)
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:04
Joined
Sep 12, 2006
Messages
15,613
could you re-import the file.

if so you could manipulate the file before import to make this a lot easier.
 

bob fitz

AWF VIP
Local time
Today, 11:04
Joined
May 23, 2011
Messages
4,717
Obviously I do not know what your ultimate 'goal' is but clearly the table is not normalized. IMHO it would be best to deal with that first.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:04
Joined
Sep 12, 2006
Messages
15,613
you could do this as a 2 stage thing

iterate a file, and set a flag on the apple rows, with the number of lines
then do it again, deleting the sets that are wrong

as I said, I would look at tidying the data outside access, and reimporting. Access is good at dealing with well-formed data, not good at dealing with bad data.
 

shawnntjr

Registered User.
Local time
Today, 04:04
Joined
Oct 28, 2014
Messages
42
could you re-import the file.

if so you could manipulate the file before import to make this a lot easier.

Hi! I can re-import the file, however, the text file is actually created by a system and editting it would mean editting like literally half a million lines of text. :/
 

shawnntjr

Registered User.
Local time
Today, 04:04
Joined
Oct 28, 2014
Messages
42
Obviously I do not know what your ultimate 'goal' is but clearly the table is not normalized. IMHO it would be best to deal with that first.

Actually, I didn't intend to normalize my table in the first place. My re-arranging code works in the sense that:

Table A's Row 1 will be placed in Table B's Row 1 Column 1.
Table A's Row 2 will be placed in Table B's Row 1 Column 2.
Table A's Row 3 will be placed in Table B's Row 1 Column 3.
Table A's Row 4 will be placed in Table B's Row 1 Column 4.
Table A's Row 5 will be placed in Table B's Row 1 Column 5.
Table A's Row 6 will be placed in Table B's Row 1 Column 6.
Table A's Row 7 will be placed in Table B's Row 2 Column 1.
Table A's Row 8 will be placed in Table B's Row 2 Column 2.
Table A's Row 9 will be placed in Table B's Row 2 Column 3.
Table A's Row 10 will be placed in Table B's Row 2 Column 4.
.
.
.
So on and so forth
 

Christos99

Registered User.
Local time
Today, 04:04
Joined
Dec 19, 2013
Messages
24
Hi try this : Call Del_Invalid ("Apple")

Cheers, Chris



Code:
Option Compare Database
Option Explicit

Sub Del_Invalid(strSearch As String)

    Dim db As Database, rst As Recordset, lngcnt As Long, k As Long
    
    On Error GoTo Del_Invalid_Err
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Table1", dbOpenTable)
    
    lngcnt = 0
    With rst
    
        Do While Not .EOF
            Debug.Print !field1
            
            If !field1 = strSearch Then
                If lngcnt > 6 Then
                    'Delete offending records
                    .MovePrevious
                    For k = 1 To lngcnt
                        .Delete
                        .MovePrevious
                    Next
                    lngcnt = 0
                Else
                    lngcnt = 1
                End If
            Else
                lngcnt = lngcnt + 1
            End If
            .MoveNext
        Loop
        ' In case last one was offending
        If lngcnt > 6 Then
            'Delete offending records
            .MovePrevious
            For k = 1 To lngcnt
                .Delete
                .MovePrevious
            Next
        End If
        
    End With
    
Del_Invalid_Exit:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    Exit Sub
    
Del_Invalid_Err:
    MsgBox "Error " & Str$(Err.Number) & " = " & Err.Description
    Resume Del_Invalid_Exit
    
End Sub
 

shawnntjr

Registered User.
Local time
Today, 04:04
Joined
Oct 28, 2014
Messages
42
Hi try this : Call Del_Invalid ("Apple")

Cheers, Chris



Code:
Option Compare Database
Option Explicit
 
Sub Del_Invalid(strSearch As String)
 
    Dim db As Database, rst As Recordset, lngcnt As Long, k As Long
 
    On Error GoTo Del_Invalid_Err
 
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Table1", dbOpenTable)
 
    lngcnt = 0
    With rst
 
        Do While Not .EOF
            Debug.Print !field1
 
            If !field1 = strSearch Then
                If lngcnt > 6 Then
                    'Delete offending records
                    .MovePrevious
                    For k = 1 To lngcnt
                        .Delete
                        .MovePrevious
                    Next
                    lngcnt = 0
                Else
                    lngcnt = 1
                End If
            Else
                lngcnt = lngcnt + 1
            End If
            .MoveNext
        Loop
        ' In case last one was offending
        If lngcnt > 6 Then
            'Delete offending records
            .MovePrevious
            For k = 1 To lngcnt
                .Delete
                .MovePrevious
            Next
        End If
 
    End With
 
Del_Invalid_Exit:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    Exit Sub
 
Del_Invalid_Err:
    MsgBox "Error " & Str$(Err.Number) & " = " & Err.Description
    Resume Del_Invalid_Exit
 
End Sub

Hi Christos! YOU'RE THE MAN! Your code is exactly what I needed to get me rolling :)
 

shawnntjr

Registered User.
Local time
Today, 04:04
Joined
Oct 28, 2014
Messages
42
Hi try this : Call Del_Invalid ("Apple")

Cheers, Chris



Code:
Option Compare Database
Option Explicit
 
Sub Del_Invalid(strSearch As String)
 
    Dim db As Database, rst As Recordset, lngcnt As Long, k As Long
 
    On Error GoTo Del_Invalid_Err
 
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Table1", dbOpenTable)
 
    lngcnt = 0
    With rst
 
        Do While Not .EOF
            Debug.Print !field1
 
            If !field1 = strSearch Then
                If lngcnt > 6 Then
                    'Delete offending records
                    .MovePrevious
                    For k = 1 To lngcnt
                        .Delete
                        .MovePrevious
                    Next
                    lngcnt = 0
                Else
                    lngcnt = 1
                End If
            Else
                lngcnt = lngcnt + 1
            End If
            .MoveNext
        Loop
        ' In case last one was offending
        If lngcnt > 6 Then
            'Delete offending records
            .MovePrevious
            For k = 1 To lngcnt
                .Delete
                .MovePrevious
            Next
        End If
 
    End With
 
Del_Invalid_Exit:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    Exit Sub
 
Del_Invalid_Err:
    MsgBox "Error " & Str$(Err.Number) & " = " & Err.Description
    Resume Del_Invalid_Exit
 
End Sub

Hi Chris,

My actual data contains a string like "pear oranges apple bananas".
Is there a way to change it such that it will check to see if the entire string contains the word "apple" instead of just searching if the field is apple or not?

Currently, the code deletes every single row as it only reads for "Apple" exactly, I think.

I've already tried these:
Code:
Call Del_Invalid(" "*"  & Apple &  "*" ")
and
Code:
If !field1 = "*" & strSearch & "*" Then
 

Christos99

Registered User.
Local time
Today, 04:04
Joined
Dec 19, 2013
Messages
24
Glad it worked for you. Coding this was the most fun I have had this week!

Try the following. Lookup help for INSTR so you can see how it works although fairly obvious.

If InStr(1, !field1, "apple") > 0 Then
 

shawnntjr

Registered User.
Local time
Today, 04:04
Joined
Oct 28, 2014
Messages
42
Glad it worked for you. Coding this was the most fun I have had this week!

Try the following. Lookup help for INSTR so you can see how it works although fairly obvious.

If InStr(1, !field1, "apple") > 0 Then

HAHA, glad you had fun with my problem! :) This works perfectly! Many thanks to you Chris! You are literally my hero right now :)
 

Users who are viewing this thread

Top Bottom