Chinese gibberish being displayed when viewing data

[mfreedman]

Hi there

This is my first time on here, so please be kind! I'm new to Access but have used an ancient database (Lotus Approach) for client records and notes for 20 years (I am a health professional). I though it was about time I started using a more current package, so am trying to switch to Access 2016 (on Windows 10). I have successfully imported all my data into Access in the DBF IV format. The patient treatment notes are in a long text field, but when you enter the field to scroll and edit/add text, after a short while an error dialog box appears "Invalid Argument" and then all the text appears to turn to wingdings (or some other peculiar font) - see screenshot.



I've tried compacting/repair the database and changing the form of the form as suggested elsewhere on this forum (it happens in datasheet view as well). I know the data is successfully exported from Lotus Approach as it opens fine in Open Office Base - but I want to use Access as Base seems to run very slowly on my computer.

Any ideas how to fix this?

Thanks in advance!

[Bob Fitz]

Welcome to the forum

The field that is bound to "Treatment 2" would need to be of "Memo" type. Check its properties in the bound table.

B.T.W.
I noticed Woodford Green on your screen shot. I used to live there when I was younger. (Much younger )

[mfreedman]

Thanks Bob
Doesn't help though! The field is designated "long text", which I understand was formerly known as "memo" field in earlier versions. The gibberish kicks in after about 500 characters.
P.S. I have a lot of clients in Woodford Green, as I am based in Chingford. Nice bit of the world!
Any other ideas?
Many thanks
Mark

[isladogs]

Unfortunately, this is often due to data being corrupted.
You may be able to recover it by decompiling your database.
See http://www.fmsinc.com/MicrosoftAcces.../Decompile.asp

Make a copy before you start....just in case

[orange]

Further to Colin's advice see this post and you could also try searching the forum for " unwanted chinese characters msaccess" or this allen browne link

[mfreedman]

Thanks Colin, I tried decompiling but no luck still!

[isladogs]

Unfortunately that was probably your best chance.
You can try copying everything into a fresh database but I think the chinese characters will go across as well.

As an experiment try the following step by step ....

1. You say you know the export worked as you opened the file in Office Base.
Did you import from that or direct from Lotus Approach?
Try importing from the other program

2. Copy a record from approach into Notepad - does it display correctly?
If so, copy & paste from there to Access.
Now look at that record in the table. Increase the height of the record until you can see the whole record
Does it still look OK?

3. Open the form in design view and increase width & height of the textbox so you can see the whole record without scrolling.
Does that record look OK?
What about the other records?

3. Can you import using any other format than using DBase?

4. Are all records scrambled - is it feasible to just re-type the data in Access?

[mfreedman]

Hi Colin

Thanks for your input, but still got the problem! To address your suggestions:
1. I saved it from Base as a DBF again but same problem recurs
2. Copied into Notepad, the chinese characters did not display, either as chinese or the original. The text appears cut off where the chinese characters were appearing in Access
3a. Can't realistically increase the size of the text box, some of the text records are just too big for this to be practical
3b. There are several other file formats Lotus Approach will export in, I've attached a screenshot of them. I've tried text delimited but Access wouldn't work with it (
4. Theres about 5000 records, so retyping them isn't really an option!

I've also tried the text delimited option, when I tried it before when I open it in Access there appears to be nothing there! I can ope in Excel (hoped I could get around it that way) but the entries are appearing in the wrong lines, even when I used an unusual character "~" (rather than comma's) to qualify fields.

Not sure where I can go from here! Thanks for all your help, any other suggestions gratefully received!

[isladogs]

Out of interest, do you get 512 characters before its cut off or mangled?

Have you tried viewing in Notepad++ (free download) which is more powerful than the standard Notepad

Seems to be some duplication in that list!

Try exporting a csv file & view that in Excel first.
If it looks OK, import that as a text file into Access

Alternatively try exporting as XLS

What does export as MDB mean? Does it create an MDB file?
If so you won't be able to open it in access 2016 but I may be able to do so in A2010 depending on which version MDB it is.
Others here still use A2007 or A2003 so someone may be able to open it for you & then convert to A2016

[mfreedman]

Hi Colin

I counted just over 500 characters before it corrupts, so possibly 512. Tried both the csv and xls files and using excel, but the data doesn't display correctly, some of it is split into several line entries rather than just one. I tried using a different delimiter character but still have the same problem.

I tried a MDB export, but nothing appeared to be exported, maybe that file format isn't working?

Thanks for all your help though...... I might have to try Apache Office Base instead :-(

[isladogs]

You could try exporting the part that is OK to Word & do a character count (including spaces) if you can be bothered
Other than working through other export formats, I don't know what to suggest
Perhaps try TAB separated values if available?

If you can upload in a format I can look at, I'll try & import into A2010 but unlikely to be successful.
Is there a forum for Lotus Approach where you might get specific guidance on exporting?

[isladogs]

One more thought. Have you tried just linking the dbase table in lotus approach to Access? Is that possible?

[mfreedman]

Thanks for all your help and advice but I think I'm going to give up on Access for now - sorry! I've found a way to do all I need in OpenOffice Base which will suffice. Thanks for trying though, its much appreciated!

Best wishes

Mark

[isladogs]

Can't say I blame you for giving up.
However before you do so, suggest you try linking to the table.
If that works, you can then use an append or update query to fix your Access table

It will only take a minute or so to try and if it fails then you're no worse off.