MS access 2003- update null fields

[bambo]

Hi,

I'm trying to update an unbound form fields back into the table using a recordset.

I'm having troubles with Null values.

Here's the code:

rst!Field1= nz(me.field1,Null)

- However, this seems to fail no matter what I put for the nz null value.

The only thing that seems to work is pretty clumsy:

If Nz(Me.Field1, "") = "" Then
rst!Field1 = Null
Else
rst!Field1 = Me.Field1
End If

Any other suggestions?

Thanks,

[BillBoeBaggins]

What is "Nz" is that a custom function or a function I have never seen before?
What errors are you getting, or what is it doing that your not sure why?

Can this "Nz" function accept Null parameters?

[Smartacus]

Nz lets you return a value when a variant is null

[bambo]

So, any ideas about this?

[BillBoeBaggins]

rst!Field1= nz(me.field1,Null)

So am I reading this right?
If me.field1 Is Null Then Null Else Me.Field1
......
So I take it the field is not true Null it is "", but you want it to be loaded with Null if "". So your Nz is not returning Null back on your first line because it is "", Not Null.

Do you have field restrictions that are preventing it from being assigned ""?

[bambo]

Hi,

I'm not sure I understand the question but it is an unbound form, and the DB is mySQL.

Thanks

[Siddharth Rout]

Try this

Dim varFld
varFld = Me.Field1
rst!Field1= nz(varFld,Null)