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

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

"." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
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.

On Dec 6, 5:07 pm, "Brendan Reynolds"
<brenr...@discussions.microsoft.com> wrote:
> 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
>
> "." <mr.humph...@gmail.com> wrote in messagenews:(E-Mail Removed)...
> 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

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

"." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Since it's embedded in SQL access doesn't support the
> vbTextCompare/vbBinaryCompare option.
>
> On Dec 6, 5:07 pm, "Brendan Reynolds"
> <brenr...@discussions.microsoft.com> wrote:
>> 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
>>
>> "." <mr.humph...@gmail.com> wrote in
>> messagenews:(E-Mail Removed)...
>> 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
>

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

thanks,

mike

On Dec 7, 5:03 am, "Brendan Reynolds"
<brenr...@discussions.microsoft.com> wrote:
> 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
>
> "." <mr.humph...@gmail.com> wrote in messagenews:(E-Mail Removed)...
>
>
>
> > Since it's embedded in SQL access doesn't support the
> > vbTextCompare/vbBinaryCompare option.
>
> > On Dec 6, 5:07 pm, "Brendan Reynolds"
> > <brenr...@discussions.microsoft.com> wrote:
> >> 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
>
> >> "." <mr.humph...@gmail.com> wrote in
> >> messagenews:(E-Mail Removed)...
> >> 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- Hide quoted text -- Show quoted text -

Works for me ...

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

--
Brendan Reynolds
Access MVP

"." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Yes, but the constants are not accepted inside a sql clause either.
>
> thanks,
>
> mike
>
> On Dec 7, 5:03 am, "Brendan Reynolds"
> <brenr...@discussions.microsoft.com> wrote:
>> 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
>>
>> "." <mr.humph...@gmail.com> wrote in
>> messagenews:(E-Mail Removed)...
>>
>>
>>
>> > Since it's embedded in SQL access doesn't support the
>> > vbTextCompare/vbBinaryCompare option.
>>
>> > On Dec 6, 5:07 pm, "Brendan Reynolds"
>> > <brenr...@discussions.microsoft.com> wrote:
>> >> 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
>>
>> >> "." <mr.humph...@gmail.com> wrote in
>> >> messagenews:(E-Mail Removed)...
>> >> 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- Hide quoted text -- Show quoted text -
>

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"
<brenr...@discussions.microsoft.com> wrote:
> Works for me ...
>
> SELECT Customers.CompanyName, InStr(1,[CompanyName],"e",1) AS Expr1
> FROM Customers;
>
> --
> Brendan Reynolds
> Access MVP
>
> "." <mr.humph...@gmail.com> wrote in messagenews:(E-Mail Removed)...
>
>
>
> > Yes, but the constants are not accepted inside a sql clause either.
>
> > thanks,
>
> > mike
>
> > On Dec 7, 5:03 am, "Brendan Reynolds"
> > <brenr...@discussions.microsoft.com> wrote:
> >> 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
>
> >> "." <mr.humph...@gmail.com> wrote in
> >> messagenews:(E-Mail Removed)...
>
> >> > Since it's embedded in SQL access doesn't support the
> >> > vbTextCompare/vbBinaryCompare option.
>
> >> > On Dec 6, 5:07 pm, "Brendan Reynolds"
> >> > <brenr...@discussions.microsoft.com> wrote:
> >> >> 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
>
> >> >> "." <mr.humph...@gmail.com> wrote in
> >> >> messagenews:(E-Mail Removed)...
> >> >> 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- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -

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/de...vafctinstr.asp

--
Brendan Reynolds
Access MVP

"." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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"
> <brenr...@discussions.microsoft.com> wrote:
>> Works for me ...
>>
>> SELECT Customers.CompanyName, InStr(1,[CompanyName],"e",1) AS Expr1
>> FROM Customers;
>>
>> --
>> Brendan Reynolds
>> Access MVP
>>
>> "." <mr.humph...@gmail.com> wrote in
>> messagenews:(E-Mail Removed)...
>>
>>
>>
>> > Yes, but the constants are not accepted inside a sql clause either.
>>
>> > thanks,
>>
>> > mike
>>
>> > On Dec 7, 5:03 am, "Brendan Reynolds"
>> > <brenr...@discussions.microsoft.com> wrote:
>> >> 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
>>
>> >> "." <mr.humph...@gmail.com> wrote in
>> >> messagenews:(E-Mail Removed)...
>>
>> >> > Since it's embedded in SQL access doesn't support the
>> >> > vbTextCompare/vbBinaryCompare option.
>>
>> >> > On Dec 6, 5:07 pm, "Brendan Reynolds"
>> >> > <brenr...@discussions.microsoft.com> wrote:
>> >> >> 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
>>
>> >> >> "." <mr.humph...@gmail.com> wrote in
>> >> >> messagenews:(E-Mail Removed)...
>> >> >> 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- 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

