MS Access VBA boolean values localization problem

Question

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.

A checkbox returns values that can't be localised. So, somehow you cast those values. Without your code we can't tell why. — Gustav
– Gustav, Commented Mar 28, 2020 at 13:23
Thank you for the hint @Gustav, I found the bug by providing the code (see edit). — martin.lindenlauf
– martin.lindenlauf, Commented Mar 28, 2020 at 21:11
You should post the solution as an answer and accept it so someone else who faces a similar problem may find the solution. — forpas
– forpas, Commented Mar 28, 2020 at 21:14

martin.lindenlauf · Accepted Answer · 2020-03-29 10:01:12Z

1

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...

answered Mar 29, 2020 at 10:01

martin.lindenlauf

3941 gold badge4 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gustav · Accepted Answer · 2020-03-29 10:45:00Z

1

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)

edited Mar 29, 2020 at 10:45

answered Mar 29, 2020 at 10:16

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

1 Comment

martin.lindenlauf Over a year ago

Thank's for checking this out! So your comment above was not 100% correct: in the special case of assigning True to a checkbox it may happen that you run into a localization problem (maybe depending on program version or setup) ;)

Collectives™ on Stack Overflow

MS Access VBA boolean values localization problem

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related