Calculated Controls Nz IIf

[Robert5833]

Good day all;
I’m trying to calculate and display dates as calculated controls in a series
of Text Boxes on a form, but having problems with zero length or Null values.
I am using a time stamp date from Text Box (1), using a numeric value from
Text Box (2) to calculate an “extended†date (as date) displayed in Text Box
(3).

This series works fine, except for the extended date in Text Box (3), which
displays #Error where there is no numeric value in Text Box (2) (because it
is not a criterion for every record).

I can resolve the displayed #Error by entering “0†as a value (rather than
Null or empty) in Text Box (2) for those records which do not have criterion
of “equal to or greater than 1,†but that results in negative values for
other calculated controls on the form.

Is there an expression/syntax to allow either a Null value, empty, or zero
length string in the underlying table, so that I don’t get Null propagation
errors, and so those fields which do not have a multiplier greater than “0â€
do not display any result?

Calculated Control (Text Box) with #Error:
Control Source;
=DateAdd("m",(+[ComboBoxWithNumMultiplier]),[TextBoxWithDateStampRecord])

(Note: ComboBoxWithNumMultiplier above is Format “None†with Row Source on a
table field; Data Type Number)

I can’t seem to find the right syntax to incorporate the Nz, or IIf functions.
Any suggestions would be greatly appreciated.

Thank you,
Robert5833

 

[Dick D]

Yes, you can incorporate the NZ function in your calculation:
=DateAdd("m",NZ([ComboBoxWithNumMultiplier],0),[TextBoxWithDateStampRecord])

The NZ function uses the value of the field, when null - it substitues the
second value (0).

 

[BruceM]

If I understand correctly, you are trying to add to a date in Text1 the
number of months specified in Text2, and have the result appear in Text3.
If so, try this as the Control Source of Text 3:
=DateAdd("m",[Text2],[Text1])
To allow for Text2 being null you could have:
=DateAdd("m",Nz([Text2],0),[Text1])
This will add zero to today's date. You could choose another number than 0
if you prefer to default to adding one month or whatever in case of a Null
in Text2.
With Nz you have to decide what value you want to use instead of null. The
default, I think, is an empty string, which won't help since you need a
number.

 

[Robert5833]

Thank you Dick D;
With your help I was able to get the syntax right for the Nz function in the
DateAdd expression. I tried it out and it works well! This will help me in a
dozen other instances that I’ve struggled with the Nz function!

And thanks again for the quick reply!
Robert5833

Yes, you can incorporate the NZ function in your calculation:
=DateAdd("m",NZ([ComboBoxWithNumMultiplier],0),[TextBoxWithDateStampRecord])

The NZ function uses the value of the field, when null - it substitues the
second value (0).

--
Hopefully helpful,
Dick D

Good day all;
I’m trying to calculate and display dates as calculated controls in a series
of Text Boxes on a form, but having problems with zero length or Null values.
I am using a time stamp date from Text Box (1), using a numeric value from
Text Box (2) to calculate an “extended†date (as date) displayed in Text Box
(3).

This series works fine, except for the extended date in Text Box (3), which
displays #Error where there is no numeric value in Text Box (2) (because it
is not a criterion for every record).

I can resolve the displayed #Error by entering “0†as a value (rather than
Null or empty) in Text Box (2) for those records which do not have criterion
of “equal to or greater than 1,†but that results in negative values for
other calculated controls on the form.

Is there an expression/syntax to allow either a Null value, empty, or zero
length string in the underlying table, so that I don’t get Null propagation
errors, and so those fields which do not have a multiplier greater than “0â€
do not display any result?

Calculated Control (Text Box) with #Error:
Control Source;
=DateAdd("m",(+[ComboBoxWithNumMultiplier]),[TextBoxWithDateStampRecord])

(Note: ComboBoxWithNumMultiplier above is Format “None†with Row Source on a
table field; Data Type Number)

I can’t seem to find the right syntax to incorporate the Nz, or IIf functions.
Any suggestions would be greatly appreciated.

Thank you,
Robert5833

 

[Robert5833]

Thank you BruceM;
With your help I’ve got that control working properly now with no #Error.

The only lingering issue I have is that some of my calculated controls
return a negative value for those records that calculate on the “0†value
added by the Nz function. (I have a control that uses Text (3) to evaluate
“months remaining†in numerical terms, which displays –(X) for those “0â€
value calculations.)

