Access not recognizing SQL boolean values

Question

I have the following SQL statement for adding records in Microsoft Access. When I run the statement, every field gets added correctly except the boolean field, which should always be false. However, it always adds the record with the default boolean value (true).

Dim strSQL As String
strSQL = "INSERT INTO Part VALUES ('" & Me.IdPartPrimary.Value & "', '" & Me.NamePartPrimary.Value & "', '" & Me.BrandPartPrimary.Value & "', '" & Me.ModelPartPrimary.Value & "', '" & Me.FunctionPartPrimary.Value & "', 0, '" & Me.FatherPartPrimary.Value & "', '" & Me.ProviderPartPrimary.Value & "', '" & Me.AmountPartPrimary.Value & "');"
DoCmd.RunSQL strSQL

Things I've tried:

Putting the 0 between ' '
Replacing the 0 with the word false, False and No

I can't set false to be the default value because what I need is for it to be different from the default value

Table

Form

What's the "Boolean field" here? Can you share your table schema and clue is on your variables/values you are inserting here? — JNevill, Aug 31 '22 at 17:36
If fields are Yes/No type, don't use apostrophe delimiters. Those are only for text type field and Yes/No is number type. I don't understand your last statement. You can set 0 as DefaultValue in table design and then enter -1 if that is what you want. — June7, Aug 31 '22 at 17:39
@June7 I tried that but it doesn't work, it still adds the record with the default value of the field — LaCosaCoding, Aug 31 '22 at 17:42
I believe you will need to insert `'True'` instead of `0`. I agree with changing this over to binding your parameters though so you don't suffer sql injection. It would be relatively easy for someone filling out this form to destroy your database. — JNevill, Aug 31 '22 at 17:46
@June7 By that do you mean the assistant that pops up when I create a button? I can't do that becuase I don't want the user to be able to control whether the field Primary_Part is true or false. The reason for this is a bit long — LaCosaCoding, Aug 31 '22 at 17:47
And which of those referenced controls is for Yes/No field? Form can be bound to table and you can still manage the input to Yes/No field with code. Still not making sense to me. You say you want the field to receive 0 (False, No) yet you say you can't set that as DefaultValue. Binding form to table has nothing to do with creating a command button. — June7, Aug 31 '22 at 17:49
Oh yeah the form is bound to the table. How would I do to only manage the yes/no input with code? Sorry for the confusion I am incredibly new to Access — LaCosaCoding, Aug 31 '22 at 17:51
If form is bound and controls are bound to fields then using SQL makes no sense. You will create two records. And definitely should not include an autonumber field in the INSERT action. — June7, Aug 31 '22 at 17:52
BOUND form as already indicated. Inputs go directly to table. This is basic Access functionality. — June7, Aug 31 '22 at 17:55
This is for a school project and the teacher (kind of an idiot) apparently doesn't allow us to use BOUND forms, is there a way to go over this problem using SQL? — LaCosaCoding, Aug 31 '22 at 18:01
Then don't BIND form so you don't create two records. I am still confused by how you want to treat Yes/No field. If you want record to be created with 0 value then just set DefaultValue property in table. Again, do not include autonumber field in INSERT action. You will have to explicitly list field names: `INSERT INTO Part(Part_Name, ..., Primary_Part, ...) VALUES(... & ", 0," & ...)` — June7, Aug 31 '22 at 18:11

June7 · Accepted Answer · 2022-09-01T17:49:52.820

Binding form to table and running SQL to create record into same table will result in two new records. Do one or the other.

Do not include autonumber field in the INSERT action. Will have to explicitly list the fields to receive data.

If you want new record to always have 0 value in the Yes/No field, then set that as DefaultValue in table design, otherwise use a constant in the SQL concatenation to specify.

strSQL = "INSERT INTO Part(..., Primary_Part, ...) VALUES(" & ... & ", 0, " & ... & ")"

AFAIK, Access cannot execute multi-action SQL statements but SQL injection is still possible. If you want to explore use of Parameters in VBA, review How do I use parameters in VBA in the different contexts in Microsoft Access?

Another alternative to avoid SQL injection is to open a recordset, use its AddNew method to create a new record, and set value of each field. DAO.Recordset AddNew

Access not recognizing SQL boolean values

1 Answers1