Access INSTR function fails in SQL for some Japanese characters

[.]

The Instr function will return an incorrect substring position for
values containing some Japanese characters. While most Japanese
characters are fine, some characters such as ã‚°(GU - \u30b0) and
プ(PU \u30D7) will be counted as two characters by the Instr function
offsetting the returned position incorrectly. Most importantly, the
returned position is incorrect even when passed as an argument to the
Mid function. Most other Japanese characters function correctly when
passed to the Instr function.

For example,
Mid( ‘グab’, Instr(‘グab’, ‘a’), 1)
will incorrectly return ‘b’. With most other Japanese characters,
‘a’ would be correctly returned.

Is this a known problem? Is there some way to work around this?

thanks

mike

 

[Brendan Reynolds]

Does it make any difference if you specify the optional arguments? For
example ...

Mid( '?ab', Instr(1, '?ab', 'a', vbTextCompare), 1)

.... or ...

Mid( '?ab', Instr(1, '?ab', 'a', vbBinaryCompare), 1)

.... or ...

Mid( '?ab', Instr(1, '?ab', 'a', vbDatabaseCompare), 1)

--
Brendan Reynolds
Access MVP

The Instr function will return an incorrect substring position for
values containing some Japanese characters. While most Japanese
characters are fine, some characters such as ?(GU - \u30b0) and
?(PU \u30D7) will be counted as two characters by the Instr function
offsetting the returned position incorrectly. Most importantly, the
returned position is incorrect even when passed as an argument to the
Mid function. Most other Japanese characters function correctly when
passed to the Instr function.

For example,
Mid( '?ab', Instr('?ab', 'a'), 1)
will incorrectly return 'b'. With most other Japanese characters,
'a' would be correctly returned.

Is this a known problem? Is there some way to work around this?

thanks

mike

 

[.]

Since it's embedded in SQL access doesn't support the
vbTextCompare/vbBinaryCompare option.

 

[Brendan Reynolds]

They're just constants, in SQL use the literal values. 0 = vbBinaryCompare,
1 = vbTextCompare, 2 = vbDatabaseCompare. You can find these values in the
Object Browser or just print them in the Immediate Window ...

? vbBinaryCompare
0
? vbTextCompare
1
? vbDatabaseCompare
2

I can't promise that this will work, as I wouldn't know how to go about
entering Japanese characters in order to test it. But it may be worth a try.

 

[.]

Yes, but the constants are not accepted inside a sql clause either.

thanks,

mike

 

[Brendan Reynolds]

Works for me ...

SELECT Customers.CompanyName, InStr(1,[CompanyName],"e",1) AS Expr1
FROM Customers;

 

[.]

Interesting. I get #Error if I add a 3rd argument.

select instr(machine,'a') from sessionRun gives the position found
select instr(machine,'a',1) from sessionRun gives #Error

sessionRun is a Text field, 255 characters.

I'm using Access 2003

thanks,

mike

Works for me ...

SELECT Customers.CompanyName, InStr(1,[CompanyName],"e",1) AS Expr1
FROM Customers;

--
Brendan Reynolds
Access MVP

Yes, but the constants are not accepted inside a sql clause either.

 

[Brendan Reynolds]

You need to specify the first, 'start' argument, which is otherwise
optional, when specifying the 'compare' argument ...

select instr(1,machine,'a',1) from sessionRun

Here's a link to the on-line help topic ...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctinstr.asp

--
Brendan Reynolds
Access MVP

Interesting. I get #Error if I add a 3rd argument.

select instr(machine,'a') from sessionRun gives the position found
select instr(machine,'a',1) from sessionRun gives #Error

sessionRun is a Text field, 255 characters.

I'm using Access 2003

thanks,

mike

Works for me ...

SELECT Customers.CompanyName, InStr(1,[CompanyName],"e",1) AS Expr1
FROM Customers;

--
Brendan Reynolds
Access MVP

message

Yes, but the constants are not accepted inside a sql clause either.


On Dec 7, 5:03 am, "Brendan Reynolds"
They're just constants, in SQL use the literal values. 0 =
vbBinaryCompare,
1 = vbTextCompare, 2 = vbDatabaseCompare. You can find these values in
the
Object Browser or just print them in the Immediate Window ...

? vbBinaryCompare
0
? vbTextCompare
1
? vbDatabaseCompare
2

I can't promise that this will work, as I wouldn't know how to go
about
entering Japanese characters in order to test it. But it may be worth
a
try.

messagenews:[email protected]...

Since it's embedded in SQL access doesn't support the
vbTextCompare/vbBinaryCompare option.

On Dec 6, 5:07 pm, "Brendan Reynolds"
Does it make any difference if you specify the optional arguments?
For
example ...

Mid( '?ab', Instr(1, '?ab', 'a', vbTextCompare), 1)

... or ...

Mid( '?ab', Instr(1, '?ab', 'a', vbBinaryCompare), 1)

... or ...

Mid( '?ab', Instr(1, '?ab', 'a', vbDatabaseCompare), 1)

messageThe Instr function will return an incorrect substring position for
values containing some Japanese characters. While most Japanese
characters are fine, some characters such as ?(GU - \u30b0) and
?(PU \u30D7) will be counted as two characters by the Instr
function
offsetting the returned position incorrectly. Most importantly,
the
returned position is incorrect even when passed as an argument to
the
Mid function. Most other Japanese characters function correctly
when
passed to the Instr function.

For example,
Mid( '?ab', Instr('?ab', 'a'), 1)
will incorrectly return 'b'. With most other Japanese characters,
'a' would be correctly returned.

Is this a known problem? Is there some way to work around this?

mike- Hide quoted text -- Show quoted text -- Hide quoted text --
Show quoted text -

 

[.]