The underlying problem seems to be with the DateAdd or DateDiff functions?

Would a better approach for my situation be a macro or code string that sets
the Control Property to Null if the returned value is equal to or less than 0?

Thanks again!
Robert5833

If I understand correctly, you are trying to add to a date in Text1 the
number of months specified in Text2, and have the result appear in Text3.
If so, try this as the Control Source of Text 3:
=DateAdd("m",[Text2],[Text1])
To allow for Text2 being null you could have:
=DateAdd("m",Nz([Text2],0),[Text1])
This will add zero to today's date. You could choose another number than 0
if you prefer to default to adding one month or whatever in case of a Null
in Text2.
With Nz you have to decide what value you want to use instead of null. The
default, I think, is an empty string, which won't help since you need a
number.

Good day all;
I’m trying to calculate and display dates as calculated controls in a
series
of Text Boxes on a form, but having problems with zero length or Null
values.
I am using a time stamp date from Text Box (1), using a numeric value from
Text Box (2) to calculate an “extended†date (as date) displayed in Text
Box
(3).

This series works fine, except for the extended date in Text Box (3),
which
displays #Error where there is no numeric value in Text Box (2) (because
it
is not a criterion for every record).

I can resolve the displayed #Error by entering “0†as a value (rather than
Null or empty) in Text Box (2) for those records which do not have
criterion
of “equal to or greater than 1,†but that results in negative values for
other calculated controls on the form.

Is there an expression/syntax to allow either a Null value, empty, or zero
length string in the underlying table, so that I don’t get Null
propagation
errors, and so those fields which do not have a multiplier greater than
“0â€
do not display any result?

Calculated Control (Text Box) with #Error:
Control Source;
=DateAdd("m",(+[ComboBoxWithNumMultiplier]),[TextBoxWithDateStampRecord])

(Note: ComboBoxWithNumMultiplier above is Format “None†with Row Source on
a
table field; Data Type Number)

I can’t seem to find the right syntax to incorporate the Nz, or IIf
functions.
Any suggestions would be greatly appreciated.

Thank you,
Robert5833

 

[BruceM]

It would have helped had you posted the expression that is returning the
negative result, and stated what you would have it do if the result is less
than 0. In general you would need to test for that. To substitute a 0 if
Text2 is null or less than 0 you could do:
IIf(Nz([Text2],0)<0,0,Nz([Text2],0))

In some cases you can assign a Default Value of 0 to a control, and avoid
the use of Nz.

Thank you BruceM;
With your help I’ve got that control working properly now with no #Error.

The only lingering issue I have is that some of my calculated controls
return a negative value for those records that calculate on the “0†value
added by the Nz function. (I have a control that uses Text (3) to evaluate
“months remaining†in numerical terms, which displays –(X) for those “0â€
value calculations.)

The underlying problem seems to be with the DateAdd or DateDiff functions?

Would a better approach for my situation be a macro or code string that
sets
the Control Property to Null if the returned value is equal to or less
than 0?

Thanks again!
Robert5833

If I understand correctly, you are trying to add to a date in Text1 the
number of months specified in Text2, and have the result appear in Text3.
If so, try this as the Control Source of Text 3:
=DateAdd("m",[Text2],[Text1])
To allow for Text2 being null you could have:
=DateAdd("m",Nz([Text2],0),[Text1])
This will add zero to today's date. You could choose another number than
0
if you prefer to default to adding one month or whatever in case of a
Null
in Text2.
With Nz you have to decide what value you want to use instead of null.
The
default, I think, is an empty string, which won't help since you need a
number.

Good day all;
I’m trying to calculate and display dates as calculated controls in a
series
of Text Boxes on a form, but having problems with zero length or Null
values.
I am using a time stamp date from Text Box (1), using a numeric value
from
Text Box (2) to calculate an “extended†date (as date) displayed in
Text
Box
(3).

This series works fine, except for the extended date in Text Box (3),
which
displays #Error where there is no numeric value in Text Box (2)
(because
it
is not a criterion for every record).

I can resolve the displayed #Error by entering “0†as a value (rather
than
Null or empty) in Text Box (2) for those records which do not have
criterion
of “equal to or greater than 1,†but that results in negative values
for
other calculated controls on the form.

