Query returning Chinese Characters instead of numeric values

Posted on 2014-01-22
Medium Priority
Last Modified: 2014-01-31
A client is running one of my applications on Win 7 with Office 2010 (64 bit).  I know, avoid 64 bit Office, not my choice.  I need to find out what operating system version and Office version they have installed.

The application has been running fine for six months, but they only use it for about a week out of every month due to their business processes.  This afternoon, the client sent me an email indicating that she was getting a runtime error #13 (type mismatch) in one segment of the code.

This evening, I was able to copy here data table over to my system and run the application with her data; ran fine (but I'm running Office 2007, guess I need to breakout the laptop and test on 2010 x64).  Went out to their network and ran the version that is sitting on the network and I got the same error she did.  Backtracked the problem to a query that is returning Chinese characters in stead of what should be numeric values.  Obviously, when my code attempts to update a numeric field with those values, it is failing.

The two computed columns look like:

Alloc: [RunVol]*Switch([Alloc_Prod]="O" And Val(Nz([Oil],0))>0,[SumOfOil]/[Oil],
                                        [Alloc_Prod]="W" And Val(Nz([Water],0))>0,[SumOfWater]/[Water],


Pct_Alloc: Switch([Alloc_Prod]="O" And Val(Nz([Oil],0))>0,[SumOfOil]/[Oil],
                              [Alloc_Prod]="W" And Val(Nz([Water],0))>0,[SumOfWater]/[Water],

I opened another version of the database and it encountering the same issue.  All of the other queries involved in this query all display valid values,

When I change the SWITCH to nested IIF( ) statements, they are working properly again.

IIf([Alloc_Prod]="O" And Val(Nz([Oil],0))>0,[SumOfOil]/[Oil],IIf([Alloc_Prod]="W" And Val(Nz([Water],0))>0,[SumOfWater]/[Water],Null))

Anyone else have any problems with SWITCH() in 64 bit Office 2010?
Question by:Dale Fye
LVL 38

Expert Comment

by:Jim P.
ID: 39801931
Have you tried a C&R?

I ran into an issue when I was building an Acc03 DB on my Win7 64 bit workstation and then tried running on a Win2k3 server. All I had to do was do the C&R there and the problem went away.
LVL 12

Expert Comment

ID: 39802408
Probably there are some bad records corrupting a table.

Make a backup of the database.

Compact and repair it.

Now open the database and see if you can find and delete the records with "chinese" characters.

If you cannot delete the records, you may need to  temporarily remove the table indexes, delete the records, then replace the indexes afterwards.
LVL 50

Accepted Solution

Dale Fye earned 0 total points
ID: 39802610
I've done the C&R of the front-end, no effect.

All of the tables are fine, and the three queries that feed this final query all run fine and return the correct numeric values.

It was something about the SWITCH function that was causing the problem.  When I replaced SWITCH with IIF( ) all the Chinese characters disappeared.
LVL 59
ID: 39802845
<< Backtracked the problem to a query that is returning Chinese characters in stead of what should be numeric values. >>

I've only seen this with database corruption.  I've got one client right now where this happens in one specific part of the program.  I haven't pinned it down yet, but should be working on some of that this weekend.

I don't by the way believe this is 64 exclusive issue, but something in ACE.   I haven't seen situations like this prior to 2007.

  What  I find is just about the entire record will be flooded with Chinese characters.

  I don't know if it really is a localization problem, or just in general garbage data is inserted in place of the record.

  No doubt though there is a bug in Access/ACE leading to this.

  I'm really hoping to be able to pin this down in the clients DB and turn it over to Microsoft.

LVL 50

Author Closing Comment

by:Dale Fye
ID: 39823538
Thanks for your thoughts, Jim.

The removal of the Switch function from that one query resolved the issues.

I'm going to research this a little further and see if I can replicate the problem on my 64 bit Office as well.

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question