#1
  1. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2004
    Posts
    39
    Rep Power
    15

    Unhappy 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.
  2. #2
  3. No Profile Picture
    Grumpier Old Moderator
    ASP Mastermind (5000+ posts)

    Join Date
    Sep 2003
    Posts
    10,143
    Rep Power
    191
    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.
    ======
    Doug G
    ======
    I didn't attend the funeral, but I sent a nice letter saying I approved of it. --Mark Twain
  4. #3
  5. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2004
    Posts
    39
    Rep Power
    15

    Unhappy Not the one


    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?
  6. #4
  7. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2004
    Posts
    39
    Rep Power
    15

    Lightbulb ... another thought!


    Originally Posted by akb786
    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-
  8. #5
  9. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Aug 2004
    Location
    Zaandam, The Netherlands
    Posts
    70
    Rep Power
    15

    Memo, nText, varWchar all the same


    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.
  10. #6
  11. No Profile Picture
    Grumpier Old Moderator
    ASP Mastermind (5000+ posts)

    Join Date
    Sep 2003
    Posts
    10,143
    Rep Power
    191
    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.
  12. #7
  13. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2004
    Posts
    39
    Rep Power
    15

    Question Memo


    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
  14. #8
  15. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Aug 2004
    Location
    Zaandam, The Netherlands
    Posts
    70
    Rep Power
    15

    Cool


    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.&copy;

    M.
  16. #9
  17. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2004
    Posts
    39
    Rep Power
    15

    Thumbs down ?!? still not quite sure


    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...'
  18. #10
  19. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Aug 2004
    Location
    Zaandam, The Netherlands
    Posts
    70
    Rep Power
    15

    Cool


    Try this:

    Code:
    SELECT Message.Message, LEN(Format(Message.Message)) AS [No of characters]
    FROM Message INNER JOIN [User] ON Message.[Message ID]=User.[Message ID]
    GROUP BY Message.Message;
    Grtz.&copy;

    M.
  20. #11
  21. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2004
    Posts
    39
    Rep Power
    15

    Thumbs down unfortunately no..


    Thanks.. but no.. still doesnt work.. It still cuts the longer messages at 255..
  22. #12
  23. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2004
    Posts
    39
    Rep Power
    15

    Thumbs up all is well..


    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-
  24. #13
  25. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2004
    Posts
    39
    Rep Power
    15

    Thumbs up all is well..


    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-

Similar Threads

  1. Cannot establish a connection to SQL Server 2K sp3a remotely
    By ngandy in forum Microsoft SQL Server
    Replies: 6
    Last Post: January 19th, 2005, 03:54 AM
  2. "where" clause problem
    By gilgalbiblewhee in forum ASP Development
    Replies: 6
    Last Post: August 18th, 2004, 10:43 PM
  3. function for dropdown with 3 checkboxes
    By gilgalbiblewhee in forum ASP Development
    Replies: 1
    Last Post: August 18th, 2004, 09:07 PM
  4. Replies: 8
    Last Post: March 26th, 2004, 11:23 AM
  5. insert query
    By jamesjohn in forum ASP Development
    Replies: 1
    Last Post: March 18th, 2004, 12:36 AM

IMN logo majestic logo threadwatch logo seochat tools logo