VB and SQL in Access

Hey all.. I know I have posted this thread before but I hadnt received any feedback on it.. I was wondering if anyone would be able to help because I am really stuck with this..

Basically in an Access Database holding messages of online chat conversations I need to run a query to find out which is the longest message and which is the shortest message sent. In order to this the number of characters need to be counted and then the max and min selected however I'm having problems with the data type of the message

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..

Any help would be greatly appreciated.

Shouldn't it be

SELECT Message.Message, LEN(Message.[Message]) AS [No of characters]

Just guessing, I don't know how (or if) Access handles a sql LEN function.

Hey,

That didnt seem to change anything, I am still having the same problem.

The SQL that I had works fine and counts the characters. The only thing it makes a mistake on is when its counting the larger messages which are greater than 255 characters. It seems to cut the messages after 255 characters and so only displays some of the message and No of Characters is displayed as 255.

The data type on the table is set to memo.. but not sure how to get round this error..

Does any body know anything about this?

Having said that.. is there another way to do the same thing but in VB ?? Perhaps it could be linked that way..

Please advise if possible, Im even newer to VB than I am to SQL so would be great if someone could be of assistance

Many thanks
-x-

Hi,

Have you tried to convert the memo field to a "normal" string in the SQL? That generally solved any problem in my situations (ranged from simple VB programs to complex DB2 Datawarehousing to Impromptu reporting).

Thing is, convert it so something that is easy to tackle and you should be fine.

Grtz.©

M.

Are you sure your access column is a Memo type and not a Text type?

I don't have any problems with VB6 and long strings from memo fields.

Hi,

Yes it is definatly set to Memo, even I checked it many times to make sure as I could not make any sense of it.

I didnt quite understand how to 'convert the memo field to a "normal" string in the SQL'

In the SQL statements there are no mentions of the data type and I cant change it to anything else on the table design because it wont hold the full messages..

Please advise

Hi,

try using Format(<fieldname>) as Memo_Formated in your SQL. That is if you use an Access Database. If you are going to query a SQL database, use CAST or CONVERT.

Grtz.©

M.

Where would 'Format(Message) as Memo_Formated' fit into the SQL...

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

Yes I am using MS Access and tried putting the above statement into the SQL that I have and I get the error 'Syntax error (missing operator) in query...'

Try this:



Grtz.©

M.

Thanks.. but no.. still doesnt work.. It still cuts the longer messages at 255..

I had a reply from another forum who managed to solve this.. The problem in my code was that I had 'Group by Message' which apparently never works.. by removing that line it worked perfectly!

The code that works is :

Query name : 'length'

SELECT User.User, Message.Message, LEN(Message) AS [No of characters]
FROM Message INNER JOIN [User] ON Message.[Message ID]=User.[Message ID];

It counts the number of characters per message and displays the full message with the correct number of characters (even for the messages that go over 255!)

The reason for doing all this was that I needed to extract the longest and the shortest message.. I have now been able to do that so thought I would share it with anyone interested :

Max_length :

SELECT length.User, length.Message, (length.[No of characters]) AS Expr1
FROM length
WHERE (((length.[No of characters])>=All
(SELECT [No of characters]
FROM length)));


Max_length query extracts the maximum 'No of characters' from the 'length' query and displays the username, message and number of characters.

Min_length works in the same way :

SELECT length.User, length.Message, (length.[No of characters])
FROM length
WHERE [No of characters] <= ALL
(SELECT [No of characters]
FROM length);


Thanks to everyone who responded and offered their help I really appreaciate it
-x-

I had a reply from another forum who managed to solve this.. The problem in my code was that I had 'Group by Message' which apparently never works.. by removing that line it worked perfectly!

The code that works is :

Query name : 'length'

SELECT User.User, Message.Message, LEN(Message) AS [No of characters]
FROM Message INNER JOIN [User] ON Message.[Message ID]=User.[Message ID];

It counts the number of characters per message and displays the full message with the correct number of characters (even for the messages that go over 255!)

The reason for doing all this was that I needed to extract the longest and the shortest message.. I have now been able to do that so thought I would share it with anyone interested :

Max_length :

SELECT length.User, length.Message, (length.[No of characters]) AS Expr1
FROM length
WHERE (((length.[No of characters])>=All
(SELECT [No of characters]
FROM length)));


Max_length query extracts the maximum 'No of characters' from the 'length' query and displays the username, message and number of characters.

Min_length works in the same way :

SELECT length.User, length.Message, (length.[No of characters])
FROM length
WHERE [No of characters] <= ALL
(SELECT [No of characters]
FROM length);


Thanks to everyone who responded and offered their help I really appreaciate it
-x-