26 Japanese Katakana results in Access search (80040e14/memory overflow) solution

access| Solution supplements The latest modified version, using Unicode character codes instead of non-Unicode (negative value, sometimes error) code

ゴガギグゲザジズヅデドポベプビパヴボペブピバヂダゾゼ
When any one of these 26 Japanese characters is included in the field, it causes the execution of the SQL statement to include the
[Field] like '%aaaaa% 'OrinStr (1,[field], ' AAAAA ', 1) >0
Such a query, there is no reason to appear
"Microsoft JET Database Engine error ' 80040e14 ' memory Overflow" error
Other Jet SQL function commands are not tested and probably have errors associated with character search location matching

Search related data learned that Microsoft engineers have proved to be a bug in Access, may be a grammatical relationship is Microsoft's Dongdong
Executing in a VBSinStr (1, Japanese hiragana variable, "AAAAA", 1)There are still errors.
Microsoft VBScript run-time error ' 800a0005 ' Invalid procedure call or parameter: ' InStr '


No search, because these characters appear in access to the Forum Web search can not be carried out, how painful
Yesterday, a friend shouted strange, his music database can not be searched, only 30,000 records are good
There is no doubt that katakana in Japanese is a curse, and it takes a few minutes to replace the Japanese with the words "?" Search recovered smoothly
Find the Forum Program User group the largest dynamic network Dvbbs AC version of the 7.0SP2 version of the test, also have this Japanese post after the failure to search and run error problems
Line up to search for ' 80040e14 ' memory overflow error much is!

A simple and effective solution:
To encode and decode these 26 characters, the efficiency may not feel ideal, test down the problem is not big, speed is not too great

Coding:

Function Jencode (ByVal istr)
If IsNull (ISTR) or IsEmpty (ISTR) Then
Jencode= ""
Exit function
End If
Dim f,i,e

