Query returning Chinese Characters instead of numeric values

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?
LVL 50
Dale FyeAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Author Commented:
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
 
Jim P.Commented:
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
 
pdebaetsCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< 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
 
Dale FyeAuthor Commented:
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
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.

All Courses

From novice to tech pro — start learning today.