MS-Access - SQL commands using boolean variables depending of language

Question

On my workstation, I've been using Access in English. Some other computer that will use my form have it in French. Testing it recently, I found out that Boolean variable, even if declared as True/False, come out as Vrai/Faux.

Where it becomes a problem is when I need to use the variable in a text, like an insert or simply a request.

The only workaround I have right now is using another variable of type String and replace it from French to English. The problem with this is... it's two ugly line. I mean, there must be another way than having to that every time i might use a Boolean variable in a request?

EDIT: Here is two exemples.

SELECT [...] FROM [...] WHERE [...]  in (false , " & SomeBooleanValue & ");

-- OR --

str_Sql = "INSERT INTO [...]  VALUES ('" & form_Name & "', " & is_something & ")"

DoCmd.RunSQL str_Sql

Could you give us an example of when you'd use the variable in text please? Usually, for example, I'd give my boolean field a name such as `HasQuit` and then write something like `WHERE HasQuit` and not worry about the `=TRUE` bit but I don't think you're using it that way.... — Darren Bartrup-Cook, Jul 09 '18 at 13:26
What do you mean " ...Boolean variable, even if **declared** as True/False..." ? A variable is declared as a data type, not a value. — ashleedawg, Jul 09 '18 at 13:26
you could use numbers interchangeably with `TRUE`/`FALSE`. `True = -1` `False = 0` — ashleedawg, Jul 09 '18 at 13:30
It's only in the user interface they are localised. In code (VBA), they will always be `True` and `False`, so it should present no issue for you. — Gustav, Jul 09 '18 at 13:32
@ashleedawg What i meant to say is when you assign a value to it. Like isNumeric = True , if you put a breakpoint right after it and check the value, it will be Vrai instead, on a French Access. — CedL, Jul 09 '18 at 13:39
The problem appears when you convert the variable to string, e.g. by concatenating. Example in German Access: `? "True is " & True` -> `True is Wahr` — Andre, Jul 09 '18 at 14:04
I guess using parameters instead of SQL concatenation is the cleanest solution. Otherwise you could use `CInt()` to force it to numbers -1 and 0. https://stackoverflow.com/questions/49509615/how-do-i-use-parameters-in-vba-in-the-different-contexts-in-microsoft-access — Andre, Jul 09 '18 at 14:12
I've edited my answer to include a direct solution to your issue. That said, consider Darren's extended code. — Gustav, Jul 09 '18 at 14:38

score 3 · Accepted Answer · answered Jul 09 '18 at 14:10

You could use parameters with your two examples and then not worry about concatenating into your SQL string:

Sub Test_1()

    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset

    Set qdf = CurrentDb.CreateQueryDef("", _
        "PARAMETERS SomeBooleanValue BIT; " & _
        "SELECT * FROM Table1 WHERE Field_2 = SomeBooleanValue")

    With qdf
        .Parameters("SomeBooleanValue") = True
        Set rst = .OpenRecordset
    End With

    With rst
        If Not (.BOF And .EOF) Then
            .MoveFirst
            Do
                Debug.Print .Fields("Field_1") & " - " & .Fields("Field_2")
                .MoveNext
            Loop While Not .EOF
        End If
    End With

End Sub

and

Sub Test_2()

    Dim qdf As DAO.QueryDef

    Set qdf = CurrentDb.CreateQueryDef("", _
        "PARAMETERS some_text TEXT(255), is_something BIT; " & _
        "INSERT INTO Table1 VALUES (some_text, is_something)")

    With qdf
        .Parameters("some_text") = "Some random text"
        .Parameters("is_something") = True
        .Execute
    End With

End Sub

Gustav · Answer 2 · 2018-07-09T14:37:02.650

1

You can use this format expression to force a True/False output string:

UKTrueFalse = Format(YourBooleanValue, "True;True;Fal\se")

As for your SQL, you could just use the numeric value:

SELECT [...] FROM [...] WHERE [...]  IN (0 , " & Str(Abs(SomeBooleanValue)) & ");

edited Jul 09 '18 at 14:37

answered Jul 09 '18 at 13:37

Gustav

53,498
7
29
55

I'm guessing the format has 2 true because of the 3 state possibility of the checkbox? And what about the 'Fal\se' ? – CedL Jul 09 '18 at 13:50
1

No, it is for "positive;negative;zero" because VBA also counts positive values as True. In fact. only zero is False. The escaped _s_ is needed, or s will be read as _seconds_, thus a zero would be returned. – Gustav Jul 09 '18 at 14:30

MS-Access - SQL commands using boolean variables depending of language

2 Answers2