counting characters

[Ezekiël]

Hello,

How can i count the characters in a field as fast as possible using vba. I
have a table with more than 100 fields and i need to know how many
characters there are in each field.

Thx

 

[Roger Carlson]

The Len() function will return the number of characters in a text field or
string variable.

 

[Ezekiël]

Hi Roger,

I already know that, but how to use it on 100 fields? I don't want to type
it in for each field.

 

[RobFMS]

You will probably need to go through the Controls Collection.
Rather than try giving you an elaborate piece of code, take a look at the
help file. Look at the examples they provide. If you are still stuck with
that, then repost with more specifics and we can get you through it.

With the Controls collection, you can specify which controls you want to
check the character length.

HTH

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Roger Carlson]

How you implement it depends on what you are doing. Is it controls on a
form as Rob suggests? Or do you want to do it in a query? Or do you want
to open a recordset in code and add them up there? And what do you want to
do with it (save the number, display it, etc), once you've calculated it?

Though I've not tested it, I would try marching through the Controls
collection if it is on a form (as Rob said). In a query, you could use the
Fields collection of the QueryDef object. And in a recordset, you could use
the Fields collection of the QueryDef or TableDef object.

 

[Ron Weiner]

In addition to Rob's and Roger's advice. If you need to do this for a
number of records in a table or a set of joined tables, you may want to
consider creating a Query or a Stored Procedure that computes the length of
each field for a set of records as specified in the Where clause. To get
the fastest method you should be prepared to do some testing using all of
the methods discussed here.

Ron W

 

[Ezekiël]

Hi Roger,

I want it in a query, by using a code is, so i can export it to excel. Can
you help me out here, it tried to manage it but my skills are lacking.

Thnx

 

[Roger Carlson]

Maybe I'm slow, but I don't still visualize what you're trying to do. Can
you give an example, including table, query, and field names as well as a
few records of data and what the expected output should look like? It
doesn't have to be the whole thing, just 3 fields and 3 records or so.
Nothing elaborate.

 

[Ezekiël]

Hi Roger,

What i have is a table with 200 fields where i want to count the number of
characters. I would like to have the results in a query so i can export it
to excel.

The things is, with my lacking vba skills, i must set the len() for 200
fields and that would take me all day to complete. I know it can be done
quicker with a vba code and loop through all the fields, but i don't know
how to do that.

Can you help me out?

 

[What-a-Tool]

This is one method to get your results using VBA
There are are lot of people in this group with a lot more experience than me who could probably improve on this method, but here is a place for you to start.

If you need to know the length of each and every field, you could add them to a collection for later processing, or put your message box in the Do-Loop, or put them in another field of your table, or whatever.

Private Sub btnCount_Click()
Dim rst As New ADODB.Recordset
Dim con As Connection
Set con = CurrentProject.Connection
Dim fld As Field

Dim intCount As Integer, lngSum, lngSum2 As Long

rst.Open "yourtablename", con, adOpenKeyset, adLockOptimistic, adCmdTableDirect

'Gets count of records in table (unnecessary - just felt like it)
intCount = rst.RecordCount

'Loops thru all rows till end of file
Do While Not rst.EOF

'Gets length of field by column(field) position
lngSum = lngSum + Len(rst.Fields.Item(0))

'Or loop thru all columns(fields) in table until you get to the one with the
' field name you are looking for
For Each fld In rst.Fields
If fld.Name = "yourfieldname" Then
lngSum2 = lngSum2 + Len(fld.Value)
Exit For
End If
Next fld

'Move to next record
rst.MoveNext
Loop

'Display summed results in message box, row count as title
MsgBox CStr(lngSum) & " - By Field Position" & Chr(13) & _
CStr(lngSum2) & " - By Field Name", , CStr(intCount) & " - Row Count"

End Sub


This will work as long as there are no null fields in your table that you are trying to find a length for, else, test for null:
If Not IsNull(fld.value) Then
lngSum2 = lngSum2 + Len(fld.Value)
End If




--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

 

[Ezekiël]

Hi Sean,

Thx for the example code, i'm going to try it out. Also i've read the code where it searches the field i want. Is it also possible to look it up in another table? I've a table with a field containing the fieldnames as values.


