[AccessD] Insert Into with Null Fields

Gustav,

This is interesting!  This looks like something of a syntax trick to build
the string you want.

Thanks!
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Monday, November 12, 2007 2:43 AM
To: accessd at databaseadvisors.com
Subject: Re: [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


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com