423,473 Members | 2,593 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,473 IT Pros & Developers. It's quick & easy.

MS Access query returning Chinese characters

P: 3
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT dbo_Requisitions.buyer, dbo_Requisitions.accepted_date
  2.   , dbo_Requisitions.req_uic, dbo_Requisitions.req_fy_info, dbo_Requisitions.req_serial
  3.   , dbo_Requisitions.award_date, dbo_Requisitions.contract_uic, dbo_Requisitions.contract_fy
  4.   , dbo_Requisitions.contract_suffix, dbo_Requisitions.contract_serial
  5.   , dbo_Requisitions.est_delivery_date, dbo_Requisitions.call_number
  6.   , dbo_Requisitions.total_cost
  7.   , Sum(dbo_Payments.amount_paid) AS SumOfamount_paid
  8.   , dbo_Requisitions.material_recd_date, dbo_Requisitions.complete_ship
  9.   , dbo_Requisitions.priority, dbo_Requisitions.remarks
  10.   , dbo_Requisitions.datayear, dbo_Requisitions.item_description
  11. FROM dbo_Requisitions 
  12.   LEFT JOIN (dbo_ACRN_JON_EE LEFT JOIN dbo_Payments 
  13.     ON dbo_ACRN_JON_EE.acrn_jon_ee_id 
  14.       = dbo_Payments.acrn_jon_ee_id) 
  15.    ON dbo_Requisitions.reqid 
  16.       = dbo_ACRN_JON_EE.reqid
  17. GROUP BY dbo_Requisitions.buyer, dbo_Requisitions.accepted_date
  18.   , dbo_Requisitions.req_uic, dbo_Requisitions.req_fy_info
  19.   , dbo_Requisitions.req_serial, dbo_Requisitions.award_date
  20.   , dbo_Requisitions.contract_uic, dbo_Requisitions.contract_fy, dbo_Requisitions.contract_suffix
  21.   , dbo_Requisitions.contract_serial, dbo_Requisitions.est_delivery_date
  22.   , dbo_Requisitions.call_number, dbo_Requisitions.total_cost
  23.   , dbo_Requisitions.material_recd_date, dbo_Requisitions.complete_ship
  24.   , dbo_Requisitions.priority, dbo_Requisitions.remarks, dbo_Requisitions.datayear
  25.   , dbo_Requisitions.item_description;
Aug 1 '18 #1

✓ answered by 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_Requisitions.buyer] is datatype Text-Long
Then create a calculated field as
zShrtBuyer: Left([dbo_Requisitions.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...
Expand|Select|Wrap|Line Numbers
  1. SELECT Left([dbo_Requisitions.buyer],255) As zShrtBuyer
  2. , dbo_Requisitions.accepted_date] As
  3. , Left( dbo_Requisitions.req_uic As zShrtUIC
  4. etc... 
  5. FROM (as is)
  6. GROUP BY
  7. Left([dbo_Requisitions.buyer],255) As zShrtBuyer
  8. , dbo_Requisitions.accepted_date] As
  9. , Left( dbo_Requisitions.req_uic As zShrtUIC
  10. etc... 
  11.  
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

Share this Question
Share on Google+
12 Replies


zmbd
Expert Mod 5K+
P: 5,283
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_Requisitions.buyer] is datatype Text-Long
Then create a calculated field as
zShrtBuyer: Left([dbo_Requisitions.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...
Expand|Select|Wrap|Line Numbers
  1. SELECT Left([dbo_Requisitions.buyer],255) As zShrtBuyer
  2. , dbo_Requisitions.accepted_date] As
  3. , Left( dbo_Requisitions.req_uic As zShrtUIC
  4. etc... 
  5. FROM (as is)
  6. GROUP BY
  7. Left([dbo_Requisitions.buyer],255) As zShrtBuyer
  8. , dbo_Requisitions.accepted_date] As
  9. , Left( dbo_Requisitions.req_uic As zShrtUIC
  10. etc... 
  11.  
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
Aug 1 '18 #2

PhilOfWalton
Expert 100+
P: 1,353
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
Aug 1 '18 #3

twinnyfo
Expert Mod 2.5K+
P: 2,605
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.
Aug 1 '18 #4

P: 3
ZMDB,

I changed all the fields with the left and it fixed the problem except the last field which is the item.description 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.
Aug 1 '18 #5

zmbd
Expert Mod 5K+
P: 5,283
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.
Aug 1 '18 #6

twinnyfo
Expert Mod 2.5K+
P: 2,605
Z,

Well I guess I finally did something right!

;-)
Aug 1 '18 #7

PhilOfWalton
Expert 100+
P: 1,353
@ 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
Aug 1 '18 #8

twinnyfo
Expert Mod 2.5K+
P: 2,605
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.
Aug 1 '18 #9

PhilOfWalton
Expert 100+
P: 1,353
@twinnyfo,

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

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


Phil
Aug 1 '18 #10

zmbd
Expert Mod 5K+
P: 5,283
@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.description 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!))
Aug 1 '18 #11

NeoPa
Expert Mod 15k+
P: 31,031
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.
Aug 2 '18 #12

P: 3
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.
Aug 3 '18 #13

Post your reply

Sign in to post your reply or Sign up for a free account.