?
Solved

Query returning Chinese Characters instead of numeric values

Posted on 2014-01-22
5
Medium Priority
?
683 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
[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 48

Accepted Solution

by:
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.
0
 
LVL 58
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 48

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.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

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.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

719 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