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
– JNevill, Commented Aug 31, 2022 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
– June7, Commented Aug 31, 2022 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
– LaCosaCoding, Commented Aug 31, 2022 at 17:42

June7 · Accepted Answer · 2022-09-01 17:49:52Z

2

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

edited Sep 1, 2022 at 17:49

answered Aug 31, 2022 at 18:43

June7

21.7k8 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Access not recognizing SQL boolean values

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related