Solved

Query returning Chinese Characters instead of numeric values

Posted on 2014-01-22
5
668 Views
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],
                                        True,Null)

and

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],
                              True,Null)

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?
0
Comment
Question by:Dale Fye (Access MVP)
5 Comments
 
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.
0
 
LVL 12

Expert Comment

by:pdebaets
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.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) 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.
0
 
LVL 57
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.

Jim.
0
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
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.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

740 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