Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Null Values in Text Boxes

Status
Not open for further replies.

jh1234

Technical User
Jun 9, 2004
5
US
I have an expression in a calculated text box but if the expression is null, i need to have a zero as a value. How do I do this? There isn't a default value box in the properties.

Thanks in advance.
 
JH,
Are you sure that there isn't a default value property? I don't think I've ever seen a text box w/out one. Should be under text box - properties/data/Default Value
You should set it to "0" if you want it to display a zero.
Maybe I'm misreading, or maybe you just missed it. I hope that it works!

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------
 
I have checked several times. On a report, a text box does not have a default value box only a control box. I set my control for the calculation I wanted but need a default incase no information fits the criteria of the calculation (this is a DSUM function calculation).
 
Your original post did not specify that you were working with a report. You will need to do something like the following in you control:

IIf(Your calculation = Null, "0", You calculation)

Should provide you with the direction to get what you want....Let us know if you need more assistance. The secret to creativity is knowing how to hide your sources. - Albert Einstein [spin]

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
 
Thanks for the info. I tried it a couple times, but no luck yet. Here is the expression I am using for the calculation.
=DSum(" [S+C Contribution-August] ","Past Payments Histories"," [Contract] = '24445' And [Landlord] = 'Bolton House' AND [Exit Date] is NULL")

I guess my problem with your suggestion ist that I don't know how to properly imbed my expression into your expression(I don't know the proper punctioation to use when I already have so many other things going on in the original expression.)

By the way, thanks for responding. I started a new thread called "No Default VAlues in Text Boxes" before you responded, thinking I needed to clarify what i was trying to do. If that helps to make things clearer.
 
No problem....

If this statement:

=DSum(" [S+C Contribution-August] ","Past Payments Histories"," [Contract] = '24445' And [Landlord] = 'Bolton House' AND [Exit Date] is NULL")

is what produces the Null then try replacing the control source with the following

IIf(IsNull(DSum(" [S+C Contribution-August] ","Past Payments Histories"," [Contract] = '24445' And [Landlord] = 'Bolton House' AND [Exit Date] is NULL")), "0", DSum(" [S+C Contribution-August] ","Past Payments Histories"," [Contract] = '24445' And [Landlord] = 'Bolton House' AND [Exit Date] is NULL")) The secret to creativity is knowing how to hide your sources. - Albert Einstein [spin]

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
 
You Rock!!!!!!

Thank You so much. It worked like a charm, but one thing. Add a space between IsNull and ( in the beginning, otherwise it give a syntax error message. Other than that, It is perfect. Thank you so much :)
 
Did you ever look at the NZ() function. I have used it very successfully in the past to avoid these null issues.

 
No. What is NZ() function. Access help (the incomplete version installed on my computer) does not have this and I haven't found it in the built-in functions. Please elaborate.
 
This is directly from Access97 help so forgive the formatting. The NZ function has saved me lots of heartache in the past.


You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.

Syntax

Nz(variant[, valueifnull])

The Nz function has the following arguments.

Argument Description
variant A variable of data type Variant.
valueifnull Optional (unless used in a query). A Variant that supplies a value to be returned if the variant argument is Null. This argument enables you to return a value other than zero or a zero-length string. If you use the Nz function in an expression in a query without using the ValueIfNull argument, the results will be empty in the fields that contain null values
If the value of the variant argument is Null, the Nz function returns the number zero or a zero-length string, depending on whether the context indicates the value should be a number or a string. If the optional valueifnull argument is included, then the Nz function will return the value specified by that argument if the variant argument is Null.
If the value of variant isn't Null, then the Nz function returns the value of variant.

Remarks

The Nz function is useful for expressions that may include Null values. To force an expression to evaluate to a non-Null value even when it contains a Null value, use the Nz function to return a zero, a zero-length string, or a custom return value.
For example, the expression 2 + varX will always return a Null value when the Variant varX is Null. However, 2 + Nz(varX) returns 2.
You can often use the Nz function as an alternative to the IIf function. For example, in the following code, two expressions including the IIf function are necessary to return the desired result. The first expression including the IIf function is used to check the value of a variable and convert it to zero if it is Null.

varTemp = IIf(IsNull(varFreight), 0, varFreight)
varResult = IIf(varTemp > 50, "High", "Low")

In the next example, the Nz function provides the same functionality as the first expression, and the desired result is achieved in one step rather than two.

varResult = IIf(Nz(varFreight) > 50, "High", "Low")

If you supply a value for the optional argument valueifnull, that value will be returned when variant is Null. By including this optional argument, you may be able to avoid the use of an expression containing the IIf function. For example, the following expression uses the IIf function to return a string if the value of varFreight is Null.

varResult = IIf(IsNull(varFreight), "No Freight Charge", varFreight)

In the next example, the optional argument supplied to the Nz function provides the string to be returned if varFreight is Null.

varResult = Nz(varFreight, "No Freight Charge")
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top