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, 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
What would you like to get inserted when the input value is Null? — trincot

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

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

Access SQL - Insert records with null fields?

1 Answers