Solved

How replace empty number field with a "0.00"

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

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
type of query 11 42
Passing variables to a function 6 34
Access Crosstab Query with Multiple Values 4 32
deduplicating based on criteria 2 21
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

770 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