Solved

How replace empty number field with a "0.00"

Posted on 2013-06-14
8
337 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
 

Author Comment

by:SteveL13
ID: 39247593
IIf(Nz(1000.value) = vbNullString,0,[1000])  did not work.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

914 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now