Access SQL - Insert records with null fields?

Question

I have Insert statement that inserts records from one table to another. SQL works, but not If field in WHERE clause is Null. No errors, just nothing gets inserted. How can I fix this ? This is what I have (fields are named same in both tables - Me.[Serial_No] represents bound field - textbox :

dim SQL as String

 SQL = "INSERT INTO Table1 (Serial_No,Name,Description)" & _
       "SELECT Table2.Serial_No, Table2.Name, Table2.Description" & _
       " FROM Table2" & _
       " WHERE Table2.Serial_No='" & Me.[Serial_No] & "'"

DoCmd.RunSQL SQL

If your serial_no field is null, or empty, then you are not selecting any records to be inserted in to table1 — LiamH
– LiamH, Commented May 24, 2016 at 7:39
@LiamH, so what you propose is to select record ID, and not field ?...But my problem is that Table2 doesn't have and Autonumber field... — LuckyLuke82
– LuckyLuke82, Commented May 24, 2016 at 7:52
What would you like to get inserted when the input value is Null? — trincot
– trincot, Commented May 24, 2016 at 7:57

LiamH · Accepted Answer · 2016-05-24 09:38:38Z

1

I am not sure if this is the answer you require, it doesn't appear very logical.

If name and description are never null values then ensure that they are a unique composite key in your table. You can create these unique keys in the index button in table design view. Then you could look up the serial_no using the other field values. Seems a bit long winded to me but should give you the record you require.

dim SQL as String
dim varSerialNo as string

varSerialNo = dlookup("Serial_No", "table2", "Name='" & me.Name & "' AND Description='" & me.description & "'")

 SQL = "INSERT INTO Table1 (Serial_No,Name,Description)" & _
       "SELECT Table2.Serial_No, Table2.Name, Table2.Description" & _
       " FROM Table2" & _
       " WHERE Table2.Serial_No='" & varSerialNo & "'"

DoCmd.RunSQL SQL

edited May 24, 2016 at 9:38

answered May 24, 2016 at 9:09

LiamH

1,5123 gold badges22 silver badges35 bronze badges

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

2 Comments

LuckyLuke82 Over a year ago

Great, IT works !!...I also figured out simplier way. I could just add Autonumber field (ID_Field) in Table2 and then change WHERE clause to : " WHERE Table2.ID_Field=" & Me.[ID_Field]....This way works too, and It doesn't matter If you don't have ID_Field on form, when you select record with record selector, Access selects record correctly, because It's never Null. Thanks for help !!

LiamH Over a year ago

Yes that would probably be simpler, I was not sure what fields you had apart from the ones you mentioned in question

Collectives™ on Stack Overflow

Access SQL - Insert records with null fields?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related