SitePoint Sponsor |
|
User Tag List
Results 1 to 10 of 10
Thread: Truncated Memo Field output
-
Jan 11, 2005, 08:33 #1
- Join Date
- Jul 2002
- Location
- Isle of Wight
- Posts
- 59
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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)
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
-
Jan 11, 2005, 10:07 #2
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
-
Jan 11, 2005, 10:27 #3
- 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
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
-
Jan 11, 2005, 10:38 #4
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.
-
Jan 11, 2005, 10:43 #5
- 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
-
Jan 11, 2005, 10:51 #6
Originally Posted by wavman
Originally Posted by wavman
-
Jan 11, 2005, 10:57 #7
- 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.
-
Jan 11, 2005, 11:25 #8
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
HTH
Richard
-
Jan 11, 2005, 11:30 #9
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
-
Jan 18, 2005, 04:45 #10
- 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