CODEWITHSUNDEEP
sqlms-accessvba
LuckyLuke82
LuckyLuke82

Reputation: 604

Access SQL - Insert records with null fields?

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

Upvotes: 0

Views: 555

Answers (1)

LiamH
LiamH

Reputation: 1502

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

Upvotes: 1

Related Questions