Inserting Null for an optional Number Field 1

[infomania]

I have an unbound form which is used to insert row data into a table. Several fields are optional. I am using the NZ(StrField, "") function to insert a blank in the text fields. For number fields, which are mostly FK indexes, that are optional, I would like to have a Null in the field, not a zero. Here is the insert code that works, except for the optional number field (BRD_RECORD):

Private Function insertRecord()
Dim strSQL As String
strSQL = "INSERT INTO AUCT_ITEMS "
strSQL = strSQL + "(AUCTYEAR, ITEM_NBR, ITEMNAME, "
strSQL = strSQL + "DESCRIPTION, DONOR_RECORD, BRD_RECORD, "
strSQL = strSQL + "AUCTYPE, RETAIL, MINIMUM, INCREASE, "
strSQL = strSQL + "TYPECODE) VALUES "
strSQL = strSQL + "(" & Me.AUCTYEAR
strSQL = strSQL + "," & Me.ITEMNUMBER
strSQL = strSQL + ",'" & SqlString(Me.ITEMNAME) & "'"
strSQL = strSQL + ",'" & SqlString(Nz(Me.DESCRIPTION, "")) & "'"
strSQL = strSQL + "," & Me.DONOR_RECORD
strSQL = strSQL + "," & Nz(Me.BRD_RECORD, 0)
strSQL = strSQL + ",'" & SqlString(Nz(Me.AUCTYPE, "")) & "'"
strSQL = strSQL + "," & Me.RETAIL
strSQL = strSQL + "," & Nz(Me.MINIMUM, 0)
strSQL = strSQL + "," & Nz(Me.INCREASE, 0)
strSQL = strSQL + "," & Me.TYPECODE & ")"
'MsgBox strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Function

Is there some way of doing the insert and leaving the field blank? I am using Access 2003 (as Access 2000).

 

[PHV]

Have you tried to replace this:
strSQL = strSQL + "," & Nz(Me.BRD_RECORD, 0)
By this ?
strSQL = strSQL + "," & Nz(Me.BRD_RECORD, "Null")
Or this (in case of type mismatch error) ?
strSQL = strSQL + "," & IIf(IsNull(Me.BRD_RECORD), "Null", Me.BRD_RECORD)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[infomania]

Thank you! Nz(Me.BRD_RECORD, "Null") did the trick. You get a star for this one.