Solved

Query returning Chinese Characters instead of numeric values

Posted on 2014-01-22
5
670 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)
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Why can't I change data in my query? 3 38
Reoccurring Access Query 24 47
ISeries Remote Location Route entry 2 25
Button to auto fill form with previous record 3 17
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

734 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