Problem using True/False field in other languages than English

[Olle]

Hi,
My application i developed in VBA (Excel, english). It accesses tables in
Access and it is working perfect.
But when users having PC's installed in other languages than English I've
got problem. It seams that True/False is not handled correctly.
The application creates an SQL statement, like the one below, and sends it
to Access (ADO) and return with error '-2147217904 No values given for one or
more required parameters'.
Note that True/False is equal to Sant/Falskt in Swedish. The applications
internal Boolean parameters are always set to True/False but seam to be
converted internally to the ones valid for used language. Note also that the
word 'Sant' below is an internal variable (Boolean) always set to True or
False but is converted when displayed and when it is sent to Access.

UPDATE BD_STATUS SET STATUS = Sant WHERE ((FYEAR = '2005') AND (FPER =
'001') AND (LEGUNIT = 'SE01') AND (FSITEM_SUB = '1532200000')) ;

If this SQL statement executes in Access Query it also gives an error. It
can't understand 'Sant' and return an error saying that 'Sant' is a parameter.

Are there any specific rules I have to do for International applications
using Tru/False fields in Access tables?

I use:
VBA 6.4.9972
Excel 2003 SP1
Access 2003 SP1 (2000 database format)
ADO Version 2.5 (I've tried 2.8 with the same result)

Please help.
Olle

 

[Nikos Yannacopoulos]

Olle,

Not sure this will work, but you might want to give it a shot... instead
of True/False, you could use -1/0 (the numerical values interchangeable
with True/False in Access), which works within Access. If it works in
toyr case, it will obviously be language independent. so, try:

UPDATE BD_STATUS SET STATUS = -1 WHERE ((FYEAR = '2005') AND (FPER =
'001') AND (LEGUNIT = 'SE01') AND (FSITEM_SUB = '1532200000')) ;

HTH,
Nikos

 

[Andi Mayer]

Olle,

Not sure this will work, but you might want to give it a shot... instead
of True/False, you could use -1/0 (the numerical values interchangeable
with True/False in Access), which works within Access. If it works in
toyr case, it will obviously be language independent. so, try:

UPDATE BD_STATUS SET STATUS = -1 WHERE ((FYEAR = '2005') AND (FPER =
'001') AND (LEGUNIT = 'SE01') AND (FSITEM_SUB = '1532200000')) ;

in addition: have I undrstand it right that you are using a variable
Sant? this causes a lot of troubles, because VBA doesn't now if it
should use True or your variable.

never use internal Names as variables, also if it's in your language
and it#s sometimes not visible that VBA is using this

 

[Olle]

Hi Nikos,
I've tried using -1/0 but it still converts to 'Sant' if Swedish which gives
an error. My variable in VBA is declared as Boolean.

"Nikos Yannacopoulos" skrev:

 

[Olle]

Hi Andi,
I've tried to use -1/0 instead of True/False but with the same error. My
variable is declared as Boolean and no matter if I assign it with -1 or True
it will still be converted to 'Sant'.
No, my variable is NOT named Sant - it is named istat.

The 'funny' thing with this is that I'm able to manually insert -1 or True
or Sant directly into the field in the table with no error. When I send an
SQL statement including 'Sant' it aborts. In fact I can't understand why VBA
converts True to Sant in my variable. I thought VBA was in English - no
matter in which language the Office package was installed.

/Olle

"Andi Mayer" skrev:

 

[Andi Mayer]

Hi Andi,
I've tried to use -1/0 instead of True/False but with the same error. My
variable is declared as Boolean and no matter if I assign it with -1 or True
it will still be converted to 'Sant'.
No, my variable is NOT named Sant - it is named istat.

The 'funny' thing with this is that I'm able to manually insert -1 or True
or Sant directly into the field in the table with no error. When I send an
SQL statement including 'Sant' it aborts. In fact I can't understand why VBA
converts True to Sant in my variable. I thought VBA was in English - no
matter in which language the Office package was installed.

I am working with a German Version and I never have this problem in
VBA.

on Forms, Query View and other this thinks get changed to the German
version, but in VBA I have the English version.

like a Field in the query View:
I write: F1: Mid([Field1];3)
it get's changd to Teil([Field1];3)
but the VBA is: Mid([Field1];3) As F1

therefore I don't understand how it can be changed?

is Excel doing this? because beginning with 97 or 2000 it is
converting everything to the local language and you can't use the
englisch expression (only in VBA , you set the formular in english)

 

[Nikos Yannacopoulos]

Olle,

Sorry it didn't work... once more, it just goes to show how right I am
to work with English editions of Windows and Office, although MS offers
them in my mother tongue as well!

Nikos

 

[Jörg Ackermann]

Hi,

Olle schreibselte:

Hi,
My application i developed in VBA (Excel, english). It accesses
tables in Access and it is working perfect.
But when users having PC's installed in other languages than English
I've
got problem. It seams that True/False is not handled correctly.
The application creates an SQL statement, like the one below, and
sends it
to Access (ADO) and return with error '-2147217904 No values given
for one or more required parameters'.
Note that True/False is equal to Sant/Falskt in Swedish. The
applications internal Boolean parameters are always set to True/False
but seam to be converted internally to the ones valid for used
language. Note also that the word 'Sant' below is an internal
variable (Boolean) always set to True or False but is converted when
displayed and when it is sent to Access.

UPDATE BD_STATUS SET STATUS = Sant WHERE ((FYEAR = '2005') AND (FPER =
'001') AND (LEGUNIT = 'SE01') AND (FSITEM_SUB = '1532200000')) ;

If this SQL statement executes in Access Query it also gives an
error. It can't understand 'Sant' and return an error saying that
'Sant' is a parameter.

Are there any specific rules I have to do for International
applications using Tru/False fields in Access tables?

I use:
VBA 6.4.9972
Excel 2003 SP1
Access 2003 SP1 (2000 database format)
ADO Version 2.5 (I've tried 2.8 with the same result)

Try to use

Format$(bTest, "-1;-1;0")
or
format$(value,"\T\r\u\e;\T\r\u\e;\F\a\l\s\e")

example:
Dim sSql As String
Dim bTest As Boolean

bTest = True
sSql = "UPDATE tblKunde SET tblKunde.TA = " & Format$(bTest, "-1;-1;0") & ";"
Debug.Print sSql

 

[Olle]

Hi Jörg,

It seams that your suggestion works but I have to do more tests. Many thanks!!

But can you tell me why I have to do like this? It works perfectly in
English but not in other languages. For me it looks like a 'BUG', what do you
think?

/Olle

"Jörg Ackermann" skrev: