CODEWITHSUNDEEP
ms-access
martin.lindenlauf
martin.lindenlauf

Reputation: 394

MS Access VBA boolean values localization problem

I build some SQL query strings in VBA in an MS Access Database (current Office 365 Access Version) and I ran into a weird localization problem with boolean values read from form controls: Me.MyBooleanControl returns "Wahr" (german "True") => the generated SQL fails.

I'm using a workaround now, wrapping each value request by a function to translate the values to -1/ 0 like suggested here, but I really like to know if there's a straight way to avoid this. I used the same code structure last year in another Access Database not running into the same problem - and I've absolutely no idea what's different (besides MS Office my have been updated).

Edit: I found the solution, see answer below.

Upvotes: 3

Views: 392

Answers (2)

Gustav
Gustav

Reputation: 55921

I can't replicate this with my Danish localisation:

Me!UnboundCheckbox01 = True
Debug.Print Me!UnboundCheckbox01 ' returns String: "True"

However, it appears that you can assign just about any value of any data type to an unbound checkbox:

Me!UnboundCheckbox01 = "Martin"
Debug.Print Me!UnboundCheckbox01 ' returns String: "Martin"

Me!UnboundCheckbox01 = Now
Debug.Print Me!UnboundCheckbox01 ' returns Date like: 2020-03-29 13.45.23

I'm not sure if this is intended - even though the Value property is said to be of data type Variant:

CheckBox.Value property (Access)

Upvotes: 1

martin.lindenlauf
martin.lindenlauf

Reputation: 394

Trying to reproduce the problem independently from the rest of my code, I nailed it down and found it to be my fault: I initialized the unbound checkbox as True which seemed to work fine, but causes the problem:

Private Sub Form_Load()
    Me.UnboundCheckbox01 = True
    Me.UnboundCheckbox02 = -1
End Sub

Private Sub ReadCheckBox()
    Debug.Print Me.UnboundCheckbox01 ' returns localized value "Wahr"
    Debug.Print Me.UnboundCheckbox02 ' returns -1 as expected

    ' after manually toggeling UnboundCheckbox01 to unchecked
    ' and back to checked again, the value returns as -1 too
End Sub

Sorry! Next time I'll honor the rules and provide some code - sometimes the question dissolves by just doing that...

Upvotes: 1

Related Questions