invalid use of null (again, yea, I know... sorry.)

[madEG]

Ok, I am losing my mind... I have no idea why this is happening - that being why I can't store a null value, sourced from a table via a query into a variable, that is to be later used in an insert. When I get to the part where I set the value for the variable I get the evil: Invalid use of Null: Error 94.

I imagine it must be something basic since I have no idea what is wrong with this.

Private Sub btnImportAll_Click()
    
    MsgBox ("Calling Bulk Import")

    strSQL = "Select * From tblImportContacts"
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    If rst.RecordCount > 0 Then
        'MsgBox ("rst.RecordCount:" & rst.RecordCount)
        rst.MoveFirst
        Do While Not rst.EOF
            MsgBox ("attempting to insert member")
            
            InsertTblMember
'            InsertTblAddress
'            InsertTblMemberAddress
'            InsertTblMemberCommunications
'            InsertTbListMembers
        rst.MoveNext
        Loop
    End If

End Sub


Function InsertTblMember()
    
          MsgBox ("Calling Insert TblMember(1)")
    
    txtLname = rst("Lname").Value
              MsgBox ("txtLname : " & txtLname)
    txtFname = rst("Fname").Value
              MsgBox ("txtFname: " & txtFname)
    txtSuffix = rst("Suffix").Value
              MsgBox ("txtSuffix: " & txtSuffix)
txtSalutation = rst("Salutation").Value       '<------  Invalid Use of Null here
              MsgBox ("txtSalutation after: " & txtSalutation)

   
    ' insert new record(s) with updated values
    strSQL = "INSERT INTO tblMember (lname, fname, salutation, suffix) VALUES (""" & txtLname & """,""" & txtFname & """,""" & txtSalutation & """,""" & txtSuffix & """);"
    'MsgBox ("strSQL: " & strSQL)
    DoCmd.RunSQL strSQL


    txtLname = ""
    txtFname = ""
    txtSuffix = ""
    txtSalutation = ""

[code]


Why can't I store null in that txtSalutation variable, which I have declared as a string and a public var?

I've even tried:

txtSalutation = nz(rst("Salutation").Value, null)

I *want* to store a null for when I use this variable for the insert.

What gives?

What am I doing wrong?

Ugh.

Thanks for any thoughts...  I need a drink...

 

[boblarson]

You can't assign a NULL to a string variable. If you need to be able to assign a null to a variable you can use a VARIANT (only variable which can receive a null value).

 

[madEG]

Oh man... I figured it would be something basic

Since I can't always predict (building a data loader for end users) what fields will be blank, I guess I'll need to set all the variable for all the fields to variant.

But, I wonder, am I thinking about this the wrong way - the design for the db's table says "allow zero length = NO" for all the fields... Doesn't that mean when the user doesn't store (using the bound forms) a value in a field it sets to null? meaning, when the forms for the normal DB (not this bulk loader I am trying to make) are used, doesn't a text field on a form with no value push a null into the text field?

I know a string/char variable is not the same as a char/text field - but this is hard for me to get my head around.

maybe that drink should be coffee...

Thanks Bob!

 

[boblarson]

When inserting records it doesn't put a NULL into the field. NULL is the absence of anything. Allowing Zero Length means a "" or vbNullString (constant name for empty string) can be put in the field and that is stored there if zero-length are allowed AND the user puts it there. If the empty string is not placed there explicitly and there has never been data in there then a NULL exists.

Hope that helps to clear it up.

 

[madEG]

Yes, that totally makes sense. Thanks for the followup.

 

[gemma-the-husky]

underneath all this is the question of whether you do want a null, or whether you want a value of zero, or a zero length string.

in which case you can do this with


mytext = nz(somefield,"")

myvalue = nz(somefield,0)

which sets a value in the case of the data being null.

---------------
I dislike nulls intensely, as many queries and other things can fail because of unhandled nulls, and I try to avoid them wherever possible. To my mind, they are quite a technical thing, and I am sure users wouldnt really understand the difference between a null and a ""

 

[warriorjoe]

Try "Dim"-ing the variable as a VARIANT in the function where you are using it and it will probably work.

 

[boblarson]

Try "Dim"-ing the variable as a VARIANT in the function where you are using it and it will probably work.

Apparently you didn't read the thread too closely. I had said the same thing (not in those words) in post #2 of this thread.

 

[warriorjoe]

Sorry, Bob. You're right - I didn't see ANY of the reply posts. I was, admittedly, asleep on my feet.

Joe

 

[boblarson]

Sorry, Bob. You're right - I didn't see ANY of the reply posts. I was, admittedly, asleep on my feet.

Joe

Your FIRED! - if you were asleep on your feet, I'd like to be there right now. My day is just beginning but I want to go back to bed.