Access VB and SQL - Counting Characters

Hi,

In an online chat conversations database I need to be able to work out which user has sent the longest message and which user has sent the shortest message.

I have a table called 'Message' which holds User, Message, Time etc about messages in a conversation. I need to be able to count all the characters in the Message field and retrieve the longest message and the shortest message ?! This information then needs to be displayed on an form.

I also need to do the same for words, I need to count the number of characters for each word and retrieve the longest word and the shortest word ?!

Would anyone have any ideas about how to do this?

Please help!

Most DB's support a LENGTH or LEN function for text data, one example for sql server:

SELECT whatever FROM table ORDER BY LEN(Message) DESC

Hey,

Thanks for that info it helped but I have now found another problem that I would like to request some advise upon..

Based on the LEN function I first tried :

SELECT User.User, Message.Message
FROM Message INNERJOIN [User] ON Message.[Message ID] = User.[Message ID]
ORDER BY LEN(Message) DESC;

This selected the user and message fields and ordered them based on the length of the message.

In the original Message Table the message field is set to Memo as there are quite long messages the above SQL did incorporate the full messages and ordered them in the correct manner...

HOWEVER.. I need to be able to count the number of characters per message and retrieve the longest message and then retrieve the shortest message.

I tried..

SELECT Message.Message, LEN(Message) AS [No of characters]
FROM Message INNERJOIN [User] ON Message.[Message ID] = User.[Message ID]GROUP BY Message.Message;

This counted the number of characters for each message but it did not incorporate the full messages, the maximum number of characters for the longer messages came up as 255 whereas I know that they are longer than that.. and it only displayed those 255 characters and cut off the rest of the message.

Ultimately I need to select the 'User' from the user table, 'Message' from the message table, Count the number of characters per message and then display the highest but the function needs to be aware of the messages that have characters more than 255 hence the Memo data type setting in the Message Table..

... any ideas/developments on the above??

I have been trying a few different things but getting no-where at the moment..