How replace empty number field with a "0.00"

On a form field I have a number field.  It the user clears the value in the field I want to replace it with a 0.00.

I've tried this but it doesn't work: (the field name is "1000"

=IIf([1000]="",0,[1000])
SteveL13Asked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
Auch. You can use WithEvents to extend the form class module, but that's a learning experience.

Another option is to create a helper subfunction:

Public Sub UpdateNullToZero(ByRef ctl As Control)
  If IsNull(ctl.Value) Then
    ctl.Value = 0
  End If
End Sub

Now, in the AfterUpdate events you just need to paste:

  Call UpdateNullToZero(Me.ActiveControl)

/gustav
0
 
Guru JiCommented:
you can try this

IIf(Nz(1000.value) = vbNullString,0,[1000])
0
 
clarkscottCommented:
If this is a numeric field...it will not = "".
iif(isnull([1000]),0,[1000])

scott c
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
clarkscottCommented:
Plus... you can forma a 0 to look like 0.00  ... but 0.00 is not a legitimate number.
1.0 = 1
1.1 = 1.1

0.00 = 0

etc.

scott C
0
 
SteveL13Author Commented:
IIf(Nz(1000.value) = vbNullString,0,[1000])  did not work.
0
 
Gustav BrockCIOCommented:
This should do for the value in _another_ field than [1000]:

=Nz([1000],0)

/gustav
0
 
Gustav BrockCIOCommented:
If you only have this field, use this in the AfterUpdate event:

  If IsNull(Me![1000].Value) Then
    Me![1000].Value = 0
  End If

/gustav
0
 
SteveL13Author Commented:
=Nz([1000],0)  did not work.

  If IsNull(Me![1000].Value) Then
    Me![1000].Value = 0
  End If

did work but I have 45 fields to code.
0
All Courses

From novice to tech pro — start learning today.