SQL UPDATE statement

[Lou_Reed]

What does this statement mean in MS access 2016?

strSQL = "UPDATE tblHelpDeskTickets SET tblHelpDeskTickets.ysnTicketAssigned = -1 " & _
"Where tblHelpDeskTickets.lngTicketID = " & Me.txtTicketID & ";"


It is also in the attached zip file.

Obviously, they are updating the table tblHelpDeskTickets. I am not sure what the statement



SET tblHelpDeskTickets.ysnTicketAssigned = -1

actually means?

Again, you are clearly setting a field found in the table tblHelpDeskTickets, which has ysnTicketAssigned = -1. But what does that mean?


The reason that I must learn this statement and all that it means, is because I have a statement like it in my program, but I must update two tables
not one.

I believe that my SQL UPDATE statement would be more complicated than what is shown above.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[ranman256]

-1 is another word for TRUE.
it sets a binary field to true.

[Lou_Reed]

Okay, that is what I was wondering. I was unsure what that statement meant. Now since I have two tables, I assume that I must have two updateSQL statements?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Micron]

-1 is another word for TRUE.
it sets a binary field to true.

Well, you've commented on something that bugs me from time to time because of not remembering something. We probably agree that 0 is always False (or No/Off) but I have run into occasions in writing code where I had to use 1 for True (or maybe it was Yes) but for the life of me, I cannot recall why - just that it tripped me up at first. According to this page for 2013/2016, True/Yes (and I presume On) can be 1 or -1, which doesn't seem to be accurate for 2007 as far as I can tell - at least not in a query and not in a sql based recordset. I haven't been able to find any web results that apply to version 2007, so if anyone can shed light on when True/Yes/On (which are formats for the bit value) has to be represented by 1 I'd appreciate it.
The only thing I'm not sure I'd say is that -1 sets a binary field to true, since a binary field can only be a number. It's the format of the table yes/no field that governs how Access represents that field when using it in a form or report. So 0 is displayed as False, Off, or No and I believe the default control will be a check box.
It would seem that Allen Browne stopped using them - http://allenbrowne.com/noyesno.html

[CJ_London]

The premise is 0 is false/no, anything else is true

in the vba immediate window type

?cbool(12345)

or any number other than 0 will return true

Basically anything can only be either true or false, although you can have a 'don't know'. There is no maybe.

In sql server true is represented by 1

I believe the issue comes in a Boolean field in a table where a 'state' of true or false has yet to be determined i.e. don't know), which is why you can leave it as null. So the other way around Allen's comments is to always set the default to true or false. It depends what the Boolean represents but when the developer is designing the table, they should know the default state for new records.