VBA gives a negative value when should give a positive-CodePudding

I'm trying to set the value of the field p_person as 1 when the bbp.ind=Y.

SqlStmt = "UPDATE person " & _
            " SET p_person = 1 " & _
            " FROM Person p INNER JOIN " & PersonTableName &
              " bbp ON p.personID = bbp.personID " & _
            " WHERE bbp.ind = 'Y' " & BBPersonsManual_Filter_and
SqlConn.Execute SqlStmt, RecordsAffected
LogRecords "Update p_person", Str(RecordsAffected)

Instead of 1, the value is being defined as -1 when the value of the bbp.ind=Y.

How can I solve this?

CodePudding user response：

If this is a Boolean column, then 0 will be interpreted as False and any value unequal 0 will be treated as True. The standard value for True is -1 in an Access table.

So, this behavior is expected. When you test the value of a Boolean column (a Yes/No column) don't test p_person = 1 or p_person <> 1. Instead test p_person <> 0 or p_person = 0. Like this it does not matter whether there is 1 or -1 in the column.