Index errors and Nulls

[dpj]

Having difficulties inserting into this field in an Access 2003 table:
LicenseNumber:
datatype=text, required=no, allow zero length=no, indexed=Yes(no duplicates)
User may not have the data available at the time the record is inserted.

Using data from an unbound textbox (tboLicense) on an AddRecord form, I have
as part of an SQL Insert statement:

A)
" & Nz(Me.tboLicense, "Null") & " --> fine if textbox has no data,
generates type mismatch error otherwise. I understand why, text field without
quotes.
B)
'" & Nz(Me.tboLicense, "Null") & "' --> fine if textbox has data, otherwies
tries to insert the string 'Null' into the field LicenseNumber. Obviously,
duplicate values in index error will prevent the insertion from happening a
second time.
C)
'" & Nz(Me.tboLicense, vbNull) & "' --> fine if textbox has data, otherwies
tries to insert the string '1' into the field LicenseNumber.
D)
'" & Me.tboLicense & "' --> fine if textbox has data. If no data, the SQL
Insert statement puts '' (single quote single quote) for this field and I get
duplicate values in the index error
D)
tried various permutations with allow zero length string=yes, no luck.

I welcome any assistance.

 

[John Vinson]

Using data from an unbound textbox (tboLicense) on an AddRecord form, I have
as part of an SQL Insert statement:

Try

IIf(IsNull(Me.tboLicense), Null, "'" & Me.tboLicense & "'")

You're having problems because you're always trying to insert SOME
sort of text string; the text string "Null" is not the same as the SQL
operator Null. I don't think you can use NZ() in this instance!

John W. Vinson[MVP]

 

[dpj]

Hi John,

Thanks for your assistance.

I was able to use the Nz function after some tweaking. Here's what worked:

" & Nz("'" + Me.tboLicense + "'", "Null") & "