[AccessD] Insert Into with Null Fields

Hi Dan

If your fields doesn't allow for zero length strings but do accept Nulls, you can use:

' Replace zero-length strings with 'Null' and wrap strings in quotes.
varA = IIf(Len(Nz(txtA), vbNullString)=0, "Null", "'" & txtA & "'")
varB = IIf(Len(Nz(txtB), vbNullString)=0, "Null", "'" & txtB & "'")
varC = IIf(Len(Nz(txtC), vbNullString)=0, "Null", "'" & txtC & "'")

' Build SQL string with the word Null for empty variables.
Stg = "INSERT INTO tblMain ( A, B, C )" _
& " VALUES (" & varA & ", " & varB & ", " & varC & ")"

/gustav


>>> dwaters at usinternet.com 11-11-2007 20:07 >>>
Sometimes when I write an INSERT INTO string, I have to take into account
that a text box or memo field might be null.  If there is one possibility of
a Null, I have to write and choose from 2 different strings, if there are
two possibilities of Null fields, then I have to write and choose from 4
different strings, and so on.

Is there a way out of this?

Thanks!
Dan