How to assign NULL or EMPTY to Numeric Field

[Punchinello]

I've read bunches of threads that address NULL and EMPTY issues but none that demonstrates how to assign a NULL or EMPTY value to a numeric or date field. For example, I'm using standard edit controls and assigning their .text properties from an Access table in code like so:

Ctrl.Text = IIf(IsNull(rs!ADate), "", Format$(rs!ADate, "MM/DD/YYYY"))

But then a user will do this:
1. Enter a date and save the record to the database.
2. Return to the record, blank out the date and save it.

So I want a statement like one of these (neither of which actually work):

rs!ADate = IIf(Ctrl.Text = vbNullString, Null, CDate(Ctrl.Text))

or

rs!ADate = IIf(Ctrl.Text = vbNullString, Empty, CDate(Ctrl.Text))

And a similar solution for assigning to number fields. Any ideas?

 

[dvannoy]

I use the following

If txtDate.Text & "" = "" Then
rs.Fields("FieldName&quot = Null
Else
rs.Fields("FieldName&quot = txtDate.Text
End If

 

[LazyMe]

I think the problem lies in the use of vbNullString in your comparison.

I think that edit control returns a zero-length string (which is not the same as a vbNullString).


Try this:
rs!ADate = IIf(Len(trim(Ctrl.Text)) = 0, Null, CDate(Ctrl.Text))


Greetings,
Rick

 

[Punchinello]

Thanks for the fast help! Using replies from dvannoy and LazyMe (and my own little pea-sized brain) I've determined that the problem is related to the use of the IIf function in that the following statement:

rs!ADate = IIf(CtrlIsBlank, Null, CDate(Ctrl.Text))

evaluates the expression CDate(Ctrl.Text) whether or not this value will be used as the result of the function. Therefore, unless Ctrl.Text reflects a valid date, this line will crash. Like in dvannoy's example, my solution is to replace this line with:

If CtrlIsBlank Then
  rs!ADate = Null
Else
  rs!ADate = CDate(Ctrl.Text)
End If

Thanks again!