This is one method to get your results using VBA
There are are lot of people in this group with a lot more experience than me who could probably improve on this method, but here is a place for you to start.

If you need to know the length of each and every field, you could add them to a collection for later processing, or put your message box in the Do-Loop, or put them in another field of your table, or whatever.

Private Sub btnCount_Click()
Dim rst As New ADODB.Recordset
Dim con As Connection
Set con = CurrentProject.Connection
Dim fld As Field

Dim intCount As Integer, lngSum, lngSum2 As Long

rst.Open "yourtablename", con, adOpenKeyset, adLockOptimistic, adCmdTableDirect

'Gets count of records in table (unnecessary - just felt like it)
intCount = rst.RecordCount

'Loops thru all rows till end of file
Do While Not rst.EOF

'Gets length of field by column(field) position
lngSum = lngSum + Len(rst.Fields.Item(0))

'Or loop thru all columns(fields) in table until you get to the one with the
' field name you are looking for
For Each fld In rst.Fields
If fld.Name = "yourfieldname" Then
lngSum2 = lngSum2 + Len(fld.Value)
Exit For
End If
Next fld

'Move to next record
rst.MoveNext
Loop

'Display summed results in message box, row count as title
MsgBox CStr(lngSum) & " - By Field Position" & Chr(13) & _
CStr(lngSum2) & " - By Field Name", , CStr(intCount) & " - Row Count"

End Sub


This will work as long as there are no null fields in your table that you are trying to find a length for, else, test for null:
If Not IsNull(fld.value) Then
lngSum2 = lngSum2 + Len(fld.Value)
End If




--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

 

[What-a-Tool]

Not sure exactly what you mean

--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

Hi Sean,

Thx for the example code, i'm going to try it out. Also i've read the code where it searches the field i want. Is it also possible to look it up in another table? I've a table with a field containing the fieldnames as values.


This is one method to get your results using VBA
There are are lot of people in this group with a lot more experience than me who could probably improve on this method, but here is a place for you to start.

If you need to know the length of each and every field, you could add them to a collection for later processing, or put your message box in the Do-Loop, or put them in another field of your table, or whatever.

Private Sub btnCount_Click()
Dim rst As New ADODB.Recordset
Dim con As Connection
Set con = CurrentProject.Connection
Dim fld As Field

Dim intCount As Integer, lngSum, lngSum2 As Long

rst.Open "yourtablename", con, adOpenKeyset, adLockOptimistic, adCmdTableDirect

'Gets count of records in table (unnecessary - just felt like it)
intCount = rst.RecordCount

'Loops thru all rows till end of file
Do While Not rst.EOF

'Gets length of field by column(field) position
lngSum = lngSum + Len(rst.Fields.Item(0))

'Or loop thru all columns(fields) in table until you get to the one with the
' field name you are looking for
For Each fld In rst.Fields
If fld.Name = "yourfieldname" Then
lngSum2 = lngSum2 + Len(fld.Value)
Exit For
End If
Next fld

'Move to next record
rst.MoveNext
Loop

'Display summed results in message box, row count as title
MsgBox CStr(lngSum) & " - By Field Position" & Chr(13) & _
CStr(lngSum2) & " - By Field Name", , CStr(intCount) & " - Row Count"

End Sub


This will work as long as there are no null fields in your table that you are trying to find a length for, else, test for null:
If Not IsNull(fld.value) Then
lngSum2 = lngSum2 + Len(fld.Value)
End If




--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

 

[Ezekiël]

Hi Sean,

Sorry for my bad english, i'll try to be more clearer.

What i mean is, the part of your code example:

'Or loop thru all columns(fields) in table until you get to the one with the
' field name you are looking for
For Each fld In rst.Fields
If fld.Name = "yourfieldname" Then
lngSum2 = lngSum2 + Len(fld.Value)
Exit For
End If
Next fld

Can it also lookup the fieldname from another table, where the fieldname is an recordvalue?

The table where i run your code against has fieldnames as fields, but i really only need the ones in the second table.
Not sure exactly what you mean

--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

Hi Sean,

Thx for the example code, i'm going to try it out. Also i've read the code where it searches the field i want. Is it also possible to look it up in another table? I've a table with a field containing the fieldnames as values.


