Avoid Invalid use of Null error when the field is empty

Question

I created a form in Access 2010 and I fill in the form based on text boxes (B and C) and selection form a combobox (A). The problem is if any of the text box left empty I get “Invalid use of Null” error. I noticed I can avoid this error if I Dim the text boxes as Variant instead of Integer. I am not sure if this is the right solution. Can I change the following script to avoid this error?

Private Sub ABCBoxEnter_Click()
Dim A As String
Dim B As Integer
Dim C As Integer
If Not IsNull(Me!ComboBox.Value) Then
    A = Me!ComboBox.Value
    B = Afield
    C = Bfield
    values = "VALUES ("
    values = values & "'" & ID & "','" & A & "','" & B & "','" & C & "')"
    SQL = "INSERT  INTO ContactTable (ID, A, B, C)"
    SQL = SQL & values
    DoCmd.RunSQL SQL
    Me.B.Value = ""
    Me.C.Value = ""
End If
End Sub

Gustav · Accepted Answer · 2017-08-15T10:48:56.623

1

You can use Nz:

values = values & "'" & Nz(ID) & "','" & Nz(A) & "','" & Nz(B) & "','" & Nz(C) & "')"

Or, better, implement my function CSql

edited Aug 15 '17 at 10:48

answered Aug 15 '17 at 10:45

Gustav

53,498
7
29
55

That will throw an invalid use of null on `A = Me!ComboBox.Value` if `Me!ComboBox.Value` is `Null`, except when `A` is specified as a `Variant`. `A = CSql(Me!ComboBox.Value)` and the same for `B` and `C` would be a more valid approach when going for `CSql`. Then `B` and `C` should be a string. – Erik A Aug 15 '17 at 10:47
@ErikvonAsmuth: No, that is ruled out by the _If_ just before (the line above). But see my added link to _CSql_. – Gustav Aug 15 '17 at 10:51
Thanks for the answers. I used the Nz function as follows: '" & Nz(B, “N/A”) & "','" & Nz(C, “N/A”) & "' and it is working fine with the first data entry and I can see N/A where there is no entry but for the following entries I just received blank cells where no entries!! Any idea why this is happing and how can I fix it? – Dan Wood Aug 15 '17 at 14:26
Sorry, I have past the bit I am interested in. The full command is: values = values & "'" & Nz(ID) & "','" & Nz(A) & "','" & Nz(B, “N/A”) & "','" & Nz(C,“N/A”) & "')". I did not use Nz with ID or A. Even when I used with ID and A, it did not solve the issue. – Dan Wood Aug 15 '17 at 14:53
Still, if ID is a key, and with that name it should be, it must be assigned a value. – Gustav Aug 15 '17 at 15:14
The ID in this table - which is not a primary key - is populated via a relationship from another table - which is the main table with ID as a primary key. – Dan Wood Aug 15 '17 at 17:31
Then it is supposed to have a value. If not, as you have seen, the insert will fail. – Gustav Aug 15 '17 at 20:21
Thanks Gustav, the problem was in Me.B.Value = "" Me.C.Value = "". I changed "" to Null and I can get N/A in any empty entry :) – Dan Wood Aug 16 '17 at 10:25

score 1 · Answer 2 · answered Aug 15 '17 at 10:46

The big question is: what do you want to happen when a value is Null.

As you noticed, integers and strings can't contain Null, while a Variant can. Dimming your intermediary variables as Variant is an approach that's okay in your code. You can also use the Nz function to replace Null with an empty string if referred to as a string, or 0 if referred to as an integer.

Note that if you were to remove the ' apostrophes from your SQL statement, you would run into an error, and you would have to use Iif(IsNull(B), "Null", B) where you just have B in the SQL statement.

Avoid Invalid use of Null error when the field is empty

2 Answers2