Consulting

Results 1 to 5 of 5

Thread: Skip Null and Zero Values In SQL Statement

  1. #1
    VBAX Regular
    Joined
    Sep 2012
    Posts
    63
    Location

    Skip Null and Zero Values In SQL Statement

    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?

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,971
    Location
    If TxtBxCliNum = 0 Or TxtBxCliNum = "" Then ClientNumber = Null
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Sep 2012
    Posts
    63
    Location
    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.

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,971
    Location
    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.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  5. #5
    VBAX Regular
    Joined
    Sep 2012
    Posts
    63
    Location
    Thanks for that will work for the situation I have.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •