Inserting empty items into a field.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the SQL statement below to insert a record into a table. The
statement works fine if K contains a value. (both [ASH VALUE], AND [BULK
DENSITY] are double data types.) But I often want to set one or both to
empty/null. I seem to be able to do this if i edit the table manualy by
deleting the entry in those fields but how do I do it with the SQL statement.
Can I "set K to null somehow?


strSQL = "INSERT INTO [LOT QC DATA] " _
& "([COMPOUND LOT], DRUM, SAMPLE,[ASH VALUE],[BULK DENSITY]) " _
& "SELECT " _
& "'" & Me.LOT & "'" & "," & i & "," & j & "," & K & "," & K & ";"
 
You can try
NZ(K,"NULL")

....& Me.LOT & "'" & "," & i & "," & j & "," & Nz(K,"Null" & "," &
Nz(K,"Null") & ";"
I think that will concatenate into your statement correctly.
 
John:

Thanks I will give it a try next time. In the meantime I realized that as
blank is how I want the field to start out a simpler way was to set the
defalt value in the table to blank. If I need to have a differnt default I
will give your method a try.

John Spencer said:
You can try
NZ(K,"NULL")

....& Me.LOT & "'" & "," & i & "," & j & "," & Nz(K,"Null" & "," &
Nz(K,"Null") & ";"
I think that will concatenate into your statement correctly.

ED007 said:
I am using the SQL statement below to insert a record into a table. The
statement works fine if K contains a value. (both [ASH VALUE], AND [BULK
DENSITY] are double data types.) But I often want to set one or both to
empty/null. I seem to be able to do this if i edit the table manualy by
deleting the entry in those fields but how do I do it with the SQL
statement.
Can I "set K to null somehow?


strSQL = "INSERT INTO [LOT QC DATA] " _
& "([COMPOUND LOT], DRUM, SAMPLE,[ASH VALUE],[BULK DENSITY]) " _
& "SELECT " _
& "'" & Me.LOT & "'" & "," & i & "," & j & "," & K & "," & K & ";"
Click to expand...
Click to expand...
 
ED007 said:
I am using the SQL statement below to insert a record into a table. The
statement works fine if K contains a value. (both [ASH VALUE], AND [BULK
DENSITY] are double data types.) But I often want to set one or both to
empty/null. I seem to be able to do this if i edit the table manualy by
deleting the entry in those fields but how do I do it with the SQL statement.
Can I "set K to null somehow?


strSQL = "INSERT INTO [LOT QC DATA] " _
& "([COMPOUND LOT], DRUM, SAMPLE,[ASH VALUE],[BULK DENSITY]) " _
& "SELECT " _
& "'" & Me.LOT & "'" & "," & i & "," & j & "," & K & "," & K & ";"
Click to expand...


Just set K to the string "Null" so the final SQL statement
looks something like:

INSERT INTO [LOT QC DATA] ([COMPOUND LOT], DRUM, SAMPLE,
[ASH VALUE], [BULK DENSITY])
SELECT '1234', 12, 34, Null, Null
 
Back
Top