Truncated Memo Field output

[wavman]

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

 SQLQuery = "SELECT * FROM file_data WHERE file_id in( " & fileidVar & ") ORDER BY file_date DESC" 
SET RsBookmark = MakeRecordSet(cn,SQLQuery)

SQLQuery 2

 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

[asterix]

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

[wavman]

Hi,

The code for the first query is:

 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

 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

[asterix]

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.

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

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

[asterix]

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.

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.

[wavman]

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.

[asterix]

So, open Northwind, and try this out.

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

  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

[asterix]

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

[wavman]

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