M
Michel Walsh
Hi,
Len( MyString ) - Len( Replace(MyString, " ", "" ))
would return the number of spaces.
Hoping it may help,
Vanderghast, Access MVP
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?
message 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
message 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)
message 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)
Len( MyString ) - Len( Replace(MyString, " ", "" ))
would return the number of spaces.
Hoping it may help,
Vanderghast, Access MVP
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?
message 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
message 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)
message 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)