Is there an expression/syntax to allow either a Null value, empty, or
zero
length string in the underlying table, so that I don’t get Null
propagation
errors, and so those fields which do not have a multiplier greater than
“0â€
do not display any result?

Calculated Control (Text Box) with #Error:
Control Source;
=DateAdd("m",(+[ComboBoxWithNumMultiplier]),[TextBoxWithDateStampRecord])

(Note: ComboBoxWithNumMultiplier above is Format “None†with Row Source
on
a
table field; Data Type Number)

I can’t seem to find the right syntax to incorporate the Nz, or IIf
functions.
Any suggestions would be greatly appreciated.

Thank you,
Robert5833

 

[Robert5833]

Thank you BruceM;

In retrospect I should have been clear about the other calculated fields and
what I wanted them to do but at the start I thought the solution might be
that Text2 could pass a Null value rather than a Null propagation. Had that
been the case Text3 wouldn’t have been an issue. Lesson learned… At any rate,
Text3 expression is: =DateDiff("m",Now(),[tboMonthLimit]) {to return a
"months remaining" numeric value}

One of my struggles has been incorporating the Nz function into the
DateDiff/DateAdd functions, which to date has resulted in a “too many
arguments†error. But I’ll attempt to blend your suggestion with the Text3
expression.

Thanks again! Your help is greatly appreciated!
Robert5833

It would have helped had you posted the expression that is returning the
negative result, and stated what you would have it do if the result is less
than 0. In general you would need to test for that. To substitute a 0 if
Text2 is null or less than 0 you could do:
IIf(Nz([Text2],0)<0,0,Nz([Text2],0))

In some cases you can assign a Default Value of 0 to a control, and avoid
the use of Nz.

Thank you BruceM;
With your help I’ve got that control working properly now with no #Error.

The only lingering issue I have is that some of my calculated controls
return a negative value for those records that calculate on the “0†value
added by the Nz function. (I have a control that uses Text (3) to evaluate
“months remaining†in numerical terms, which displays –(X) for those “0â€
value calculations.)

The underlying problem seems to be with the DateAdd or DateDiff functions?

Would a better approach for my situation be a macro or code string that
sets
the Control Property to Null if the returned value is equal to or less
than 0?

Thanks again!
Robert5833

If I understand correctly, you are trying to add to a date in Text1 the
number of months specified in Text2, and have the result appear in Text3.
If so, try this as the Control Source of Text 3:
=DateAdd("m",[Text2],[Text1])
To allow for Text2 being null you could have:
=DateAdd("m",Nz([Text2],0),[Text1])
This will add zero to today's date. You could choose another number than
0
if you prefer to default to adding one month or whatever in case of a
Null
in Text2.
With Nz you have to decide what value you want to use instead of null.
The
default, I think, is an empty string, which won't help since you need a
number.

Good day all;
I’m trying to calculate and display dates as calculated controls in a
series
of Text Boxes on a form, but having problems with zero length or Null
values.
I am using a time stamp date from Text Box (1), using a numeric value
from
Text Box (2) to calculate an “extended†date (as date) displayed in
Text
Box
(3).

This series works fine, except for the extended date in Text Box (3),
which
displays #Error where there is no numeric value in Text Box (2)
(because
it
is not a criterion for every record).

I can resolve the displayed #Error by entering “0†as a value (rather
than
Null or empty) in Text Box (2) for those records which do not have
criterion
of “equal to or greater than 1,†but that results in negative values
for
other calculated controls on the form.

Is there an expression/syntax to allow either a Null value, empty, or
zero
length string in the underlying table, so that I don’t get Null
propagation
errors, and so those fields which do not have a multiplier greater than
“0â€
do not display any result?

Calculated Control (Text Box) with #Error:
Control Source;
=DateAdd("m",(+[ComboBoxWithNumMultiplier]),[TextBoxWithDateStampRecord])

(Note: ComboBoxWithNumMultiplier above is Format “None†with Row Source
on
a
table field; Data Type Number)

I can’t seem to find the right syntax to incorporate the Nz, or IIf
functions.
Any suggestions would be greatly appreciated.

Thank you,
Robert5833

 

[BruceM]

Again, if an expression is giving you trouble, post it.

