Insert Nulls

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

Guest

I've got a problem that is driving me crazy...

I'm coding an SQL Insert statement and need to set some of the values to null.

Dim tmp_null
tmp_null = Null
sqlstring = "insert into broadcast_messages_CDMi " _
& "(message_id, member_name, message_type, " _
& " message_text, active_from_date, active_to_date, rejected_date, " _
& " acknowledged_date, starting_date, completed_date, " _
& " message_status, broadcast_type) " _
& " values ('" & tmp_message_id & "', " _
& " '" & Me.calendar_consultant & "', " _
& " 'To Do', " _
& " 'Please complete a timesheet for " & Me.calendar_customer & "',
" _
& " '" & Format(engagement_start_date, "m/d/yyyy") & "', " _
& " '" & Format(engagement_end_date, "m/d/yyyy") & "', " _
& " '" & tmp_null & " ', " _
& " '" & tmp_null & " ', " _
& " '" & tmp_null & " ', " _
& " '" & tmp_null & " ', " _
& " '" & tmp_null & " ', " _
& " 'Monday'" _
& ")"
DoCmd.RunSQL sqlstring

When the row is inserted, the column is not set to null, it is set to blank
("") which is not a null value. All columns in the table allow nulls except
for the first column (message_id) which is set to not null.

Can anyone please tell me what I'm doing wrong?

Thanks in advance for your help!
 
PeterM said:
I've got a problem that is driving me crazy...

I'm coding an SQL Insert statement and need to set some of the values
to null.

Dim tmp_null
tmp_null = Null
sqlstring = "insert into broadcast_messages_CDMi " _
& "(message_id, member_name, message_type, " _
& " message_text, active_from_date, active_to_date,
rejected_date, " _ & " acknowledged_date, starting_date,
completed_date, " _ & " message_status, broadcast_type) " _
& " values ('" & tmp_message_id & "', " _
& " '" & Me.calendar_consultant & "', " _
& " 'To Do', " _
& " 'Please complete a timesheet for " & Me.calendar_customer
& "', " _
& " '" & Format(engagement_start_date, "m/d/yyyy") & "', " _
& " '" & Format(engagement_end_date, "m/d/yyyy") & "', " _
& " '" & tmp_null & " ', " _
& " '" & tmp_null & " ', " _
& " '" & tmp_null & " ', " _
& " '" & tmp_null & " ', " _
& " '" & tmp_null & " ', " _
& " 'Monday'" _
& ")"
DoCmd.RunSQL sqlstring

When the row is inserted, the column is not set to null, it is set to
blank ("") which is not a null value. All columns in the table allow
nulls except for the first column (message_id) which is set to not
null.

Can anyone please tell me what I'm doing wrong?

Thanks in advance for your help!
Click to expand...

You need to actually use the word Null in your Values clause, and not
enclose it in quotes. Try this:

sqlstring = "insert into broadcast_messages_CDMi " _
& "(message_id, member_name, message_type, " _
& " message_text, active_from_date, active_to_date,
rejected_date, " _
& " acknowledged_date, starting_date, completed_date, " _
& " message_status, broadcast_type) " _
& " values ('" & tmp_message_id & "', " _
& " '" & Me.calendar_consultant & "', " _
& " 'To Do', " _
& " 'Please complete a timesheet for " & _
Me.calendar_customer & "', " _
& " '" & Format(engagement_start_date, "m/d/yyyy") & "', " _
& " '" & Format(engagement_end_date, "m/d/yyyy") & "', " _
& " Null, " _
& " Null, " _
& " Null, " _
& " Null, " _
& " Null, " _
& " 'Monday'" _
& ")"

The idea is to build a SQL statement that actually instructs the
database engine to insert Null in the designated fields.
 
Back
Top