SQL Server YesNo fields - Microsoft Access SQL Server Online


	Back to Access SQL Server Online Comments List

Upload Images @Reply Bookmark Link Email

SQL Server YesNo fields

Robert Race

12 months ago

I have an Access database hooked up ODBC to SQL Server. This works fine but in some tables I have Yes/No fields. I set the default value to 0 and it is set in SQL Server at a bit field. When I try to update the record with the Yes/No I get an error message: "This record has been changed by another user since you started editing it...."
My Access database for is using the Table, not a query. Seems to be an issue with all the Yes/No fields.

	Adam Schwanz @Reply 12 months ago
Run an update query or manually go in and click true/false any new records created after you imported to sql server, to set the value of all the fields to False where they dont = True. It's a weird bug with nulls, I've ran into it a few times myself.

	Richard Rost @Reply 12 months ago
Yeah, the problem is that Access stores a boolean "Yes" as -1, whereas SQL Server stores it as a bit value of 1. Why did Microsoft do it this way? Meh?

	Robert Race OP @Reply 12 months ago
Thanks, I thought I had that cleared up but viewing the top 200 records my last entries were Null. Fixed that and it worked.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access SQL Server Online.