Hi,

In a database holding messages from an online chat system I want to be able to look at all the messages within the conversation and pick out the longest, shortest and average number of characters in a word.

I more or less have it working in VB but I wanted to put it in a module in access and call it using a query and then displaying the results on a form within the database.

I basically want the script to look at the messages and determine the longest and shortest word length and the word it self.. and then the average word length.

I dont know how to get this so that the query take the longest shortest and average word separatly and display it..



Public Function getMessage_Longest_Shortest_Average_Word(Llenth As Integer, Slenth As Integer, Alenth As Integer, message As String)
' THIS FUNCTION TAKE A STRING
' AND THREE INTEGERS "Llenth" LONGEST "Slenth" SHORTEST "Alenth" AVERAGE
' FOR AVERAGE IT ROUNDS IT TO INTEGER
' IT CONSIDERS A SINGLE LETTER AS ONE WORD
' 'I AM' CONSIST OF TWO WORDS

    Dim i As Long
    Dim chrCount, wordCount As Integer
    Dim chr As String
    Dim chrVal As Integer

    Llenth = 0
    Slenth = 0
    Alenth = 0

    message = message & "$"
    For i = 1 To Len(message)
        chr = Mid(message, i, 1)
        chrVal = Asc(chr)
        If chrVal >= 65 And chrVal <= 90 Then
            chrCount = chrCount + 1
        ElseIf chrVal >= 97 And chrVal <= 122 Then
            chrCount = chrCount + 1
        Else
            If Llenth < chrCount Then
                Llenth = chrCount
            End If
            If (Slenth >= chrCount) Or Slenth = 0 Then
                Slenth = chrCount
            End If
            If chrCount > 1 Then
                Alenth = Alenth + chrCount
                wordCount = wordCount + 1
            End If
            chrCount = 0
        End If
    Next
    Alenth = Math.Round(Alenth / wordCount, 0)
End Function


I hope someone can help

-x-

Hi,

   I don't have the solution together, but what I would do is to capture each line, or each message (I am not sure how you are capturing it) and then push the undividual words into an array and then check the Len of each word. Have two other variables, one that holds an integer of the longest word, and one that holds the longest word, for example.

'==========
WordArray = Split(strLine, " ") 'use a space between
'the double quotes to split the array on the space
'between each word.

i = 0
strLongestWord = "I"
For i = 0 To Ubound(WordArray)
   strLongWord = WordArray(i)
      If Len(strLongWord) > Len(strLongestWord) Then
          strLongestWord = strLongWord
          i = i + 1
      End If
Next
'==========

I am not sure if the syntax is exactly right, but this should work. In the same For Next, you can do the strShortestWord, strShortWord. Start with strShortestWord = "the"

HTH





mmcdonal

'==========
WordArray = Split(strLine, " ") 'use a space between
'the double quotes to split the array on the space
'between each word.

i = 0
strLongestWord = "I"
strShortestWord = "the"
For i = 0 To Ubound(WordArray)
   strLongWord = WordArray(i)
   strShortWord = WordArray(i)
      If Len(strLongWord) > Len(strLongestWord) Then
          strLongestWord = strLongWord
      End If
      If Len(strShortWord) < Len(strShortestWord) Then
          strShortestWord = strShortWord
      End If
   i = i + 1
Next
'==========


mmcdonal

Hi,

Thanks for your reply.. but Im still not sure on how I would be able to acheive this..

Is this part of a function..

as well as having the function I would need to be able to have a query that can call the function and display the longest word used after going through all the messages and the same for the shortest word.. and if possible an average word..

?

You would use this as part of your process to parse all the fields in your database. I thought you had that part done.

If you need help with the entire process, I need to know what the database structure is around the specific fields you are parsing since you stated "In a database holding messages from an online chat system... " Is the data already in the database, or are you grbbing it from a log and checking for longest and shortest words?

You would use unbound text boxes on your form to show the longest and shortest words. Make there record source "=Longest()" and "=Shortest()" once you have the modules built. You would not use a query for this.

Thanks,


mmcdonal

ok basically.. I have a database with one Table holding all the info - i know its not great to just have one table but I get data from a user that they extract from an online chat system and they want to run queries on this data. So data is loaded and unloaded into the database from an excel spreadsheet (which is why it was easier to have the excel spreadsheet transfer everything into one table instead of trying to split it up.)

The fields are : Msg ID, User, Message, Time etc..

The database is mainly in Access with SQL queries on VB forms, but there are certain queries i wanted to have in which I couldnt get SQL to do. This is why I have been trying to create modules and then calling the modules from queries. In this way the data is then extracted into a query like the rest of the database and then i can link that to the relevant forms and reports etc. I am not that great with VB VBA and therefore am having problems with this bit.

For this particular problem I wanted to have something that would basically look at all the Message fields in [Table] and determine which was the longest word, shortest word and perhaps average word.

I dont know if you saw the posts about Capital Letters that JPJoe (Jon) helped me with but I wanted it to be something like that. A module that I can call from a query.

does that help you to understand my problem a bit more..

Okay, I wrote this and it works to find the LONGEST Word:

'==========
Function Longest()

Dim db As Database
Dim rs As Recordset
Dim stSQL As String
Dim i As Integer
Dim LongArray As Variant
Dim stLongest As String
Dim stLong As String

stSQL = "SELECT * FROM tblMsg"

stLongest = "I"

Set db = CurrentDb()
Set rs = db.OpenRecordset(stSQL)

rs.MoveFirst

Do While Not rs.EOF
    i = 0
    LongArray = Split(rs!Message, " ")
    For i = LBound(LongArray) To UBound(LongArray)
        stLong = LongArray(i)
        If Len(stLong) > Len(stLongest) Then
            stLongest = stLong
        End If
        i = i + 1
    Next
    rs.MoveNext