Great! Thanks for the help.

It works fine with textCompare and binaryCompare, only fails with
databaseCompare.

thanks,

mike

You need to specify the first, 'start' argument, which is otherwise
optional, when specifying the 'compare' argument ...

select instr(1,machine,'a',1) from sessionRun

Here's a link to the on-line help topic ...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctinstr.asp

--
Brendan Reynolds
Access MVP

Interesting. I get #Error if I add a 3rd argument.

select instr(machine,'a') from sessionRun gives the position found
select instr(machine,'a',1) from sessionRun gives #Error

sessionRun is a Text field, 255 characters.

I'm using Access 2003

thanks,

mike

Works for me ...

SELECT Customers.CompanyName, InStr(1,[CompanyName],"e",1) AS Expr1
FROM Customers;

--
Brendan Reynolds
Access MVP

message


Yes, but the constants are not accepted inside a sql clause either.

thanks,

mike

On Dec 7, 5:03 am, "Brendan Reynolds"
They're just constants, in SQL use the literal values. 0 =
vbBinaryCompare,
1 = vbTextCompare, 2 = vbDatabaseCompare. You can find these values in
the
Object Browser or just print them in the Immediate Window ...

? vbBinaryCompare
0
? vbTextCompare
1
? vbDatabaseCompare
2

I can't promise that this will work, as I wouldn't know how to go
about
entering Japanese characters in order to test it. But it may be worth
a
try.

--
Brendan Reynolds
Access MVP

message
Since it's embedded in SQL access doesn't support the
vbTextCompare/vbBinaryCompare option.

On Dec 6, 5:07 pm, "Brendan Reynolds"
Does it make any difference if you specify the optional arguments?
For
example ...

Mid( '?ab', Instr(1, '?ab', 'a', vbTextCompare), 1)

... or ...

Mid( '?ab', Instr(1, '?ab', 'a', vbBinaryCompare), 1)

... or ...

Mid( '?ab', Instr(1, '?ab', 'a', vbDatabaseCompare), 1)

--
Brendan Reynolds
Access MVP

messageThe Instr function will return an incorrect substring position for
values containing some Japanese characters. While most Japanese
characters are fine, some characters such as ?(GU - \u30b0) and
?(PU \u30D7) will be counted as two characters by the Instr
function
offsetting the returned position incorrectly. Most importantly,
the
returned position is incorrect even when passed as an argument to
the
Mid function. Most other Japanese characters function correctly
when
passed to the Instr function.

For example,
Mid( '?ab', Instr('?ab', 'a'), 1)
will incorrectly return 'b'. With most other Japanese characters,
'a' would be correctly returned.

Is this a known problem? Is there some way to work around this?

thanks

mike- Hide quoted text -- Show quoted text -- Hide quoted text --
Show quoted text -

 

[Brendan Reynolds]

Cool, thanks for letting us know that it worked.

--
Brendan Reynolds
Access MVP

Great! Thanks for the help.

It works fine with textCompare and binaryCompare, only fails with
databaseCompare.

thanks,

mike

You need to specify the first, 'start' argument, which is otherwise
optional, when specifying the 'compare' argument ...

select instr(1,machine,'a',1) from sessionRun

Here's a link to the on-line help topic ...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctinstr.asp

--
Brendan Reynolds
Access MVP

Interesting. I get #Error if I add a 3rd argument.

select instr(machine,'a') from sessionRun gives the position found
select instr(machine,'a',1) from sessionRun gives #Error

sessionRun is a Text field, 255 characters.

I'm using Access 2003

thanks,

mike


On Dec 7, 3:54 pm, "Brendan Reynolds"
Works for me ...

SELECT Customers.CompanyName, InStr(1,[CompanyName],"e",1) AS Expr1
FROM Customers;

--
Brendan Reynolds
Access MVP

message


Yes, but the constants are not accepted inside a sql clause either.

thanks,

mike

On Dec 7, 5:03 am, "Brendan Reynolds"
They're just constants, in SQL use the literal values. 0 =
vbBinaryCompare,
1 = vbTextCompare, 2 = vbDatabaseCompare. You can find these values
in
the
Object Browser or just print them in the Immediate Window ...

? vbBinaryCompare
0
? vbTextCompare
1
? vbDatabaseCompare
2

I can't promise that this will work, as I wouldn't know how to go
about
entering Japanese characters in order to test it. But it may be
worth
a
try.

--
Brendan Reynolds
Access MVP

message
Since it's embedded in SQL access doesn't support the
vbTextCompare/vbBinaryCompare option.

On Dec 6, 5:07 pm, "Brendan Reynolds"
Does it make any difference if you specify the optional
arguments?
For
example ...

Mid( '?ab', Instr(1, '?ab', 'a', vbTextCompare), 1)

... or ...

Mid( '?ab', Instr(1, '?ab', 'a', vbBinaryCompare), 1)

... or ...

Mid( '?ab', Instr(1, '?ab', 'a', vbDatabaseCompare), 1)

--
Brendan Reynolds
Access MVP

messageThe Instr function will return an incorrect substring position
for
values containing some Japanese characters. While most Japanese
characters are fine, some characters such as ?(GU - \u30b0) and
?(PU \u30D7) will be counted as two characters by the Instr
function
offsetting the returned position incorrectly. Most importantly,
the
returned position is incorrect even when passed as an argument
to
the
Mid function. Most other Japanese characters function correctly
when
passed to the Instr function.

For example,
Mid( '?ab', Instr('?ab', 'a'), 1)
will incorrectly return 'b'. With most other Japanese
characters,
'a' would be correctly returned.

Is this a known problem? Is there some way to work around this?

thanks

mike- Hide quoted text -- Show quoted text -- Hide quoted
text --
Show quoted text -