MS Access query returning Chinese characters

[mjp58]

I’m new to access and I’m trying to fix someone else's access database and when I run one of the queries the query returns Chinese characters in all the text fields (they are set as long text) and the query is doing a group by. How do I fix this? Here is the SQL for the query:

SELECT DISTINCT dbo_Requisitions.buyer, dbo_Requisitions.accepted_date
  , dbo_Requisitions.req_uic, dbo_Requisitions.req_fy_info, dbo_Requisitions.req_serial
  , dbo_Requisitions.award_date, dbo_Requisitions.contract_uic, dbo_Requisitions.contract_fy
  , dbo_Requisitions.contract_suffix, dbo_Requisitions.contract_serial
  , dbo_Requisitions.est_delivery_date, dbo_Requisitions.call_number
  , dbo_Requisitions.total_cost
  , Sum(dbo_Payments.amount_paid) AS SumOfamount_paid
  , dbo_Requisitions.material_recd_date, dbo_Requisitions.complete_ship
  , dbo_Requisitions.priority, dbo_Requisitions.remarks
  , dbo_Requisitions.datayear, dbo_Requisitions.item_description
FROM dbo_Requisitions 
  LEFT JOIN (dbo_ACRN_JON_EE LEFT JOIN dbo_Payments 
    ON dbo_ACRN_JON_EE.acrn_jon_ee_id 
      = dbo_Payments.acrn_jon_ee_id) 
   ON dbo_Requisitions.reqid 
      = dbo_ACRN_JON_EE.reqid
GROUP BY dbo_Requisitions.buyer, dbo_Requisitions.accepted_date
  , dbo_Requisitions.req_uic, dbo_Requisitions.req_fy_info
  , dbo_Requisitions.req_serial, dbo_Requisitions.award_date
  , dbo_Requisitions.contract_uic, dbo_Requisitions.contract_fy, dbo_Requisitions.contract_suffix
  , dbo_Requisitions.contract_serial, dbo_Requisitions.est_delivery_date
  , dbo_Requisitions.call_number, dbo_Requisitions.total_cost
  , dbo_Requisitions.material_recd_date, dbo_Requisitions.complete_ship
  , dbo_Requisitions.priority, dbo_Requisitions.remarks, dbo_Requisitions.datayear
  , dbo_Requisitions.item_description;

[zmbd]

mjp58:
There was an old bug when grouping on Memo fields (aka Text Long)
(Bytes Thread: Access: getting chinese (?) character unexpectedly, why? )

Make a copy of your query
In the copy, create a calculated field for each of the long-text fields.
Example if [dbo_Requisition s.buyer] is datatype Text-Long
Then create a calculated field as
zShrtBuyer: Left([dbo_Requisition s.buyer],255)

(you can also use a MID() function depending on what you need from the data)

Do this for all of your long-text fields such that...

SELECT Left([dbo_Requisitions.buyer],255) As zShrtBuyer
, dbo_Requisitions.accepted_date] As
, Left( dbo_Requisitions.req_uic As zShrtUIC
[iCODE]etc...[/iCODE] 
FROM (as is)
GROUP BY
Left([dbo_Requisitions.buyer],255) As zShrtBuyer
, dbo_Requisitions.accepted_date] As
, Left( dbo_Requisitions.req_uic As zShrtUIC
[iCODE]etc...[/iCODE]

Your FROM and JOINS should be fine

>>CANNOT STRESS ENOUGH: Do this on a COPY of your query. It can get messy with that many calculated fields. You may very well run into a "Query is Too Complex" error with as many fields as you have listed.

The best solution is to remove as many of the Long-Text fields as possible from the database. Many people use them by mistake when the standard text field will suffice.

Finally - is this joined/linked to a SQL server or was it joined to an SQL Server at any time in the past? I've read a few articles and it seems to me that the "DBO_" prefix shows up in SQL Server table field names

[PhilOfWalton]

Assuming zmbd is correct, and he usually is, I am guessing that with the possible exception of remarks, most of the text fields should be short text (255 characters) or dates.

Have a look at your data in the table and see if there are any fields that are over 255 characters long, and whether the data matches the field type.

If you do alter the table structure, make sure everything is backed up.

Phil

[twinnyfo]

Unless I am writing a book, I try to avoid memo fields. If I have long comments (or typically, multiple comments added to one record), I will create a table of comments, using the main table's PK to group comments by record.

[mjp58]

ZMDB,

I changed all the fields with the left and it fixed the problem except the last field which is the item.descriptio n field. When I add this field and try to run the query I get the following message '' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

When I remove this field from the query it work great.

[zmbd]

twinnyfo
(...) I will create a table of comments, using the main table's PK to group comments by record.

Exactly what I do!

And I've found this to be immensely helpful when pulling those records together:
Allen Browne Concatenate values from related records

I have another function that I've used in the past... IDK what I did with it... should be posted here in one of the threads too.

[twinnyfo]

Z,

Well I guess I finally did something right!

;-)

[PhilOfWalton]

@ zmbd
Sorry as usual to disagree, and I accept your method will work, but I stick to my original comments as endorsed by twinnyfo, that the table structure looks "dodgy".

The field names give a pretty good idea as to the data held in them, and most do NOT look like Memos.

Phil

[twinnyfo]

mjp58:
the query returns Chinese characters in all the text fields (they are set as long text)

@Phil:
Long text is the same thing as Memo - MS Access renamed the data type a while back.

[PhilOfWalton]

@twinnyfo,

Yes, fully aware of that. Perhaps I should have said

....and most do NOT look like Long Text (Memos).


Phil

[zmbd]

@PhilOfWalton

The field names give a pretty good idea as to the data held in them, and most do NOT look like Memos.

Please carefully read MJP58 OP

(...) all the text fields (they are set as long text) (...)

(the underline is mine :) )
While some users do use descriptive field names, the names themselves, may not reflect the data type selected by the user. Hence my comment

(...)The best solution is to remove as many of the Long-Text fields as possible from the database. Many people use them by mistake when the standard text field will suffice(...)

BTW, this should be taken to read, "The table design is most likely flawed, please check to see that the proper data type was selected for each of the table fields in question." Read with the voice one would hear in a MontyPython voice over :-) (which should also be taken that I agree with you that the design is "dodgy." Let us take a moment of silence for MJP58's struggle with this inherited mess of a database - may it rest in peace and be improved greatly by our efforts.


@mjp58

(...)last field which is the item.descriptio n field. When I add this field and try to run the query I get the following message '' is not a valid name. (...) that it is not too long. (...)

You may have ran into the length limit (around 64000 characters maybe less depending on the database design) or there really is a typo.

Have you looked at all of your tables and made sure that the "Long Text" data type is required?

Add the field back in to the query so that you can get the SQL string for us and post back, maybe a typo or the length - hard to tell without seeing the SQL.

Did I mention looking at all of the text fields to verify that they really need to have that "Long Text"?
If you can change the database structure I would do so.
!!! MAKE A BACK-UP OF THE DATA FILE !!!
Before you make any changes to the data type of the fields!
(Honestly one should never ever make changes to the in use database without a backup and preferably ALL changes are made to copy of the database and then rolled out - new data can usually be appended to the tables if needed (another reason to work with split databases!))

[NeoPa]

Loving seeing you all work together and cover all the possibilities and recommendations .

Especially pleased to see expressions of disagreements along with real explanations that clarify why.

[mjp58]

ZMDB, thank you for your help. I did finally fix the fields in the table with the wrong data type and everything is working now.