How save Null fields to a table

[mar_t]

Good Day,

I've created (thanks to Google) an sql statement to INSERT field/s values into my table and it works fine. But, how if a field or fields are empty or Null. As of this writing I'm still searching the web for answers. Any inputs are greatly appreciated.

Private Sub cmdSave_Click()
sql = "Insert into tblEmployee(LastName,FirstName) Values ('" & LastName & "','" & FirstName & "')"
DoCmd.RunSQL (sql)
myList.Requery
End Sub

Thank you in advance.

[Rod]

To answer your question - presumably the table fields are TEXT - I would use the Nz function. This function traps nulls and substitutes the value of your choice.

Nz(LastName, "") returns the last name or a zero length string if LastName is null. Now you need to place quotation marks around the string. I suggest the values expression should be written as:

 
" ... Values ('" & Nz(LastName, "") & "', '" & Nz(FirstName, "") & "')"

[mar_t]

Hi Rod,

Thank you, I'll try the code. How if the table field is a number?

[Rod]

I think I have mislead you somewhat.



Here's the situation as I know it.

• All controls may have a value of null. In particular unless you take specific action, unbound text boxes, combo boxes and list boxes are initialised with null.
• Text and number table fields may also contain null. (You have however specifically to allow a text field to contain a zero-length string. This is where I did not explain properly in my first post.)
• Only a variant type VBA variable can contain a null.

Thus using INSERT INTO with null values should cause no problem with either text or number fields, unless the 'Required' property is set to 'Yes' for that field.

[mar_t]

Hi Rod,

The code works with table field/s with Text as the data type but I received a pop-up message " Microsoft access didn't update 1 field(s) due to a type conversion error..." for null field/s with 'Numbers' as the data type. In my tblEmployee i have a field PhoneNo and the data type is Numbers and the 'Required' property is set to 'No'. Help.

[Rod]

Hold up a moment. Which code are we talking about?

Nz(PhoneNumber, "") will not work for numeric table fields because a zero-length string is not numeric. If you don't want a null in your numeric table field the correct syntax is Nz(PhoneNumber,0).

" ... Values ('" & Nz(LastName, "") & "', '" & Nz(FirstName, "") & "', " & Nz(PhoneNumber, 0) & ")"

I include the above example because the quotation mark syntax is slightly different for numeric fields.