mysql, access and 255char limit

[galorin]

I am trying to populate a listbox using a sql query as the Row Source. All the information is coming off a linked table, with the data residing on a remote MySQL server. Access lists the data type as memo for the comment field, on the server, the field is varchar(500)

The query runs fine for most cases, but if the char length is greater than 255, only the characters after the 255'th character are displayed. When viewing the data directly in Access, all the data shows up, so that tells me that it is the listbox causing me bother, not anything before that.

I don't know how to make Access show all the data, not just the last bits of the data.

 

[HarleyQuinn]

Sounds like a similar issue to

http://www.tek-tips.com/viewthread.cfm?qid=1420361,

with the listview truncating the data.

Not sure why you're getting the end of the data rather than a truncated version though...

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[galorin]

It looks like I am running into the memo truncation shown at

http://allenbrowne.com/ser-63.html,

but the wrong way around.

Here is the query I started with, but it truncates the comment properly, returning the first 255 characters, but for every row in the database, I get two duplicate rows back from the database. It shouldn't be truncating full stop (according to rules), but it does.

SELECT companycomments.[Entered Date], companycomments.[Comment By], companycomments.Comment, companycomments.[Follow Up Date], companycomments.[Follow Up By], companycomments.CommentID, companies.Company
FROM companies INNER JOIN companycomments ON companies.Company = companycomments.Company
WHERE (((companies.Company)=[Forms]![Company]![Company]))
ORDER BY companycomments.[Entered Date] DESC;

If I change it to

 SELECT DISTINCT ......

then I don't get 2 rows returned for every 1 row on the DB, but I do get the text that's getting chopped off when I don't use a select distinct.

 

[HarleyQuinn]

What happens if you use GROUP BY instead of DISTINCT (might be clutching at straws here...)?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Remou]

I think (more accurately, guess) the problem is with displaying the memo-type data in a listbox. Would it be suitable to display a truncated comment and only show the full text (in a textbox, perhaps, when the item is selected?

 

[HarleyQuinn]

That was my first thought Remou (the truncation of the listbox I mean) until the mention of changing the query slightly changed the truncation of the field threw me a bit of a curve ball (though I suppose where the changed results were being output wasn't specified).

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[galorin]

I've tried several variations on the GROUP BY idea, but I can't get any to come out syntactically correct, or to produce a result that returns any data at all.

 

[HarleyQuinn]

A tip for GROUP BY, if you're not using any aggregate functions (SUM, MAX, MIN) copy your full SELECT (and field list) portion of the query, paste it in after your WHERE clause and then change the SELECT you've got down there to a GROUP BY. e.g.

SELECT companycomments.[Entered Date], companycomments.[Comment By], companycomments.Comment, companycomments.[Follow Up Date], companycomments.[Follow Up By], companycomments.CommentID, companies.Company
FROM companies INNER JOIN companycomments ON companies.Company = companycomments.Company
WHERE (((companies.Company)=[Forms]![Company]![Company]))
[red]GROUP BY companycomments.[Entered Date], companycomments.[Comment By], companycomments.Comment, companycomments.[Follow Up Date], companycomments.[Follow Up By], companycomments.CommentID, companies.Company[/red]
ORDER BY companycomments.[Entered Date] DESC;

Please note, I'm not saying that getting this right will have any effect on your actual problem but it is worth knowing how to do correctly.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[galorin]

Well, that has given me a listbox with one entry for every entry in the database, but looks like I'll need to come up with a different solution for the textbox, as it just shows what isn't shown in the listbox as a direct reference to the column in the listbox.

 

[galorin]

Blah, it's still doing the truncating, is it possible to do something like

IF length(comment)>50 then write first 50 chars + ... else display full text without resorting to VBA for onFormLoad() event to populate the listbox, then an onClick() or similar event for putting full comment into my textbox

 

[HarleyQuinn]

Yeah, you can do that in the query no problem (and you were almost there! ), just an example:

NewComment: Iif(Len(comments)>=50,left(comments,47) & "...",comments)

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.