text is gibarish in group by query (1 Viewer)

marlan

Registered User.
Local time
Tomorrow, 00:40
Joined
Jan 19, 2010
Messages
409
text is gibrish in group by query

Hi all you experts!

while processing string manipulation on a table (140K records, 200-250 MB) the file has corrupted, and I lost all vba Modules, but the data and other DB objects seemed to be in tact.

I now have a query with a text field, when I make a simple join SELECT, the text comes in fine (and, of course, seems fine when presented in table), but when Group by - I get Gibarish: "CARVEDILOL 6.25MG, TABLETS"
Is presented in Group by as: "砅5"

I have recreated the file, importing queries, including this one, but then recreated it as a totally new query, but get tha same results.

File Corruption? anything to do about it?
 
Last edited:

marlan

Registered User.
Local time
Tomorrow, 00:40
Joined
Jan 19, 2010
Messages
409
Thanks for replying,

No.
 

marlan

Registered User.
Local time
Tomorrow, 00:40
Joined
Jan 19, 2010
Messages
409
Sorry, I was wrong!

this field is the only memo!...
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:40
Joined
Feb 28, 2001
Messages
27,001
Well... Memo fields behave quite well as long as you know what you are doing with them, but they become a problem when you import to such fields. You are allowed to make a field in text format or in Rich Text format (RTF) - which includes font markers.

There is also the question of whether your DB or that table is set to be ASCII text format or a UNICODE format, and somehow the UNICODE is not right.

I agree with vbaInet that something is wrong with the Memo field, but the first thing I would try is (if possible) to change the Memo to a TEXT field of 255 bytes and import into that. Do something to force it to not be in a place where RTF works. I would first try to eliminate format issues before I would blithely say "file corruption."
 

marlan

Registered User.
Local time
Tomorrow, 00:40
Joined
Jan 19, 2010
Messages
409
Hi DocMan,

This field was a 255 bite text field, but some my data exceeded that length, and I made it a memo.
Today the maximum length of text in this field is 480, the average is about 44...

Is there something I can do to get it back to a text field?
 

vbaInet

AWF VIP
Local time
Today, 22:40
Joined
Jan 22, 2010
Messages
26,374
Basically, Memo fields shouldn't be included in a Group By clause.
Perform the Group By minus the memo field, then join the memo field to the query afterwards.
 

marlan

Registered User.
Local time
Tomorrow, 00:40
Joined
Jan 19, 2010
Messages
409
Hi,
I thought of your idea, but in Allen Browne's link I brought above, he suggested to aggregate the Memo field by FIRST (and deal with change of the field name), and it seems to give me fine results.
Is one method better than the other?

TIA!
 

vbaInet

AWF VIP
Local time
Today, 22:40
Joined
Jan 22, 2010
Messages
26,374
That's fine as well, but what's the point in aggregating a text field with 255 chars just for the sake of it. If you don't need that field aggregated, you can add it at a later stage and your query will run with greater efficiency.
 

marlan

Registered User.
Local time
Tomorrow, 00:40
Joined
Jan 19, 2010
Messages
409
I am (actually was;), I now use this Query without aggregation) using this field just to present meaningful data in an aggregation on it's table.

I guess you'r rite: it is more efficient to join the memo field, than to compare it's value and find if it is a first occurrence...

Thank you for your help!
 

Users who are viewing this thread

Top Bottom