This is one method to get your results using VBA
There are are lot of people in this group with a lot more experience than me who could probably improve on this method, but here is a place for you to start.

If you need to know the length of each and every field, you could add them to a collection for later processing, or put your message box in the Do-Loop, or put them in another field of your table, or whatever.

Private Sub btnCount_Click()
Dim rst As New ADODB.Recordset
Dim con As Connection
Set con = CurrentProject.Connection
Dim fld As Field

Dim intCount As Integer, lngSum, lngSum2 As Long

rst.Open "yourtablename", con, adOpenKeyset, adLockOptimistic, adCmdTableDirect

'Gets count of records in table (unnecessary - just felt like it)
intCount = rst.RecordCount

'Loops thru all rows till end of file
Do While Not rst.EOF

'Gets length of field by column(field) position
lngSum = lngSum + Len(rst.Fields.Item(0))

'Or loop thru all columns(fields) in table until you get to the one with the
' field name you are looking for
For Each fld In rst.Fields
If fld.Name = "yourfieldname" Then
lngSum2 = lngSum2 + Len(fld.Value)
Exit For
End If
Next fld

'Move to next record
rst.MoveNext
Loop

'Display summed results in message box, row count as title
MsgBox CStr(lngSum) & " - By Field Position" & Chr(13) & _
CStr(lngSum2) & " - By Field Name", , CStr(intCount) & " - Row Count"

End Sub


This will work as long as there are no null fields in your table that you are trying to find a length for, else, test for null:
If Not IsNull(fld.value) Then
lngSum2 = lngSum2 + Len(fld.Value)
End If




--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

 

[What-a-Tool]

forgot an important part of this code - place just before "End Sub" :

rst.Close
Set rst = Nothing

--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

This is one method to get your results using VBA
There are are lot of people in this group with a lot more experience than me who could probably improve on this method, but here is a place for you to start.

If you need to know the length of each and every field, you could add them to a collection for later processing, or put your message box in the Do-Loop, or put them in another field of your table, or whatever.

Private Sub btnCount_Click()
Dim rst As New ADODB.Recordset
Dim con As Connection
Set con = CurrentProject.Connection
Dim fld As Field

Dim intCount As Integer, lngSum, lngSum2 As Long

rst.Open "yourtablename", con, adOpenKeyset, adLockOptimistic, adCmdTableDirect

'Gets count of records in table (unnecessary - just felt like it)
intCount = rst.RecordCount

'Loops thru all rows till end of file
Do While Not rst.EOF

'Gets length of field by column(field) position
lngSum = lngSum + Len(rst.Fields.Item(0))

'Or loop thru all columns(fields) in table until you get to the one with the
' field name you are looking for
For Each fld In rst.Fields
If fld.Name = "yourfieldname" Then
lngSum2 = lngSum2 + Len(fld.Value)
Exit For
End If
Next fld

'Move to next record
rst.MoveNext
Loop

'Display summed results in message box, row count as title
MsgBox CStr(lngSum) & " - By Field Position" & Chr(13) & _
CStr(lngSum2) & " - By Field Name", , CStr(intCount) & " - Row Count"

End Sub


This will work as long as there are no null fields in your table that you are trying to find a length for, else, test for null:
If Not IsNull(fld.value) Then
lngSum2 = lngSum2 + Len(fld.Value)
End If




--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

 

[What-a-Tool]

First, rst is a recordset object that has been filled with the data from
your table with the <rst.open "yourtable" ...>
method. There is nothing in it but whatever is in the table "yourtable".
So when you search thru the fields in rst <For Each fld In rst.Fields>
you are only searching thru the fields in "yourtable".

I Still don't understand exactly what you are trying to do - You have a
table that has nothing in it but the names of the fields from another
table!?

There are a number of different ways to access the data from two different
tables. Seems like going into VB is probably the most complicated, though.
Can't you do a query or report to get the data you want?
Or are you just trying to teach yourself VB from what you get here? If thats
it, I will help as I am able. Being a student myself, I know how frustrating
it can be trying to figure something like this out on your own.

If you have a field in one table and you want to retrieve all rows with a
matching field in another table, you can use the <GetRows> methos
(
array = recordset.GetRows( Rows, Start, Fields ))

or if you want to look-up one record from a table you can use the DLookup
method
(
expression.DLookup(Expr, Domain, Criteria)

)

Examples of both are available in Help.


--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

Hi Sean,

Sorry for my bad english, i'll try to be more clearer.

What i mean is, the part of your code example:

'Or loop thru all columns(fields) in table until you get to the one
with the
' field name you are looking for
For Each fld In rst.Fields
If fld.Name = "yourfieldname" Then
lngSum2 = lngSum2 + Len(fld.Value)
Exit For
End If
Next fld

Can it also lookup the fieldname from another table, where the fieldname is
an recordvalue?

The table where i run your code against has fieldnames as fields, but i
really only need the ones in the second table.
Not sure exactly what you mean

--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

Hi Sean,

Thx for the example code, i'm going to try it out. Also i've read the code
where it searches the field i want. Is it also possible to look it up in
another table? I've a table with a field containing the fieldnames as
values.


This is one method to get your results using VBA
There are are lot of people in this group with a lot more experience than me
who could probably improve on this method, but here is a place for you to
start.

If you need to know the length of each and every field, you could add them
to a collection for later processing, or put your message box in the
Do-Loop, or put them in another field of your table, or whatever.

Private Sub btnCount_Click()
Dim rst As New ADODB.Recordset
Dim con As Connection
Set con = CurrentProject.Connection
Dim fld As Field

Dim intCount As Integer, lngSum, lngSum2 As Long

rst.Open "yourtablename", con, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

'Gets count of records in table (unnecessary - just felt like it)
intCount = rst.RecordCount

'Loops thru all rows till end of file
Do While Not rst.EOF

'Gets length of field by column(field) position
lngSum = lngSum + Len(rst.Fields.Item(0))

'Or loop thru all columns(fields) in table until you get to the one
with the
' field name you are looking for
For Each fld In rst.Fields
If fld.Name = "yourfieldname" Then
lngSum2 = lngSum2 + Len(fld.Value)
Exit For
End If
Next fld

'Move to next record
rst.MoveNext
Loop

'Display summed results in message box, row count as title
MsgBox CStr(lngSum) & " - By Field Position" & Chr(13) & _
CStr(lngSum2) & " - By Field Name", , CStr(intCount) & " - Row
Count"

End Sub


This will work as long as there are no null fields in your table that you
are trying to find a length for, else, test for null:
If Not IsNull(fld.value) Then
lngSum2 = lngSum2 + Len(fld.Value)
End If




--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

 

[Ezekiël]

Sean,

Thx for your willingness to help me. You're right about teaching myself vba, so yeah it can be hard sometimes.

Anyway my first table has records and the second table has also records, but the values more like the definitions of a table.

In table 1 i have something like this:

ID q1 q2 q3
1 10 LA CA
2 11 SA CA

In table 2 i have something like this:

Tablename Fieldname Length Type
table1 q1 10 number
table1 q2 2 text
table1 q3 2 text

What i also would like to do, is to get the records and columns in table1 based on table2


forgot an important part of this code - place just before "End Sub" :

rst.Close
Set rst = Nothing

--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

This is one method to get your results using VBA
There are are lot of people in this group with a lot more experience than me who could probably improve on this method, but here is a place for you to start.

If you need to know the length of each and every field, you could add them to a collection for later processing, or put your message box in the Do-Loop, or put them in another field of your table, or whatever.

Private Sub btnCount_Click()
Dim rst As New ADODB.Recordset
Dim con As Connection
Set con = CurrentProject.Connection
Dim fld As Field

Dim intCount As Integer, lngSum, lngSum2 As Long

rst.Open "yourtablename", con, adOpenKeyset, adLockOptimistic, adCmdTableDirect

'Gets count of records in table (unnecessary - just felt like it)
intCount = rst.RecordCount

'Loops thru all rows till end of file
Do While Not rst.EOF

'Gets length of field by column(field) position
lngSum = lngSum + Len(rst.Fields.Item(0))

'Or loop thru all columns(fields) in table until you get to the one with the
' field name you are looking for
For Each fld In rst.Fields
If fld.Name = "yourfieldname" Then
lngSum2 = lngSum2 + Len(fld.Value)
Exit For
End If
Next fld