You can allow for Null in tboMonthLimit by applying Nz to it. The Value If
Null argument needs to be a date, since DateDiff works with dates. Here it
is with the Value If Null argument equal to today's date:

=DateDiff("m",Now(),nz([tboMonthLimit],Now()))

Be aware that Now() gives you the date plus the current time. Date() gives
you the date only. Actually, Date returns midnight of the current date,
which you can see if you format it to show hours, minutes, and seconds. I
use the Date function unless I specifically need the time of day. If you
use Now when all you need is the date you can get unexpected results. In
particular, two values that show the same date are not treated by Access as
the same if those values were obtained using Now(). With any database open,
press Ctrl + G to open the immediate code window. In the Immediate pane
type:
?Now() = Date()
It will be false unless it is exactly midnight. However, ?Now() = Now() or
?Date() = Date() will be true.
Just something to be aware of.

Thank you BruceM;

In retrospect I should have been clear about the other calculated fields
and
what I wanted them to do but at the start I thought the solution might be
that Text2 could pass a Null value rather than a Null propagation. Had
that
been the case Text3 wouldn’t have been an issue. Lesson learned… At any
rate,
Text3 expression is: =DateDiff("m",Now(),[tboMonthLimit]) {to return a
"months remaining" numeric value}

One of my struggles has been incorporating the Nz function into the
DateDiff/DateAdd functions, which to date has resulted in a “too many
arguments†error. But I’ll attempt to blend your suggestion with the Text3
expression.

Thanks again! Your help is greatly appreciated!
Robert5833

It would have helped had you posted the expression that is returning the
negative result, and stated what you would have it do if the result is
less
than 0. In general you would need to test for that. To substitute a 0
if
Text2 is null or less than 0 you could do:
IIf(Nz([Text2],0)<0,0,Nz([Text2],0))

In some cases you can assign a Default Value of 0 to a control, and avoid
the use of Nz.

Thank you BruceM;
With your help I’ve got that control working properly now with no
#Error.

The only lingering issue I have is that some of my calculated controls
return a negative value for those records that calculate on the “0â€
value
added by the Nz function. (I have a control that uses Text (3) to
evaluate
“months remaining†in numerical terms, which displays –(X) for those
“0â€
value calculations.)

The underlying problem seems to be with the DateAdd or DateDiff
functions?

Would a better approach for my situation be a macro or code string that
sets
the Control Property to Null if the returned value is equal to or less
than 0?

Thanks again!
Robert5833


:

If I understand correctly, you are trying to add to a date in Text1
the
number of months specified in Text2, and have the result appear in
Text3.
If so, try this as the Control Source of Text 3:
=DateAdd("m",[Text2],[Text1])
To allow for Text2 being null you could have:
=DateAdd("m",Nz([Text2],0),[Text1])
This will add zero to today's date. You could choose another number
than
0
if you prefer to default to adding one month or whatever in case of a
Null
in Text2.
With Nz you have to decide what value you want to use instead of null.
The
default, I think, is an empty string, which won't help since you need
a
number.

Good day all;
I’m trying to calculate and display dates as calculated controls in
a
series
of Text Boxes on a form, but having problems with zero length or
Null
values.
I am using a time stamp date from Text Box (1), using a numeric
value
from
Text Box (2) to calculate an “extended†date (as date) displayed in
Text
Box
(3).

This series works fine, except for the extended date in Text Box
(3),
which
displays #Error where there is no numeric value in Text Box (2)
(because
it
is not a criterion for every record).

I can resolve the displayed #Error by entering “0†as a value
(rather
than
Null or empty) in Text Box (2) for those records which do not have
criterion
of “equal to or greater than 1,†but that results in negative values
for
other calculated controls on the form.

Is there an expression/syntax to allow either a Null value, empty,
or
zero
length string in the underlying table, so that I don’t get Null
propagation
errors, and so those fields which do not have a multiplier greater
than
“0â€
do not display any result?

Calculated Control (Text Box) with #Error:
Control Source;
=DateAdd("m",(+[ComboBoxWithNumMultiplier]),[TextBoxWithDateStampRecord])

(Note: ComboBoxWithNumMultiplier above is Format “None†with Row
Source
on
a
table field; Data Type Number)

I can’t seem to find the right syntax to incorporate the Nz, or IIf
functions.
Any suggestions would be greatly appreciated.

Thank you,
Robert5833