Loop

Longest = stLongest

End Function
'==========

Here is the same function for the SHORTEST word:

'==========
Function Shortest()

Dim db As Database
Dim rs As Recordset
Dim stSQL As String
Dim i As Integer
Dim ShortArray As Variant
Dim stShortest As String
Dim stShort As String

stSQL = "SELECT * FROM tblMsg"

stShortest = "the"

Set db = CurrentDb()
Set rs = db.OpenRecordset(stSQL)

rs.MoveFirst

Do While Not rs.EOF
    i = 0
    ShortArray = Split(rs!Message, " ")
    For i = LBound(ShortArray) To UBound(ShortArray)
        stShort = ShortArray(i)
        If Len(stShort) < Len(stShortest) Then
            stShortest = stShort
        End If
        i = i + 1
    Next
    rs.MoveNext
Loop

Shortest = stShortest

End Function
'==========

Give me a second to write the function for the average length of the words...


mmcdonal

Okay, here is the function to find the Average number of characters in all words, rounded to the nearest integer:

'==========
Function Average()

Dim db As Database
Dim rs As Recordset
Dim stSQL As String
Dim i As Integer
Dim AveArray As Variant
Dim stAverage As String
Dim intAverage As Integer
Dim intAveCounter As Integer

stSQL = "SELECT * FROM tblMsg"

intAverage = 0

Set db = CurrentDb()
Set rs = db.OpenRecordset(stSQL)

rs.MoveFirst

Do While Not rs.EOF
    i = 0
    AveArray = Split(rs!Message, " ")
    For i = LBound(AveArray) To UBound(AveArray)
        stAverage = AveArray(i)
            intAverage = intAverage + Len(stAverage)
            intAveCounter = intAveCounter + 1
        i = i + 1
    Next
    rs.MoveNext
Loop

Average = CInt(intAverage / intAveCounter)

End Function
'==========

All you need to do is use your experience to add this to a query. Can you post with what you did and how it works out?

HTH


mmcdonal

Ive been trying to get the Longest Function working but havent been able to ...

I dont know if its because there is something i dont understand with the code.. the kind of layout that i understood was this.. here is the example that i had for the capital letters..

this was in a module..

Function FindUppers(strText As String, WholeWordOnly As Boolean) As Boolean

Dim cLetter As String
Dim cLetterASC As Long
Dim cWord As Boolean

Dim L As Integer

For L = 2 To Len(strText)

    cLetter = Mid(strText, L, 1)
    cLetterASC = Asc(cLetter)

    Select Case cLetterASC
        Case 65 To 90 'Cap Found
            If WholeWordOnly Then

                'And check that the letter is not an I
                If Asc(Mid(strText, i + 1, 1)) <> 73 Then
                    Select Case Asc(Mid(strText, i + 1, 1))
                        Case 32, 33, 44, 45, 46, 47, 58, 59, 63, 95
                            Exit Function
                    End Select
                End If

                L = L + 1 'Next Letter
                'Loop forward through the rest of the word. If a lowercase is found then clear the cWord var
                Do While i < Len(strText)

                    cLetter = Mid(strText, L, 1)
                    cLetterASC = Asc(cLetter)

                    Select Case cLetterASC
                        Case 32, 33, 44, 45, 46, 47, 58, 59, 63, 95
                            L = L - 1 'Move back one letter so Current L is examined (punctuation,space)
                            Exit Do
                        Case Else
                            If cLetterASC >= 65 And cLetterASC <= 90 Then
                                cWord = True
                            Else
                                cWord = False
                                Exit Do
                            End If
                    End Select
                    L = L + 1 'Next Letter
                Loop
            Else 'Find a Partially Capped word with no punctuation preceding and NOT full capped word
                i = L - 1

                'First Check that next letter (+2) is not uppercase
                If Asc(Mid(strText, i + 2, 1)) >= 65 And Asc(Mid(strText, i + 2, 1)) <= 90 Then Exit Function
                'And check thet the letter is not an I
                If Asc(Mid(strText, i + 1, 1)) = 73 Then Exit Function

                Do While i >= 1

                    cLetter = Mid(strText, i, 1)
                    cLetterASC = Asc(cLetter)

                    Select Case cLetterASC
                        Case 32
                            'Do nothing
                        Case 33, 44, 45, 46, 47, 58, 59, 63, 95 'Punctuation
                            Exit Do
                        Case Else
                            cWord = True
                            Exit Do
                    End Select
                    i = i - 1 'Next Letter
                Loop
            End If

        Case 32, 33, 44, 45, 46, 47, 58, 59, 63, 95
            'Check that the cWord variable has not been previously populated (Whole Word)
            If cWord Then
                FindUppers = True
                Exit Function
            End If
    End Select
Next L

If cWord Then FindUppers = True

End Function


and then this was the query :

SELECT FindUppers([Message],True) AS WholeWordOnly, User, Message
FROM [Table]
WHERE (((FindUppers([Message],True))=True));

This called the function on the message field and when it was true.. displayed the results in the query..

can it work like this.. or how do i call up the function you made..

Well, I got this to work with each Function going to one query like this:

SELECT DISTINCT Longest() AS Longest, Shortest() AS Shortest, Average() AS Average
FROM tblMsg;

What you do is open a new query in Design view, don't select any tables and switch to SQL view. Then paste this code in and modify it for your table names etc. This will give you a query that looks like this:

qryStats
Longest Shortest Average
containing I 5

These results are for my database that I made to demo this.

HTH


mmcdonal