Number of characters in a cell

[ossse]

I am trying to cycle through all of the rows in a field (field ='Comments') and return the number of characters of that cell. The field is a memo type.

Could you please help with my module code. It does not like the following line:
length = Len(fld(i))

Here is the code:

Dim dbs As Database, fld As Field, tbl As TableDef
Dim i As Integer, count As Integer, length As Integer, mem As String

Set dbs = CurrentDb()
Set tbl = dbs.TableDefs![Filtered]
Set fld = tbl.Fields![Comment]
count = tbl.RecordCount

Debug.Print count

For i = 0 To (count - 1)
length = Len(fld(i))
Debug.Print length
Next i

 

[ByteMyzer]

First, please stop referring to MS Access fields as Cells. Cells are objects specific to MS Excel.

Second, the expression Len(fld(i)) implies a field array, and fld is only defined as a single-level object.

If what you are trying to accomplish is to loop through all the records and print the length of the Comment field for each line, you need to reference a RecordSet object, not a TableDef object. Try this:

Dim dbs As DAO.Database, rst as DAO.Recordset

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("[i]Filtered[/i]")

Do While Not rst.EOF
    Debug.Print Len(rst.Fields("Comment"))
    rst.MoveNext
Loop

rst.Close
Set rst = Nothing

dbs.close
Set dbs = Nothing

 

[Melagan]

I am trying to cycle through all of the rows in a field (field ='Comments')

Then below...

Set fld = tbl.Fields![Comment]

Is your field name 'Comments' or 'Comment' ?

Beyond that, I think the code is throwing an error because your fld variable is for a Field object, not the contents of the field itself. I was able to accomplish this by looping through a recordset.

Function CountMe()
Dim dbs As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim rst As DAO.Recordset
Dim i As Integer, Count As Integer, length As Integer, mem As String
Dim SQLText As String
    
    SQLText = "SELECT Comments FROM Filtered"
    Set dbs = CurrentDb()
    Set tbl = dbs.TableDefs("Filtered")
    Set rst = dbs.OpenRecordset(SQLText)

    Count = tbl.RecordCount

    Debug.Print Count

    Do While rst.EOF = False
        length = Len(rst("Comments"))
        Debug.Print length
    Loop

End Function

~Melagan
______
"It's never too late to become what you might have been.

 

[Melagan]

Here is what the loop should look like:

Do While rst.EOF = False
  length = Len(rst("Comments"))
  Debug.Print length
[COLOR=red]  rst.MoveNext[/color]
Loop

~Melagan
______
"It's never too late to become what you might have been.

 

[CautionMP]

...and a couple of enhancements for speed.

Sub CountMe()
Dim rst As DAO.Recordset

    Set rst = CurrentDB.OpenRecordset("SELECT Comments FROM Filtered", dbOpenForwardOnly, dbReadOnly)

    Do While rst.EOF = False
        Debug.print rst.RecordCount, Len(rst.Collect(0))
        rst.MoveNext
    Loop

CountMe_Exit:
    rst.Close
    set rst = Nothing
End Function

[ol][li]Keep the variable declarations to a minimum.[/li]
[li]Open the recordset forward only & read only[/li]
[li]Use [tt]RecordCount[/tt] to find the number of records (with Forward Only will not work at BOF/EOF so do it in the loop.)[/li]
[li]Use the hidden recordset method [tt]Collect[/tt] to get the data (Microsoft claims this is the fastest method to get the data from a field since it only pulls the value and no other field properties.)[/li]
[li]Clean up the object(s), old habit from Excel[/li][/ol]

Hope this helps,
CMP