'Move to next record
rst.MoveNext
Loop

'Display summed results in message box, row count as title
MsgBox CStr(lngSum) & " - By Field Position" & Chr(13) & _
CStr(lngSum2) & " - By Field Name", , CStr(intCount) & " - Row Count"

End Sub


This will work as long as there are no null fields in your table that you are trying to find a length for, else, test for null:
If Not IsNull(fld.value) Then
lngSum2 = lngSum2 + Len(fld.Value)
End If




--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

 

[What-a-Tool]

Here is one of several possible ways to do this - and not necessarily the best. Like I said before, there are many people in this group with much more experience than me who could probably improve on this.

Good luck. With what I've given you so far and use of the help files, you should (hopefully!?) be golden from here on out.


Private Sub btnProcess_Click()
Dim rstTBL1 As New ADODB.Recordset
Dim rstTBL2 As New ADODB.Recordset
Dim con As Connection
Set con = CurrentProject.Connection
Dim strFieldName, strTableName, strSQL As String

'Open recordset containing values from Table2
rstTBL2.Open "Table2", con, adOpenKeyset, adLockOptimistic, adCmdTableDirect

'Loops thru all rows in Table2
Do While Not rstTBL2.EOF
'Gets contents of fldTableName and fldFieldName for current row
strTableName = rstTBL2.Fields.Item(0)
strFieldName = rstTBL2.Fields.Item(1)

'Creates an SQL query using field and table names
strSQL = "SELECT " & strTableName & "." & strFieldName & " FROM " & strTableName & ";"
'Open 2nd recordset which will be filled with the query just created by
' above SQL statement. This recordset will contain ALL records from
' currently named field
rstTBL1.Open strSQL, con, adOpenStatic, adLockReadOnly, adCmdText

'Loop thru all records in new recordset
Do While Not rstTBL1.EOF
MsgBox rstTBL1.Fields.Item(0), , strTableName & "." & strFieldName
'Move to next record
rstTBL1.MoveNext
Loop

'Make sure you close this recordset, because you won't be able to
' open it again for the next record in Table2 if you don't
rstTBL1.Close
Set rstTBL1 = Nothing

'Move to next record
rstTBL2.MoveNext

Loop

'Close recordset containing Table2 data
rstTBL2.Close
Set rstTBL2 = Nothing

End Sub

--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

Sean,

Thx for your willingness to help me. You're right about teaching myself vba, so yeah it can be hard sometimes.

Anyway my first table has records and the second table has also records, but the values more like the definitions of a table.

In table 1 i have something like this:

ID q1 q2 q3
1 10 LA CA
2 11 SA CA

In table 2 i have something like this:

Tablename Fieldname Length Type
table1 q1 10 number
table1 q2 2 text
table1 q3 2 text

What i also would like to do, is to get the records and columns in table1 based on table2


forgot an important part of this code - place just before "End Sub" :

rst.Close
Set rst = Nothing

--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

This is one method to get your results using VBA
There are are lot of people in this group with a lot more experience than me who could probably improve on this method, but here is a place for you to start.

If you need to know the length of each and every field, you could add them to a collection for later processing, or put your message box in the Do-Loop, or put them in another field of your table, or whatever.

Private Sub btnCount_Click()
Dim rst As New ADODB.Recordset
Dim con As Connection
Set con = CurrentProject.Connection
Dim fld As Field

Dim intCount As Integer, lngSum, lngSum2 As Long

rst.Open "yourtablename", con, adOpenKeyset, adLockOptimistic, adCmdTableDirect

'Gets count of records in table (unnecessary - just felt like it)
intCount = rst.RecordCount

'Loops thru all rows till end of file
Do While Not rst.EOF

'Gets length of field by column(field) position
lngSum = lngSum + Len(rst.Fields.Item(0))

'Or loop thru all columns(fields) in table until you get to the one with the
' field name you are looking for
For Each fld In rst.Fields
If fld.Name = "yourfieldname" Then
lngSum2 = lngSum2 + Len(fld.Value)
Exit For
End If
Next fld

'Move to next record
rst.MoveNext
Loop

'Display summed results in message box, row count as title
MsgBox CStr(lngSum) & " - By Field Position" & Chr(13) & _
CStr(lngSum2) & " - By Field Name", , CStr(intCount) & " - Row Count"

