VBA errors with Access in non-English language

[EL_Gringo]

I recently face strange errors when taking an App to a computer that runs Access in Spanish (I'm developing on an all English System, W10, Office 2013 32 Bit): A simple statement like Me.FilterOn=True asks to introduce the parameter value for Verdadero (Spanish for True); no matter if I then type 'Verdadero' or 'True', I get an error. Setting Me.FilterOn=Verdadero also produces an error (Compile error, variable not defined).
And it gets stranger. The lines:

Call StoreSettingBool("SysFESettings", False, 3)
Public Sub StoreSettingBool(ByVal MTable As String, ByVal MWhat As Boolean, ByVal MRow As Byte)
CurrentDb.Execute "UPDATE " & MTable & " SET " & _
MTable & ".SysSetBool = " & MWhat & " WHERE " & MTable & ".IDNum = " & MRow & ";"
End Sub

produces an error, as MWhat contains 'Verdadero' which is not recognized, but

Call StoreSettingBool("SysFESettings", False, 3)
Public Sub StoreSettingBool(ByVal MTable As String, ByVal MWhat As Boolean, ByVal MRow As Byte)
If MWhat = True Then
CurrentDb.Execute "UPDATE " & MTable & " SET " & _
MTable & ".SysSetBool = True WHERE " & MTable & ".IDNum = " & MRow & ";"
Else
CurrentDb.Execute "UPDATE " & MTable & " SET " & _
MTable & ".SysSetBool = False WHERE " & MTable & ".IDNum = " & MRow & ";"
End If
End Sub

works fine. When I change Access language from Spanish to English, all works perfect, but that I cannot do to the users, as Word and Excel are affected too. This Problem has not been existing before, it all worked fine taking the Apps to computers running Access in other languages.

 

[The_Doc_Man]

There is a simple enough workaround to get you going. I don't know what Access wants when set to Spanish, but I know that Boolean is a typecast of BYTE (SHORT INTEGER). Therefore, use -1 for TRUE and 0 for FALSE until you can figure out what Access wants.

OR ... you could open the VBA window, open the immediate Window, and see what you get when you type:

Debug.Print CBOOL( -1 )
Debug.Print CBOOL( 0 )

Those two lines should tell you what your Spanish-language version wants. Note also that this line:

If MWhat = True Then

works equally well if you write it as

If MWhat Then

 

[arnelgp]

change your public sub, StoreSettingBool, declaration and change MRow to Long (instead of Byte).

Byte data can only accept 0 up to 255 values.

Public Sub StoreSettingBool(ByVal MTable As String, ByVal MWhat As Boolean, ByVal MRow As Long)
    CurrentDb.Execute "UPDATE " & MTable & " SET " & _
        MTable & ".SysSetBool = " & MWhat & " WHERE " & MTable & ".IDNum = " & MRow & ";"
End Sub

 

[ebs17]

The real problem is demonstrated with the following example (here for German).

Sub test_Values()
    Dim sSQL As String
    Const cbFlag As Boolean = True
    Const cdValue As Double = 12.55
    Const cdtDate As Date = #1/23/2023#

    ' with German Access and German regional settings
   
    sSQL = "INSERT INTO AnyTable (BooleanField, DoubleField, DateField)" & _
           " VALUES (" & cbFlag & ", " & cdValue & ", " & cdtDate & ")"
    Debug.Print
    Debug.Print "1 - untreated"
    Debug.Print sSQL
   
    sSQL = "INSERT INTO AnyTable (BooleanField, DoubleField, DateField)" & _
           " VALUES (" & IIf(cbFlag = "Wahr", "True", "False") & ", " & Str(cdValue) & ", " & Format(cdtDate, "\#yyyy\-mm\-dd\#") & ")"
    Debug.Print
    Debug.Print "2 - treated"
    Debug.Print sSQL
End Sub

' output in immediate window

1 - untreated
INSERT INTO AnyTable (BooleanField, DoubleField, DateField) VALUES (Wahr, 12,55, 23.01.2023)

2 - treated
INSERT INTO AnyTable (BooleanField, DoubleField, DateField) VALUES (True,  12.55, #2023-01-23#)

Access converts values into values from the language setting or into values from the regional settings without being asked and this causes errors.

In contrast to VBA, however, SQL does not carry out any internal conversions, but only accepts selected formats. Therefore, such values from "outside" must also be treated depending on the data type.

 

[KitaYama]

Const cbFlag As Boolean = True
sSQL = "INSERT INTO AnyTable (BooleanField, DoubleField, DateField)" & _
" VALUES (" & IIf(cbFlag = "Wahr", "True", "False") & ", " & Str(cdValue) & ", " & Format(cdtDate, "\#yyyy\-mm\-dd\#") & ")"

Why you don't receive an error on IIF(cbFlag="Wahr")?
CbFlag is dimmed as boolean .

and with Japanese Access and Japanese reginal settings why I receive this?

1 - untreated
INSERT INTO AnyTable (BooleanField, DoubleField, DateField) VALUES (True, 12.55, 2023/01/23)

Thanks

 

[ebs17]

? true * 1, CBool("true") * 1, CBool("wahr") * 1, CBool(-1)
-1            -1            -1            Wahr

True, wahr, verdadero, the box with the tick ... these are all formats for the actual intrinsic value -1, just like labels for bottles with the same content. Different labels are used in different countries. However, the SQL validator only accepts a specific label.

When I look at it that way, CBool(cbFlag) * 1 is more universally applicable than IIf(cbFlag = "Wahr", "True", "False").

 

[KitaYama]

True, wahr, verdadero, the box with the tick ... these are all formats for the actual intrinsic value -1

Sorry to insist. Just trying to understand. You've enclosed Wahr in double quote marks. IIf(cbFlag = "Wahr"...
How can a boolean be tested against a string and vba doesn't throw a data type mismatch error?
Whar is surrounded by quotes.
If it was IIf(cbFlag = Wahr... I can understand. But "Whar"?

thanks

 

[ebs17]

Formats are strings.

Format function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

 

[KitaYama]

Formats are strings.

Format function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

I'm sorry. But I can't understand how format function relates to IIf(cbFlag = "Wahr"...
Anyway, I think I better stay away from this discussion and don't take your time.

Thank you.

 

[Minty]

@KitaYama CBool takes a string type and converts it to a Boolean. It will error if it can't work it out.
So in my locality (UK)

Obviously my English Office version has no clue what "Whar" means.

 

[KitaYama]

@KitaYama CBool takes a string type and converts it to a Boolean. It will error if it can't work it out.
So in my locality (UK)
View attachment 105980

Obviously my English Office version has no clue what "Whar" means.

@Minty
I know about CBool. But it's not used in the subscribed code in #4

This is what I see ;

Const cbFlag As Boolean = True
sSQL = "INSERT INTO AnyTable (BooleanField, DoubleField, DateField)" & _
" VALUES (" & IIf(cbFlag = "Wahr", "True", "False") & ", " & Str(cdValue) & ", " & Format(cdtDate, "\#yyyy\-mm\-dd\#") & ")"
[\quote]

To the best of my knowledge, we can not test a boolean variable against a string, According to @ebs17, Wahr is the equivalant of True in Germany. But as long is it's surronded by double qutoe marks, it becomes a string. To me, it's just like I use:

myVar="1"

Even if 1 is a number, as long as it's inside the quotation marks, it's behaved as a string.
So I still can't understand how IIF(cbFlag="Wahr"..... can be compiled and not throw a data mismatch error on runtime.

If I'm wrong, I'd be appreciated for any correction.

Thank you.

 

[sonic8]

with Japanese Access and Japanese reginal settings why I receive this?

You should keep in mind that the VBA *editor* does not support Unicode. This might be the reason, why you see a slightly different behavior for Japanese.

So I still can't understand how IIF(cbFlag="Wahr"..... can be compiled.

It can be compiled because VBA hardly does any type checking at compile time.
It can also be executed with German language settings because VBA implicitly tries to perform a type conversion if it encounters different types in an expression.
Implicitly the code is identical to: IIF(cbFlag=CBool("Wahr") ....

 

[EL_Gringo]

There is a simple enough workaround to get you going. I don't know what Access wants when set to Spanish, but I know that Boolean is a typecast of BYTE (SHORT INTEGER). Therefore, use -1 for TRUE and 0 for FALSE until you can figure out what Access wants.

OR ... you could open the VBA window, open the immediate Window, and see what you get when you type:

Debug.Print CBOOL( -1 )
Debug.Print CBOOL( 0 )

Those two lines should tell you what your Spanish-language version wants. Note also that this line:

If MWhat = True Then

works equally well if you write it as

If MWhat Then

Debug.Print CBOOL( -1 ) returns Verdadero, but when I use Verdadero (or Falso for that matter), VBA does not understand it. Using -1 or CBool(-1) in the calling Procedure also produces the error, even CBool(MWhat) does. I did think of this workaround, but it does not work.
By the way, lines like Me!SomeObject.Enabled=True do work without problems.

 

[EL_Gringo]

change your public sub, StoreSettingBool, declaration and change MRow to Long (instead of Byte).

Byte data can only accept 0 up to 255 values.

Public Sub StoreSettingBool(ByVal MTable As String, ByVal MWhat As Boolean, ByVal MRow As Long)
    CurrentDb.Execute "UPDATE " & MTable & " SET " & _
        MTable & ".SysSetBool = " & MWhat & " WHERE " & MTable & ".IDNum = " & MRow & ";"
End Sub

MRow is declared as Byte on purpose and has nothing to do with the present problem.

 

[EL_Gringo]

? true * 1, CBool("true") * 1, CBool("wahr") * 1, CBool(-1)
-1            -1            -1            Wahr

True, wahr, verdadero, the box with the tick ... these are all formats for the actual intrinsic value -1, just like labels for bottles with the same content. Different labels are used in different countries. However, the SQL validator only accepts a specific label.

When I look at it that way, CBool(cbFlag) * 1 is more universally applicable than IIf(cbFlag = "Wahr", "True", "False").

Eberhard, you made my day ... well, partially. CBool(MWhat) * 1 does work with False (or True) in the calling procedure!
However, Me.FilterOn = CBool(True) * 1 does not work, neither does using Verdadero in any form. VBA does not recognize Verdadero and the suggestion when writing the code is only True or False.
Tried: Dim Verdadero as Boolean / Verdadero = True / Me.FilterOn=Verdadero: No joy. I cannot get VBA to set Me.FilterOn when running on the Spanish Office.

 

[ebs17]

SQL and VBA speak English, they only accept True. That stays the same.

Only in the case described above does the existing True become something else. You have to understand that first, because you don't make any mistakes if you understand the syntax, and then you have to deal with it.

sSQL = "UPDATE " & MTable & " SET " & _
              MTable & ".SysSetBool = True WHERE " & MTable & ".IDNum = " & MRow & ";"
Debug.Print sSQL

In the finished SQL statement after assembly, True must arrive, alternatively -1.

 

[EL_Gringo]

Update:
Me.FilterOn=True is working just fine in some Forms, in others it doesn't, no matter where the line is put (FormOpen event, FormLoad, a command-button procedure). And yes, I did a C&R and decompile / recompile.
BTW I checked Spanish forums: The suggestions are Me.FilterOn=True, and I could not find any post to a problem similar to mine. Reinstalling MS-Office will probably not resolve the problem, as it started simultaneously on two machines with Office in Spanish.

 

[EL_Gringo]

SQL and VBA speak English, they only accept True. That stays the same.

Only in the case described above does the existing True become something else. You have to understand that first, because you don't make any mistakes if you understand the syntax, and then you have to deal with it.

sSQL = "UPDATE " & MTable & " SET " & _
              MTable & ".SysSetBool = True WHERE " & MTable & ".IDNum = " & MRow & ";"
Debug.Print sSQL

In the finished SQL statement after assembly, True must arrive, alternatively -1.

I think I do understand both circumstances perfectly, and I'm pretty sure I understand the syntax. What I don't understand is, why A) True is translated to 'Verdadero' and appearing as such in the assembled SQL-String when using MWhat and B) Why VBA, after translating, does not understand 'Verdadero'. Could that be a bug in Access?

 

[ebs17]

It's not a bug, it's a feature.

SQL and VBA speak English.
But if you work with assistants (design view for queries, forms, reports, code generator), function names and spellings are adapted there to the used national language, and then also such output as in the immediate window to regional settings. On the one hand, something like that may help, on the other hand, it helps to increase confusion, and as a side effect you then have such incomprehensible things as here in the topic.

Take it and learn to control it.

 

[arnelgp]

MRow is declared as Byte on purpose and has nothing to do with the present problem.

you'll get Future Error anyway if you passed a value more than 255.