First Published 13 Mar 2023
Boolean variables/fields can have two values, True / False, and may cause various issues in different versions of Access
This article explores some of the issues you may experience and ways of solving them
Problem 1
As part of my Automatic Form Resizing example application, I had 2 boolean variables in a SaveMonitorInfo procedure
The procedure is used to populate a table, tblMonitors
Here are the relevant sections of the original code:
Private Sub SaveMonitorInfo(ForMonitorID As String, bytNoMonitors As Byte)
'adds info to tblMonitors
Dim blnPrimary As Boolean, blnCurrent As Boolean
'more code here . . .
If GetMonitorInfo(CLng(ForMonitorID), MONITORINFOEX) = False Then Failed "GetMonitorInfo"
With MONITORINFOEX
If .dwFlags And MONITOR_PRIMARY Then
blnPrimary = True
Else
blnPrimary = False
End If
End With
'more code here . . .
'check which monitor is current
blnCurrent = GetXCursorPos >= lngLeft And GetXCursorPos <= lngRight And GetYCursorPos >= lngTop And GetYCursorPos <= lngBottom
'populate table
strSQL = "INSERT INTO tblMonitors ( MonitorID, PrimaryMonitor, [Left], [Top], [Right], Bottom, HRes, VRes, CurrentMonitor )" & _
" SELECT " & I & " AS MonitorID, " & blnPrimary & " AS PrimaryMonitor," & _
" " & lngLeft & " AS [Left], " & lngTop & " AS [Top], " & lngRight & " AS [Right], " & lngBottom & " AS Bottom," & _
" " & lngHRes & " AS HRes, " & lngVRes & " AS VRes, " & blnCurrent & " AS CurrentMonitor;"
CurrentDb.Execute strSQL, dbFailOnError
End Sub
The procedure works perfectly in English language versions of Office where the 2 variables blnPrimary and blnCurrent return True or False
However, that isn’t the case in other languages.
For example in Spanish, the code returns the Spanish for True (Verdadero) or False (Falso), neither of which are recognised by VBA.
As a result, the code fails with error 3061 ... Too few parameters.
Using -1/0 instead of True/False in the code does not help as the procedure will still return the unrecognized words.
Solutions
There are several ways of solving this problem:
1. Replace the boolean variables with byte (or integer) number variables and get them to return 1 (for True) or 0 (for False). For example:
Private Sub SaveMonitorInfo(ForMonitorID As String, bytNoMonitors As Byte)
'adds info to tblMonitors
Dim bytPrimary As Byte, bytCurrent As Byte
'. . .
If GetMonitorInfo(CLng(ForMonitorID), MONITORINFOEX) = 0 Then Failed "GetMonitorInfo"
With MONITORINFOEX
If .dwFlags And MONITOR_PRIMARY Then
bytPrimary = 1 'True
Else
bytPrimary = 0 'False
End If
End With
'. . .
'check which monitor is current
bytCurrent = IIf(GetXCursorPos >= lngLeft And GetXCursorPos <= lngRight And GetYCursorPos >= lngTop And GetYCursorPos <= lngBottom, 1, 0)
'populate table
strSQL = "INSERT INTO tblMonitors ( MonitorID, PrimaryMonitor, [Left], [Top], [Right], Bottom, HRes, VRes, CurrentMonitor )" & _
" SELECT " & I & " AS MonitorID, " & bytPrimary & " AS PrimaryMonitor," & _
" " & lngLeft & " AS [Left], " & lngTop & " AS [Top], " & lngRight & " AS [Right], " & lngBottom & " AS Bottom," & _
" " & lngHRes & " AS HRes, " & lngVRes & " AS VRes, " & bytCurrent & " AS CurrentMonitor;"
CurrentDb.Execute strSQL, dbFailOnError
End Sub
As VBA treats any non-zero value as True, these can then be used to populate the boolean fields in the table
2. Change the boolean fields in the table to Short Text. Use string variables in the code to return "Yes" or "No" and use that to populate the table
Problem 2
I had a similar issue with my Access/Office365/Windows Version Check utility app a year or so ago.
In that case, the use of a boolean function to populate a table caused a similar issue for German language users.
The relevant parts of the problem code were:
Function CheckAccess365() As Boolean
'. . .
' check string value exists in registry
If GetStringValFromRegistry(HKEY_LOCAL_MACHINE, registryKey, KeyName) <> "" Then
CheckAccess365 = True
End If
End Function
Initially, I changed the code above to:
Function CheckAccess365() As Byte
'v2.54 - changed from Boolean to Byte to fix issue in German when used in PopulateComputerInfo
'. . .
' check string value exists in registry
If GetStringValFromRegistry(HKEY_LOCAL_MACHINE, registryKey, KeyName) <> "" Then
CheckAccess365 = True
End If
End Function
Subsequently, I completely rewrote the code using a text string as it suited my purposes better
Function CheckAccess365() As String
'v2.61 - changed to return string value
CheckAccess365 = "No"
If Nz(GetAccessProductNumber, "") = "365" And GetAccessVersion = "16.0" Then CheckAccess365 = "Yes"
End Function
Both methods work equally well
More Problems
There are other issues with the use of boolean variables and fields.
As already stated, in Access, the boolean datatype only has two possible values True/False (-1/0)
However in most other database programs including SQL Server, boolean datatypes have 3 possible values: True/False/Null
The lack of null values in Access boolean fields can cause a number of issues including
1. Errors when SQL Server tables with null values in boolean fields are used as linked Access tables. See my article: Write Conflict Errors
2. Outer join queries fail on Yes/No fields - see thia article on Allen Browne's website
Acknowledgements
Many thanks to Jacinto Trillo and Gunter Avenius for alerting me to these issues so I could fix my code.
Feedback
Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems 13 Mar 2023
|
Return to Access Blog Page
|
Return to Top
|