End Sub


This will work as long as there are no null fields in your table that you are trying to find a length for, else, test for null:
If Not IsNull(fld.value) Then
lngSum2 = lngSum2 + Len(fld.Value)
End If




--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

 

[Ezekiël]

Hi Sean,

Thx for the help, i'm going to try it out.
Here is one of several possible ways to do this - and not necessarily the best. Like I said before, there are many people in this group with much more experience than me who could probably improve on this.

Good luck. With what I've given you so far and use of the help files, you should (hopefully!?) be golden from here on out.


Private Sub btnProcess_Click()
Dim rstTBL1 As New ADODB.Recordset
Dim rstTBL2 As New ADODB.Recordset
Dim con As Connection
Set con = CurrentProject.Connection
Dim strFieldName, strTableName, strSQL As String

'Open recordset containing values from Table2
rstTBL2.Open "Table2", con, adOpenKeyset, adLockOptimistic, adCmdTableDirect

'Loops thru all rows in Table2
Do While Not rstTBL2.EOF
'Gets contents of fldTableName and fldFieldName for current row
strTableName = rstTBL2.Fields.Item(0)
strFieldName = rstTBL2.Fields.Item(1)

'Creates an SQL query using field and table names
strSQL = "SELECT " & strTableName & "." & strFieldName & " FROM " & strTableName & ";"
'Open 2nd recordset which will be filled with the query just created by
' above SQL statement. This recordset will contain ALL records from
' currently named field
rstTBL1.Open strSQL, con, adOpenStatic, adLockReadOnly, adCmdText

'Loop thru all records in new recordset
Do While Not rstTBL1.EOF
MsgBox rstTBL1.Fields.Item(0), , strTableName & "." & strFieldName
'Move to next record
rstTBL1.MoveNext
Loop

'Make sure you close this recordset, because you won't be able to
' open it again for the next record in Table2 if you don't
rstTBL1.Close
Set rstTBL1 = Nothing

'Move to next record
rstTBL2.MoveNext

Loop

'Close recordset containing Table2 data
rstTBL2.Close
Set rstTBL2 = Nothing

End Sub

--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

Sean,

Thx for your willingness to help me. You're right about teaching myself vba, so yeah it can be hard sometimes.

Anyway my first table has records and the second table has also records, but the values more like the definitions of a table.

In table 1 i have something like this:

ID q1 q2 q3
1 10 LA CA
2 11 SA CA

In table 2 i have something like this:

Tablename Fieldname Length Type
table1 q1 10 number
table1 q2 2 text
table1 q3 2 text

What i also would like to do, is to get the records and columns in table1 based on table2


forgot an important part of this code - place just before "End Sub" :

rst.Close
Set rst = Nothing

--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

This is one method to get your results using VBA
There are are lot of people in this group with a lot more experience than me who could probably improve on this method, but here is a place for you to start.

If you need to know the length of each and every field, you could add them to a collection for later processing, or put your message box in the Do-Loop, or put them in another field of your table, or whatever.

Private Sub btnCount_Click()
Dim rst As New ADODB.Recordset
Dim con As Connection
Set con = CurrentProject.Connection
Dim fld As Field

Dim intCount As Integer, lngSum, lngSum2 As Long

rst.Open "yourtablename", con, adOpenKeyset, adLockOptimistic, adCmdTableDirect

'Gets count of records in table (unnecessary - just felt like it)
intCount = rst.RecordCount

'Loops thru all rows till end of file
Do While Not rst.EOF

'Gets length of field by column(field) position
lngSum = lngSum + Len(rst.Fields.Item(0))

'Or loop thru all columns(fields) in table until you get to the one with the
' field name you are looking for
For Each fld In rst.Fields
If fld.Name = "yourfieldname" Then
lngSum2 = lngSum2 + Len(fld.Value)
Exit For
End If
Next fld

'Move to next record
rst.MoveNext
Loop

'Display summed results in message box, row count as title
MsgBox CStr(lngSum) & " - By Field Position" & Chr(13) & _
CStr(lngSum2) & " - By Field Name", , CStr(intCount) & " - Row Count"

End Sub


