ms-access localization and default boolean values

Question

Our access client generates on the fly SQL inserts, update and delete instructions to be sent on a MS-SQL Server. Most users have the runtime version of Access 2007, and a few use the complete MS-Access version, 2003 or 2007. This morning one of our new users abroad, using a french/complete version of Access 2003, was unable to update data containing boolean fields.

It appeared that these fields are, in the french version of Access, populated with "Vrai/Faux" instead of "True/False" values. The problem was solved by installing the 2007 access runtime.

But I'd like to find a permanent solution, where I'd be able to read from somewhere which localized version of Access is in use and 'translate' the localized True/False values to standard True/False. I already checked the regional settings of the computer without success, so it is somewhere else. Any idea?

EDIT: Following JohnFX proposal, it is effectively possible to convert from local True/False to universal True/False with this simple function:

Function xBoolean(xLocalBooleanValue) as Boolean
if cint(xLocalBooleanValue) = -1 Then
    xBoolean = True
endif
if cint(xLocalBooleanValue) = 0 Then
    xBoolean = False
endif
end function

EDIT: following @David's comments, I changed the favorite solution. His proposal is smarter than mine.

EDIT: I am getting the Vrai/Faux values by reading the value of a field in a recordset:

? debug.print screen.activeForm.recordset.fields(myBooleanField).value 
Vrai

Answer 1

Have you considered using -1/0 (Access is weird about booleans) instead of true/false in your update and delete queries?

Math is the universal language, yaknow.

Also, to avoid having to localize the UI so much, why not use check-boxes instead of a text field for booleans on your UI?

Answer 2

True is NOT FALSE, or NOT 0, in all cases, no matter the localization or the database format.

So, if you replace all tests for True with NOT 0 and all tests for False with =0, then you've avoided the issue of localization of the Access keywords (I'm surprised that VBA and the Jet and Access expression services would not still understand True/False, though), as well as whichever convention your database engine uses for storing Boolean values.

In general, your data access layer ought to be abstracting that away for you. Both ODBC and ADO do it automatically, so you work with the Boolean values you know and it's taken care of for you transparently, in my experience.

I'm also still puzzled about the question, as it sounds like a display/formatting issue, but use NOT 0 and =0 for True and False avoids the problem entirely in all cases.

EDIT: In regard to the function edited into Philippe's question:

Is there a reason you've implicitly defined your function's parameter as a variant? Is that what you mean? If it's passed a Null, it's going error out on the first CInt(), as CInt() can't accept a Null.

Also, there's a logic problem in that in VBA any number but 0 is supposed to return True. It's also completely redundant code. This is simpler and returns the correct result in all cases:

  Function xBoolean(xLocalBooleanValue As Vriant) as Boolean
    If CInt(xLocalBooleanValue) <> 0 Then
       xBoolean = True
    End If
  End Function

Or, pithier still:

  Function xBoolean(xLocalBooleanValue As Variant) as Boolean
    xBoolean = (CInt(xLocalBooleanValue) <> 0)
  End Function

And to handle Nulls passed in the parameter:

  Function xBoolean(xLocalBooleanValue As Variant) as Boolean
    xBoolean = (CInt(Nz(xLocalBooleanValue, 0)) <> 0)
  End Function

I'm not sure that's necessary in the context you're currently using it, but I always hate writing code where I can imagine a case where it will error out -- even if I know it can't break in its present context, you never know where it might end up getting used, so should you anticipate a condition that can be handled, you should handle it.

Premature optimization?

No -- it's putting a safety lock on a weapon that keeps it from being misused.

(on the other hand, if it took more lines of code to handle the antipated error than the function started out with, I'd think twice about it)

Answer 3

Simple:

Function xBoolean(bool As Variant) As Boolean
    xBoolean = Abs(Nz(bool, 0))
End Function