Skip Null and Zero Values In SQL Statement

[cjmitton]

I have a data collection form for addresses / other details which will then get inserted in to a MS-SQL 2014 DB. The form has been created in Word 2016 using VBA and I'm currently stuck on the following issue.

I have two fields in my MS-SQL database which are integers clientnum and matternum they both allow null values.

My form has various textboxes for data entry, The code is as follows for when I get the values:


CliNum = TxtBxCliNum.Value
If CliNum = "" Then
ClientNum = 0
Else
If Not IsNumeric(CliNum) Then
MsgBox "Client Number must numbers", vbOKOnly, "Error"
Exit Sub
Else
ClientNum = CLng(CliNum)
End If
End If

MattNum = TxtBxMattNum.Value
If MattNum = "" Then
MatterNum = 0
Else
If Not IsNumeric(MattNum) Then
MsgBox "Matter Number must numbers", vbOKOnly, "Error"
Exit Sub
Else
MatterNum = CLng(MattNum)
End If
End If

At the moment if the textbox is empty I've assigned it the value of '0' but really I want it to be Null. The reason for setting it to 0 at the moment helps with debugging certain elements but need this to be Null really.

I then use the following insert statement insert the data in to the relevant table:


rs.Open "INSERT INTO Address (Worktype, AddType, Name, Addressee, Address1, Address2, Address3" & _
", Address4, Address5, PostCode, Ref1, OurRef, yourRef, ClientNum, MatterNum, AddDeleted) " & _
"VALUES ('" & WorkType & "', '" & AddType & "', '" & Name & "', '" & Addressee & "', '" & Address1 & "', '" & Address2 & "', '" & Address3 & "', '" & Address4 & "', " & _
"'" & Address5 & "', '" & PostCode & "', '" & Ref1 & "', '" & OurRef & "', '" & yourRef & "', '" & ClientNum & "', '" & MatterNum & "', 'False')", cn


The issue is that I want to insert null values in to clientnum and matternum when there is nothing to enter. I've done a lot of googling on this and from what I've read that if I want to get a null value for the 'integers' I'm inserting (clientnum and matternum) I should just leave them out of the SQL statement.

For this example I'd need 2 SQL statements one for with those records one without and use some form of logic to work out which statement to use. This is 'okay' for this particular circumstance but for some others I will come across it will not work so well as I could end up with 5 or 8 different SQL statements in various scenarios.

If there a way for doing this within the SQL statement itself?

[SamT]

If TxtBxCliNum = 0 Or TxtBxCliNum = "" Then ClientNumber = Null

[cjmitton]

Thanks, I've tried setting the value to Null or vbNull but neither works. As the value of this field gets sent to my MS-SQL DB and the field that is receiving this value is an integer it will not accept a null value being sent to it and put a 0 (Zero) instead.

I'm trying to find a way in the SQL Insert statement to 'skip' that field if the value is null.

So for example the last line of the SQL Statement I've used as a temp measure is like this...


"'" & Address5 & "', '" & PostCode & "', '" & Ref1 & "', '" & OurRef & "', '" & yourRef & "', , ,'False')", cn


I'm just not sure if I can 'programme' that back in SQL statement so I have only one SQL statement instead of the two I have running at the moment.

[SamT]

Since the statement is a String

Stmnt = "FirstPartOfStatement"
If ClientNumber > 0 Then Stmnt = Stmnt & ClientNumber
Stmnt = Stmnt & LastPart


You'll need to dress that up to fit SQL syntax, but you get the idea.

BTW, I changed the thread Title to better reflect the issue.

[cjmitton]

Thanks for that will work for the situation I have.