This will work as long as there are no null fields in your table that you are trying to find a length for, else, test for null:
If Not IsNull(fld.value) Then
lngSum2 = lngSum2 + Len(fld.Value)
End If




--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

 

[Ezekiël]

Sean,

Do you also have a method to count the words in a field?
Here is one of several possible ways to do this - and not necessarily the best. Like I said before, there are many people in this group with much more experience than me who could probably improve on this.

Good luck. With what I've given you so far and use of the help files, you should (hopefully!?) be golden from here on out.


Private Sub btnProcess_Click()
Dim rstTBL1 As New ADODB.Recordset
Dim rstTBL2 As New ADODB.Recordset
Dim con As Connection
Set con = CurrentProject.Connection
Dim strFieldName, strTableName, strSQL As String

'Open recordset containing values from Table2
rstTBL2.Open "Table2", con, adOpenKeyset, adLockOptimistic, adCmdTableDirect

'Loops thru all rows in Table2
Do While Not rstTBL2.EOF
'Gets contents of fldTableName and fldFieldName for current row
strTableName = rstTBL2.Fields.Item(0)
strFieldName = rstTBL2.Fields.Item(1)

'Creates an SQL query using field and table names
strSQL = "SELECT " & strTableName & "." & strFieldName & " FROM " & strTableName & ";"
'Open 2nd recordset which will be filled with the query just created by
' above SQL statement. This recordset will contain ALL records from
' currently named field
rstTBL1.Open strSQL, con, adOpenStatic, adLockReadOnly, adCmdText

'Loop thru all records in new recordset
Do While Not rstTBL1.EOF
MsgBox rstTBL1.Fields.Item(0), , strTableName & "." & strFieldName
'Move to next record
rstTBL1.MoveNext
Loop

'Make sure you close this recordset, because you won't be able to
' open it again for the next record in Table2 if you don't
rstTBL1.Close
Set rstTBL1 = Nothing

'Move to next record
rstTBL2.MoveNext

Loop

'Close recordset containing Table2 data
rstTBL2.Close
Set rstTBL2 = Nothing

End Sub

--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

Sean,

Thx for your willingness to help me. You're right about teaching myself vba, so yeah it can be hard sometimes.

Anyway my first table has records and the second table has also records, but the values more like the definitions of a table.

In table 1 i have something like this:

ID q1 q2 q3
1 10 LA CA
2 11 SA CA

In table 2 i have something like this:

Tablename Fieldname Length Type
table1 q1 10 number
table1 q2 2 text
table1 q3 2 text

What i also would like to do, is to get the records and columns in table1 based on table2


forgot an important part of this code - place just before "End Sub" :

rst.Close
Set rst = Nothing

--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

This is one method to get your results using VBA
There are are lot of people in this group with a lot more experience than me who could probably improve on this method, but here is a place for you to start.

If you need to know the length of each and every field, you could add them to a collection for later processing, or put your message box in the Do-Loop, or put them in another field of your table, or whatever.

Private Sub btnCount_Click()
Dim rst As New ADODB.Recordset
Dim con As Connection
Set con = CurrentProject.Connection
Dim fld As Field

Dim intCount As Integer, lngSum, lngSum2 As Long

rst.Open "yourtablename", con, adOpenKeyset, adLockOptimistic, adCmdTableDirect

'Gets count of records in table (unnecessary - just felt like it)
intCount = rst.RecordCount

'Loops thru all rows till end of file
Do While Not rst.EOF

'Gets length of field by column(field) position
lngSum = lngSum + Len(rst.Fields.Item(0))

'Or loop thru all columns(fields) in table until you get to the one with the
' field name you are looking for
For Each fld In rst.Fields
If fld.Name = "yourfieldname" Then
lngSum2 = lngSum2 + Len(fld.Value)
Exit For
End If
Next fld

'Move to next record
rst.MoveNext
Loop

'Display summed results in message box, row count as title
MsgBox CStr(lngSum) & " - By Field Position" & Chr(13) & _
CStr(lngSum2) & " - By Field Name", , CStr(intCount) & " - Row Count"

End Sub


This will work as long as there are no null fields in your table that you are trying to find a length for, else, test for null:
If Not IsNull(fld.value) Then
lngSum2 = lngSum2 + Len(fld.Value)
End If




