SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Hybrid View

  1. #1
    SitePoint Enthusiast wavman's Avatar
    Join Date
    Jul 2002
    Location
    Isle of Wight
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Truncated Memo Field output

    Hi,

    I am using two different select statements for two different pages but both selecting on the same table in MS Access 2003. Yet the output of SQLQuery 2 is truncating the output of the file_desc field to 255 chars. Any ideas? I have tried moving the file_data.file_desc field to the end of each section in SQLQuery 2 and that made no difference.

    Cheers.

    SQLQuery 1
    Code:
     SQLQuery = "SELECT * FROM file_data WHERE file_id in( " & fileidVar & ") ORDER BY file_date DESC" 
    SET RsBookmark = MakeRecordSet(cn,SQLQuery)
    SQLQuery 2
    Code:
     SQLQuery = " SELECT filegroup_data.filegroup_id, file_data.file_date, file_data.file_disp_id, file_data.file_title, file_data.file_id, file_data.file_desc, file_data.file_size, file_data.file_associated, file_data.file_type, filegroup_data.filegroup_name"
    SQLQuery = SQLQuery & " FROM (filegroup_data INNER JOIN file_filegroup_link ON filegroup_data.filegroup_id = file_filegroup_link.filegroup_id) INNER JOIN file_data ON file_filegroup_link.file_id = file_data.file_id"
    SQLQuery = SQLQuery & " GROUP BY filegroup_data.filegroup_id, file_data.file_date, file_data.file_disp_id, file_data.file_title, file_data.file_id, file_data.file_desc, file_data.file_size, file_data.file_associated, file_data.file_type, filegroup_data.filegroup_name"
    SQLQuery = SQLQuery & " HAVING (((filegroup_data.filegroup_id)= " & request("group") & "))"
    IF request("action") = "sort" THEN
    SQLQuery = SQLQuery & " ORDER BY " & request("fField") & " " & request("fSort") & ""
    QStringFilter = "&action=sort&fField=" & request("fField") & "&fSort=" & request("fSort") & "&group=" & request("group") & ""
    orderby = request("fField")
    sortby = request("fSort")
    ELSE
    QStringFilter = "&group=" & request("group") & ""
    SQLQuery = SQLQuery & " ORDER BY file_data.file_date DESC"
    END IF
    Set RsFileList = Server.CreateObject("ADODB.Recordset")
    RsFileList.CursorLocation = adUseClient
    RsFileList.PageSize = 20
    RsFileList.Open SQLQuery, cn, adOpenStatic, adLockOptimistic

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please post the code you use to define the connection in your first query.

    I recall that Access wil;l truncate the memo fields if:
    You are using Group By on the memo field. You are using INNER JOIN or DISTINCT.

    There is little need to GROUP BY on the memo field.
    You must rewrite your query.

    HTH

  3. #3
    SitePoint Enthusiast wavman's Avatar
    Join Date
    Jul 2002
    Location
    Isle of Wight
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    The code for the first query is:

    Code:
     Function MakeRecordset(ByRef dbConnection, ByVal tmpSQLQuery)
     set MakeRecordset = Server.CreateObject("ADODB.RecordSet")
     MakeRecordset.Open tmpSQLQuery, dbConnection, 1, 3
    End Function
    I thought if you use "group by" then you had to include all fields? I have removed the group by and replace HAVING with WHERE in the 2nd query which seems to have worked. However I have another query from a different page which needs the group by option as it is using the First() function as well. Or does it need to group? I got an error when replace the group by line in this query so it might need something more. Any ideas?

    Cheers

    Code:
     SQLQuery = "SELECT customer_group_link.customer_id, First(filegroup_data.filegroup_id) AS FirstOffilegroup_id, file_data.file_date, file_data.file_title, file_data.file_id, file_data.file_desc, file_data.file_size, file_data.file_associated, file_data.file_type, file_data.file_disp_id"
    SQLQuery = SQLQuery & " FROM ((((customer_group_link INNER JOIN filegroup_custgroup_link ON customer_group_link.customer_group_id = filegroup_custgroup_link.customer_group_id) INNER JOIN filegroup_data ON filegroup_custgroup_link.product_group_id = filegroup_data.filegroup_id) INNER JOIN file_filegroup_link ON filegroup_data.filegroup_id = file_filegroup_link.filegroup_id) INNER JOIN file_data ON file_filegroup_link.file_id = file_data.file_id) INNER JOIN customer_division_link ON (customer_division_link.div_id = filegroup_data.filegroup_div) AND (customer_group_link.customer_id = customer_division_link.cust_id)"
    SQLQuery = SQLQuery & " GROUP BY customer_group_link.customer_id, file_data.file_date, file_data.file_title, file_data.file_id, file_data.file_desc, file_data.file_size, file_data.file_associated, file_data.file_type, file_data.file_disp_id"
    IF request("action") = "filter" THEN
     IF len(request("fSearch")) > 0 THEN
      SQLQuery = SQLQuery & " HAVING (((customer_group_link.customer_id) = " & session("dlc-custid") & ") AND ((file_data.file_title) Like '%" & request("fSearch") & "%'))"
      IF request("fSearchDesc") = "yes" THEN
       searchDesc = "yes"
       SQLQuery = SQLQuery & "  OR (((customer_group_link.customer_id) = " & session("dlc-custid") & ") AND ((file_data.file_desc) Like '%" & request("fSearch") & "%'))"
      END IF
     ELSE
      SQLQuery = SQLQuery & " HAVING (((customer_group_link.customer_id) = " & session("dlc-custid") & "))"
     END IF
     SQLQuery = SQLQuery & " ORDER BY " & request("fField") & " " & request("fSort") & ""
     QStringFilter = "&action=filter&fsearch=" & request("fsearch") & "&fField=" & request("fField") & "&fSort=" & request("fSort") & ""
     IF searchDesc = "yes" THEN
      QStringFilter = QStringFilter & "&fSearchDesc=yes"
     END IF
     orderby = request("fField")
     sortby = request("fSort")
    ELSE
     SQLQuery = SQLQuery & " HAVING (((customer_group_link.customer_id) = " & session("dlc-custid") & "))"
     SQLQuery = SQLQuery & " ORDER BY file_data.file_date DESC"
    END IF
    Set RsFileList = Server.CreateObject("ADODB.Recordset")
    RsFileList.CursorLocation = adUseClient
    RsFileList.PageSize = 20
    RsFileList.Open SQLQuery, cn, adOpenStatic, adLockOptimistic

  4. #4
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Let me guess, Access created this basic query for you in the query designer?

    If you are using group by, yes, every field in the select list must be a part of an aggregate expression.

    When I said "rewrite", you could have chosen to leave out the memoofield from your original resultset, and later joined it in. Then it wouldn't have been part of the aggregation.

    What do you want to achieve by using first()? It will give you the lowest id in the set. You could also use "order by ASC" amd TOP 1 to retrieve the first value.

  5. #5
    SitePoint Enthusiast wavman's Avatar
    Join Date
    Jul 2002
    Location
    Isle of Wight
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The first is preventing duplicate values from appearing. Where the data is being taken from more than one table and being cross referenced with others I was getting results apearing mulitiple times. Which is when the access query builder came to the rescue and I managed to get the results I wanted.

    So would the best answer be to seperately fetch the file_desc field maybe when I am looping though the results? Surely creating and destroying 20 recordsets per page can't be good. Is there a better way to join it in?

    Cheers

  6. #6
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wavman
    The first is preventing duplicate values from appearing. Where the data is being taken from more than one table and being cross referenced with others I was getting results apearing mulitiple times. Which is when the access query builder came to the rescue and I managed to get the results I wanted.
    OK. But that's a poor way to get a unique resullt set IMO. You could use the DISTINCT keyword.

    Quote Originally Posted by wavman
    So would the best answer be to seperately fetch the file_desc field maybe when I am looping though the results? Surely creating and destroying 20 recordsets per page can't be good. Is there a better way to join it in?
    Indeed. Opening n recordsets to get extra data is a *terrible* way to do it, you would have to slightly mad to try. Give me some time, and I'll post some code which will help you.

  7. #7
    SitePoint Enthusiast wavman's Avatar
    Join Date
    Jul 2002
    Location
    Isle of Wight
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help, very grateful indeed. Will look forward to the code.

    I was orginally using DISTINCT but when the query got a bit hairy it got thrown out the window, not sure it could be done with that many inner joins and stuff.

    Thanks again for your help.

  8. #8
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, open Northwind, and try this out.

    I used the employees table especially because it contains a memo field ("Notes").


    Code:
      SELECT distinct e.LastName, e.FirstName, e.Notes  from Employees AS e, Orders o
      where
      e.City in (SELECT top 1 Employees.City FROM Employees GROUP BY Employees.City ORDER BY Count(Employees.City) DESC)
      and e.employeeid = o.employeeid
    This query is using set theory, to answer the quetion: "List me the firstname, lastname and notes of all employees who have made sales and who live in the city where the most employees live."

    HTH

    Richard

  9. #9
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I hope you can see that it is possible towrite simple, elegant SQL to answer even the most seemingly difficult of questions.

    Your ability to get the most out of the database rests on your level of understanding of SQL.

    I know Access is easy and great, but the query builder, especially, prevents one from actually thinking about how the data can best be questioned using sql.

    HTH

  10. #10
    SitePoint Enthusiast wavman's Avatar
    Join Date
    Jul 2002
    Location
    Isle of Wight
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Sorry for not looking sooner, but have just got back to working on this project and realised I hadn't had an email saying there was a reply.

    Still a bit confused I'm afriad! The issue I'm trying to resolve requires getting info from several tables and checking that info against each other to ensure the user has permission to see the info I am going to display to them. Am I beign really thick or does your example only work for one table?

    Cheers


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •