Solved

How replace empty number field with a "0.00"

Posted on 2013-06-14
8
339 Views
Last Modified: 2013-06-14
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])
0
Comment
Question by:SteveL13
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 11

Expert Comment

by:Guru Ji
ID: 39247558
you can try this

IIf(Nz(1000.value) = vbNullString,0,[1000])
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 39247561
If this is a numeric field...it will not = "".
iif(isnull([1000]),0,[1000])

scott c
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 39247567
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:SteveL13
ID: 39247593
IIf(Nz(1000.value) = vbNullString,0,[1000])  did not work.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39247620
This should do for the value in _another_ field than [1000]:

=Nz([1000],0)

/gustav
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39247634
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
 

Author Comment

by:SteveL13
ID: 39247677
=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
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 39247793
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question