--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

 

[What-a-Tool]

Could you use the Mid() function to count the spaces (" ") in your string?

--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

Sean,

Do you also have a method to count the words in a field?
Here is one of several possible ways to do this - and not necessarily the best. Like I said before, there are many people in this group with much more experience than me who could probably improve on this.

Good luck. With what I've given you so far and use of the help files, you should (hopefully!?) be golden from here on out.


Private Sub btnProcess_Click()
Dim rstTBL1 As New ADODB.Recordset
Dim rstTBL2 As New ADODB.Recordset
Dim con As Connection
Set con = CurrentProject.Connection
Dim strFieldName, strTableName, strSQL As String

'Open recordset containing values from Table2
rstTBL2.Open "Table2", con, adOpenKeyset, adLockOptimistic, adCmdTableDirect

'Loops thru all rows in Table2
Do While Not rstTBL2.EOF
'Gets contents of fldTableName and fldFieldName for current row
strTableName = rstTBL2.Fields.Item(0)
strFieldName = rstTBL2.Fields.Item(1)

'Creates an SQL query using field and table names
strSQL = "SELECT " & strTableName & "." & strFieldName & " FROM " & strTableName & ";"
'Open 2nd recordset which will be filled with the query just created by
' above SQL statement. This recordset will contain ALL records from
' currently named field
rstTBL1.Open strSQL, con, adOpenStatic, adLockReadOnly, adCmdText

'Loop thru all records in new recordset
Do While Not rstTBL1.EOF
MsgBox rstTBL1.Fields.Item(0), , strTableName & "." & strFieldName
'Move to next record
rstTBL1.MoveNext
Loop

'Make sure you close this recordset, because you won't be able to
' open it again for the next record in Table2 if you don't
rstTBL1.Close
Set rstTBL1 = Nothing

'Move to next record
rstTBL2.MoveNext

Loop

'Close recordset containing Table2 data
rstTBL2.Close
Set rstTBL2 = Nothing

End Sub

--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

Sean,

Thx for your willingness to help me. You're right about teaching myself vba, so yeah it can be hard sometimes.

Anyway my first table has records and the second table has also records, but the values more like the definitions of a table.

In table 1 i have something like this:

ID q1 q2 q3
1 10 LA CA
2 11 SA CA

In table 2 i have something like this:

Tablename Fieldname Length Type
table1 q1 10 number
table1 q2 2 text
table1 q3 2 text

What i also would like to do, is to get the records and columns in table1 based on table2


forgot an important part of this code - place just before "End Sub" :

rst.Close
Set rst = Nothing

--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

This is one method to get your results using VBA
There are are lot of people in this group with a lot more experience than me who could probably improve on this method, but here is a place for you to start.

If you need to know the length of each and every field, you could add them to a collection for later processing, or put your message box in the Do-Loop, or put them in another field of your table, or whatever.

Private Sub btnCount_Click()
Dim rst As New ADODB.Recordset
Dim con As Connection
Set con = CurrentProject.Connection
Dim fld As Field

Dim intCount As Integer, lngSum, lngSum2 As Long

rst.Open "yourtablename", con, adOpenKeyset, adLockOptimistic, adCmdTableDirect

'Gets count of records in table (unnecessary - just felt like it)
intCount = rst.RecordCount

'Loops thru all rows till end of file
Do While Not rst.EOF

'Gets length of field by column(field) position
lngSum = lngSum + Len(rst.Fields.Item(0))

'Or loop thru all columns(fields) in table until you get to the one with the
' field name you are looking for
For Each fld In rst.Fields
If fld.Name = "yourfieldname" Then
lngSum2 = lngSum2 + Len(fld.Value)
Exit For
End If
Next fld

'Move to next record
rst.MoveNext
Loop

'Display summed results in message box, row count as title
MsgBox CStr(lngSum) & " - By Field Position" & Chr(13) & _
CStr(lngSum2) & " - By Field Name", , CStr(intCount) & " - Row Count"

End Sub


This will work as long as there are no null fields in your table that you are trying to find a length for, else, test for null:
If Not IsNull(fld.value) Then
lngSum2 = lngSum2 + Len(fld.Value)
End If




--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)