Brendan Reynolds wrote:
> 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/de...vafctinstr.asp
>
> --
> Brendan Reynolds
> Access MVP
>
> "." <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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"
> > <brenr...@discussions.microsoft.com> wrote:
> >> Works for me ...
> >>
> >> SELECT Customers.CompanyName, InStr(1,[CompanyName],"e",1) AS Expr1
> >> FROM Customers;
> >>
> >> --
> >> Brendan Reynolds
> >> Access MVP
> >>
> >> "." <mr.humph...@gmail.com> wrote in
> >> messagenews:(E-Mail Removed)...
> >>
> >>
> >>
> >> > Yes, but the constants are not accepted inside a sql clause either.
> >>
> >> > thanks,
> >>
> >> > mike
> >>
> >> > On Dec 7, 5:03 am, "Brendan Reynolds"
> >> > <brenr...@discussions.microsoft.com> wrote:
> >> >> 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
> >>
> >> >> "." <mr.humph...@gmail.com> wrote in
> >> >> messagenews:(E-Mail Removed)...
> >>
> >> >> > Since it's embedded in SQL access doesn't support the
> >> >> > vbTextCompare/vbBinaryCompare option.
> >>
> >> >> > On Dec 6, 5:07 pm, "Brendan Reynolds"
> >> >> > <brenr...@discussions.microsoft.com> wrote:
> >> >> >> 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
> >>
> >> >> >> "." <mr.humph...@gmail.com> wrote in
> >> >> >> messagenews:(E-Mail Removed)...
> >> >> >> 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- Hide quoted text -- Show quoted text -- Hide quoted text --
> >> >> >> Show quoted text -
> >

Cool, thanks for letting us know that it worked.

--
Brendan Reynolds
Access MVP


"." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Great! Thanks for the help.
>
> It works fine with textCompare and binaryCompare, only fails with
> databaseCompare.
>
> thanks,
>
> mike
>
> Brendan Reynolds wrote:
>> 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/de...vafctinstr.asp
>>
>> --
>> Brendan Reynolds
>> Access MVP
>>
>> "." <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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"
>> > <brenr...@discussions.microsoft.com> wrote:
>> >> Works for me ...
>> >>
>> >> SELECT Customers.CompanyName, InStr(1,[CompanyName],"e",1) AS Expr1
>> >> FROM Customers;
>> >>
>> >> --
>> >> Brendan Reynolds
>> >> Access MVP
>> >>
>> >> "." <mr.humph...@gmail.com> wrote in
>> >> messagenews:(E-Mail Removed)...
>> >>
>> >>
>> >>
>> >> > Yes, but the constants are not accepted inside a sql clause either.
>> >>
>> >> > thanks,
>> >>
>> >> > mike
>> >>
>> >> > On Dec 7, 5:03 am, "Brendan Reynolds"
>> >> > <brenr...@discussions.microsoft.com> wrote:
>> >> >> 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
>> >>
>> >> >> "." <mr.humph...@gmail.com> wrote in
>> >> >> messagenews:(E-Mail Removed)...
>> >>
>> >> >> > Since it's embedded in SQL access doesn't support the
>> >> >> > vbTextCompare/vbBinaryCompare option.
>> >>
>> >> >> > On Dec 6, 5:07 pm, "Brendan Reynolds"
>> >> >> > <brenr...@discussions.microsoft.com> wrote:
>> >> >> >> 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
>> >>
>> >> >> >> "." <mr.humph...@gmail.com> wrote in
>> >> >> >> messagenews:(E-Mail Removed)...
>> >> >> >> 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- Hide quoted text -- Show quoted text -- Hide quoted
>> >> >> >> text --
>> >> >> >> Show quoted text -
>> >
>