[RESOLVED] Checkbox Values in Access

[Falconsbane]

I am storing safety data in an Access dB and a few of the fields contain the selected state of checkboxes on my form in 'Yes/No' data types. When I perform a SQL query the values I recieve back, for say a SUM() function are all negative. I know that the default check state value is -1 but how do I change the sign to be displayed in my report? Here is the SQL statement:

strSQL = "SELECT SUM([Manhours]) as SafeMonthly1,          SUM([First Aid]) as SafeMonthly2," _
            & "       SUM([Medical Aid]) as SafeMonthly3,          SUM([Lost Time Accident]) as SafeMonthly4," _
            & "       SUM([Fatal Accident]) as SafeMonthly5,       SUM([Unusual Occurrences]) as SafeMonthly6," _
            & "       SUM([Fire]) as SafeMonthly7,                 SUM([Damage]) as SafeMonthly8," _
            & "       SUM([Process]) as SafeMonthly9,              SUM([Environment]) as SafeMonthly10" _
            & "       FROM Safety_Data WHERE [Date] >= #" & Month(Date) & "/01/" & Year(Date) & "#"
            SafetyDailyRS.Open strSQL, Connection, adOpenKeyset, adLockPessimistic, adCmdText
        
            If Not SafetyDailyRS.EOF Then
                oXLSheet.Range("D9").Value = SafetyDailyRS.Fields("SafeMonthly1").Value
                oXLSheet.Range("D10").Value = SafetyDailyRS.Fields("SafeMonthly2").Value
                oXLSheet.Range("D11").Value = SafetyDailyRS.Fields("SafeMonthly3").Value
                .
                .
                .
            End If

Fire, Damage, Process, and Environment are the checkboxes used to identify the type of Unusual Occurrence. When trying to display the value for the one record I only recieve 'FALSE' as my output, and for SUM() functions as above I get negative integers.

Any suggestions as to how to go about formatting this properly?

[si_the_geek]

The 'Yes/No' data type is a boolean, so it stores values of True or False, but in a numeric form(-1 and 0). If you try to store any value other than 0, it is converted to True (-1).

To get the output you want, it could be argued that you should be using Count instead of Sum, but then you would need to use a query per field (to allow for the Where clause limiting records), or overly complex joins.

I would recommend either changing "SUM(" to "-SUM(", or simply using the Abs function in VB to ignore the negative sign, eg:

oXLSheet.Range("D9").Value = Abs(SafetyDailyRS.Fields("SafeMonthly1").Value)

[Falconsbane]

Thanks, perfect solution.