E=array ("Jn0;", "JN1;", "JN2;", "JN3;", "JN4;", "JN5;", "JN6;", "JN7;", "JN8;",

"JN9;", "JN10;", "JN11;", "JN12;", "JN13;", "JN14;", "JN15;", "Jn16;", "JN17;",

"JN18;", "Jn19", "JN20;", "JN21;", "JN22;", "JN23;", "JN24;", "JN25;"
F=array (ChrW (12468), ChrW (12460), ChrW (12462), ChrW (12464), _
ChrW (12466), ChrW (12470), ChrW (12472), ChrW (12474), _
ChrW (12485), ChrW (12487), ChrW (12489), ChrW (12509), _
ChrW (12505), ChrW (12503), ChrW (12499), ChrW (12497), _
ChrW (12532), ChrW (12508), ChrW (12506), ChrW (12502), _
ChrW (12500), ChrW (12496), ChrW (12482), ChrW (12480), _
ChrW (12478), ChrW (12476))
Jencode=istr
For I=0 to 25
Jencode=replace (Jencode,f (i), E (i))
Next
End Function

Decoding:

Function Juncode (ByVal istr)
If IsNull (ISTR) or IsEmpty (ISTR) Then
Juncode= ""
Exit function
End If
Dim f,i,e

E=array ("Jn0;", "JN1;", "JN2;", "JN3;", "JN4;", "JN5;", "JN6;", "JN7;", "JN8;", "JN9;",

"JN10;", "JN11;", "JN12;", "JN13;", "JN14;", "JN15;", "Jn16;", "JN17;", "Jn18;",

"JN19;", "Jn20", "JN21;", "JN22;", "JN23;", "JN24;", "JN25;"
F=array (ChrW (12468), ChrW (12460), ChrW (12462), ChrW (12464), _
ChrW (12466), ChrW (12470), ChrW (12472), ChrW (12474), _
ChrW (12485), ChrW (12487), ChrW (12489), ChrW (12509), _
ChrW (12505), ChrW (12503), ChrW (12499), ChrW (12497), _
ChrW (12532), ChrW (12508), ChrW (12506), ChrW (12502), _
ChrW (12500), ChrW (12496), ChrW (12482), ChrW (12480), _
ChrW (12478), ChrW (12476))
Juncode=istr
For I=0 to 25
Juncode=replace (Juncode,e (i), F (i)) '-
Next
End Function

Note that if the characters are not easy to use (Windows does not have Japanese support), the commented out section provides CHR (-23804)

.. Such a definition

Such
1.
When form input is saved, use Jencode () to save the 26-character Fuxian encoding (why are these 26 characters,

After all tests 87 Hiragana 89 Katakana finally identified)
Such as
ゴ is Chr (-23116) encoded as JN1;
2.
When displayed, use the Juncode () function to decode and restore the Japanese katakana display
3.
Search for keywords, also use Jencode () to encode and then put in like
where [Topic] like '%jencode (kewwords)% ' uses
To ensure that the value of the search matches the contents of the Encoded database field

==================================

Ps:
You can also use regular expressions to rewrite the two functions above, perhaps more efficiently
And if you don't use Japanese at all, and you don't need to search Japanese, then the decoding part doesn't work,

Save data Actually, replace these 26 katakana characters with either a null character or any character, such as "-"
And if there's a really good way to be more essential, thank you for sharing

Report:
----------------------------
Hiragana 87 ASC Values
-23391-->-23316
Unicode 3040-309f

ぁあぃいぅうぇえぉお
かがきぎくぐけげこご
さざしじすずせぜそぞ
ただちぢっつづてでと
どなにぬねのはばぱひ
びぴふぶぷへべぺほぼ
ぽまみむめもゃやゅゆ
ょよらりるれろゎわゐ
ゑをん゛゜ゝゞ
------------------------------
Katakana 89 ASC Values
-23135->-23059
Unicode 30A0-30FF

ァアィイゥウェエォオ
カガキギクグケゲコゴ
サザシジスズセゼソゾ
タダチヂッツヅテデト
ドナニヌネノハバパヒ
ビピフブプヘベペホボ
ポマミムメモャヤュユ
ョヨラリルレロヮワヰ
ヱヲンヴヵヶーヽヾ

================ Supplemental Modified version ===========================

Add a coded decoding parameter CodeType
All use a function
Use Chr () do not use Japanese characters directly
Is it simple enough?

Suspect: There is no error in displaying Japanese, and there is no error saving to the database
Only SQL uses like and INSTR when the error is not related to the display!
Also use INSTR (1,STR, "AAA", 1) in the VBS so it's wrong to search by character
Change to INSTR (LCase (str), "AAA") without error

If you must use INSTR (1,STR, "AAA", 1) Character search syntax
Be sure to Jncode () the Order of the InStr () before you go wrong

It's not a problem! It's absolutely right to notice these points!

RS ("Topicstr") =jncode (topicstr,true) ' encode data saved to the database
Displaystr=jncode (RS ("Topicstr"), false) ' Uncode ' display to the title of the page

Function Jncode (ByVal istr,codetype)
If IsNull (ISTR) or IsEmpty (ISTR) or istr= "" Then
Jncode= "": Exit function
End If
Dim f,i,e
E=array ("Jn0;", "JN1;", "JN2;", "JN3;", "JN4;", "JN5;", "JN6;";
"JN7;", "JN8;", "JN9;", "JN10;", "JN11;", "JN12;", "JN13;", _
"JN14;", "JN15;", "Jn16;", "JN17;", "Jn18;", "Jn19;", "Jn20;", _
"JN21;", "Jn22", "Jn23;", "JN24;", "JN25;"
F=array (ChrW (12468), ChrW (12460), ChrW (12462), ChrW (12464), _
ChrW (12466), ChrW (12470), ChrW (12472), ChrW (12474), _
ChrW (12485), ChrW (12487), ChrW (12489), ChrW (12509), _
ChrW (12505), ChrW (12503), ChrW (12499), ChrW (12497), _
ChrW (12532), ChrW (12508), ChrW (12506), ChrW (12502), _
ChrW (12500), ChrW (12496), ChrW (12482), ChrW (12480), _
ChrW (12478), ChrW (12476))
If Codytype Then
For i=0 to Istr=replace (ISTR,F (i),E (i)) Next
Else
For i=0 to Istr=replace (ISTR,E (i),F (i)) Next
End If
Jncode=istr
End Function