CODEWITHSUNDEEP
ms-accesslocalization
CedL
CedL

Reputation: 173

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

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

Upvotes: 0

Views: 831

Answers (2)

Gustav
Gustav

Reputation: 55921

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)) & ");

Upvotes: 1

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19837

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

Upvotes: 3

Related Questions