Same expression, different fields, doesn't work

[Paul]

I realise without the entire database there will be some guesswork
involved but I'm hoping to get a few things to check that could be the
cause of this phenomenon. I have a report displaying data from two
fields: [Hours] and [Fixed price]. Both fields are 'number' fields
using the 'single' field size. The data for both fields originate from
the same table and are 'quoted' in the same query. The report's record
source is this query and this query only. I only want the fields to
display if there is data so my text box controls have the following
control sources:

=IIf([FixedPrice]>0,[FixedPrice])
=IIf([Hours]>0,[Hours])

With the 'Fixed price' field this works fine but with the 'Hours' field
I get #Error. I assume there must be something different about the two
but can't find anything. Can anyone spot anything obvious that could be
the cause? (I have also tried the expressions

=IIf([FixedPrice]>0,[FixedPrice],)
=IIf([Hours]>0,[Hours],)

and

=IIf([FixedPrice]>0,[FixedPrice],"")
=IIf([Hours]>0,[Hours],"")

 

[Paul]

I've just noticed that because I'm doing calculations with the data in
these fields that my later results are also going 'wierd'. I think my
best bet would be to somehow format the text as white when the data = 0
but conditional formatting is greyed out for these fields!

 

[Brendan Reynolds]

Make sure that the text box is not also named 'Hours'. If it is, try
changing the name of the text box.

 

[Paul]

Maybe there is a way of controlling this at the level of the original
field properties? Something like if no value is entered, the field is
empty rather than having 0.00?

I've just noticed that because I'm doing calculations with the data in
these fields that my later results are also going 'wierd'. I think my
best bet would be to somehow format the text as white when the data = 0
but conditional formatting is greyed out for these fields!

I realise without the entire database there will be some guesswork
involved but I'm hoping to get a few things to check that could be the
cause of this phenomenon. I have a report displaying data from two
fields: [Hours] and [Fixed price]. Both fields are 'number' fields
using the 'single' field size. The data for both fields originate from
the same table and are 'quoted' in the same query. The report's record
source is this query and this query only. I only want the fields to
display if there is data so my text box controls have the following
control sources:

=IIf([FixedPrice]>0,[FixedPrice])
=IIf([Hours]>0,[Hours])

With the 'Fixed price' field this works fine but with the 'Hours' field
I get #Error. I assume there must be something different about the two
but can't find anything. Can anyone spot anything obvious that could be
the cause? (I have also tried the expressions

=IIf([FixedPrice]>0,[FixedPrice],)
=IIf([Hours]>0,[Hours],)

and

=IIf([FixedPrice]>0,[FixedPrice],"")
=IIf([Hours]>0,[Hours],"")

 

[Paul]

It is the same name but I don't think that's the problem. I have
managed to get around the #Error problem by scrapping the in-report iif
expression and removing a default=0 property from the field in the
original table. However this has caused/hasn't solved the problem with
later equations. I've got (amongst others) a field with the expression

=([Hours]*[HourlyRateGBP])+[FixedPrice]

This and all other equations in the report that use [Hours] or
[FixedPrice] simply display a blank if one of these is empty. Is there
a way to make it ignore the blank? I'm guessing it's something to do
with another iif and the NULL thingy but I've never used NULL and
haven't really read much about it either!

Make sure that the text box is not also named 'Hours'. If it is, try
changing the name of the text box.

--
Brendan Reynolds
Access MVP

I realise without the entire database there will be some guesswork
involved but I'm hoping to get a few things to check that could be the
cause of this phenomenon. I have a report displaying data from two
fields: [Hours] and [Fixed price]. Both fields are 'number' fields
using the 'single' field size. The data for both fields originate from
the same table and are 'quoted' in the same query. The report's record
source is this query and this query only. I only want the fields to
display if there is data so my text box controls have the following
control sources:

=IIf([FixedPrice]>0,[FixedPrice])
=IIf([Hours]>0,[Hours])

With the 'Fixed price' field this works fine but with the 'Hours' field
I get #Error. I assume there must be something different about the two
but can't find anything. Can anyone spot anything obvious that could be
the cause? (I have also tried the expressions

=IIf([FixedPrice]>0,[FixedPrice],)
=IIf([Hours]>0,[Hours],)

and

=IIf([FixedPrice]>0,[FixedPrice],"")
=IIf([Hours]>0,[Hours],"")

 

[Paul]

GOT IT!

I've discovered the Nz() function. You assign it to the fields like
this:

=(Nz([Hours])*[HourlyRateGBP])+Nz([FixedPriceGBP])

and it treats a null value for that field as a zero.

It is the same name but I don't think that's the problem. I have
managed to get around the #Error problem by scrapping the in-report iif
expression and removing a default=0 property from the field in the
original table. However this has caused/hasn't solved the problem with
later equations. I've got (amongst others) a field with the expression

=([Hours]*[HourlyRateGBP])+[FixedPrice]

This and all other equations in the report that use [Hours] or
[FixedPrice] simply display a blank if one of these is empty. Is there
a way to make it ignore the blank? I'm guessing it's something to do
with another iif and the NULL thingy but I've never used NULL and
haven't really read much about it either!

Make sure that the text box is not also named 'Hours'. If it is, try
changing the name of the text box.

--
Brendan Reynolds
Access MVP

I realise without the entire database there will be some guesswork
involved but I'm hoping to get a few things to check that could be the
cause of this phenomenon. I have a report displaying data from two
fields: [Hours] and [Fixed price]. Both fields are 'number' fields
using the 'single' field size. The data for both fields originate from
the same table and are 'quoted' in the same query. The report's record
source is this query and this query only. I only want the fields to
display if there is data so my text box controls have the following
control sources:

=IIf([FixedPrice]>0,[FixedPrice])
=IIf([Hours]>0,[Hours])

With the 'Fixed price' field this works fine but with the 'Hours' field
I get #Error. I assume there must be something different about the two
but can't find anything. Can anyone spot anything obvious that could be
the cause? (I have also tried the expressions

=IIf([FixedPrice]>0,[FixedPrice],)
=IIf([Hours]>0,[Hours],)

and

=IIf([FixedPrice]>0,[FixedPrice],"")
=IIf([Hours]>0,[Hours],"")

 

[BruceM]

Good job on the research. You can assign any value in place of Null with
the Nz function. For instance, sometimes in multiplication operations you
don't want to multiply by 0, so you may use:
Nz([Hours],1)
You can also use text:
Nz([Hours],"No hours")
This wouldn't be helpful in the expression, but could be in the Hours text
box.
By the way, you don't need the parentheses after the = sign or after
[Hours]:
=Nz([Hours]*[HourlyRateGBP])+Nz([FixedPriceGBP])
This expression says that if the result of Hours * Rate is Null (unknown),
which would be the case if either value is 0 since you can't multiply by 0,
assign the default value of 0, then add FixedPrice to that.

GOT IT!

I've discovered the Nz() function. You assign it to the fields like
this:

=(Nz([Hours])*[HourlyRateGBP])+Nz([FixedPriceGBP])

and it treats a null value for that field as a zero.

It is the same name but I don't think that's the problem. I have
managed to get around the #Error problem by scrapping the in-report iif
expression and removing a default=0 property from the field in the
original table. However this has caused/hasn't solved the problem with
later equations. I've got (amongst others) a field with the expression

=([Hours]*[HourlyRateGBP])+[FixedPrice]

This and all other equations in the report that use [Hours] or
[FixedPrice] simply display a blank if one of these is empty. Is there
a way to make it ignore the blank? I'm guessing it's something to do
with another iif and the NULL thingy but I've never used NULL and
haven't really read much about it either!

Make sure that the text box is not also named 'Hours'. If it is, try
changing the name of the text box.

--
Brendan Reynolds
Access MVP


I realise without the entire database there will be some guesswork
involved but I'm hoping to get a few things to check that could be
the
cause of this phenomenon. I have a report displaying data from two
fields: [Hours] and [Fixed price]. Both fields are 'number' fields
using the 'single' field size. The data for both fields originate
from
the same table and are 'quoted' in the same query. The report's
record
source is this query and this query only. I only want the fields to
display if there is data so my text box controls have the following
control sources:

=IIf([FixedPrice]>0,[FixedPrice])
=IIf([Hours]>0,[Hours])

With the 'Fixed price' field this works fine but with the 'Hours'
field
I get #Error. I assume there must be something different about the
two
but can't find anything. Can anyone spot anything obvious that could
be
the cause? (I have also tried the expressions

=IIf([FixedPrice]>0,[FixedPrice],)
=IIf([Hours]>0,[Hours],)

and

=IIf([FixedPrice]>0,[FixedPrice],"")
=IIf([Hours]>0,[Hours],"")

 

[Paul]

Thanks for the additional info - it will probably come in handy later
on.

Good job on the research. You can assign any value in place of Null with
the Nz function. For instance, sometimes in multiplication operations you
don't want to multiply by 0, so you may use:
Nz([Hours],1)
You can also use text:
Nz([Hours],"No hours")
This wouldn't be helpful in the expression, but could be in the Hours text
box.
By the way, you don't need the parentheses after the = sign or after
[Hours]:
=Nz([Hours]*[HourlyRateGBP])+Nz([FixedPriceGBP])
This expression says that if the result of Hours * Rate is Null (unknown),
which would be the case if either value is 0 since you can't multiply by 0,
assign the default value of 0, then add FixedPrice to that.

GOT IT!

I've discovered the Nz() function. You assign it to the fields like
this:

=(Nz([Hours])*[HourlyRateGBP])+Nz([FixedPriceGBP])

and it treats a null value for that field as a zero.

It is the same name but I don't think that's the problem. I have
managed to get around the #Error problem by scrapping the in-report iif
expression and removing a default=0 property from the field in the
original table. However this has caused/hasn't solved the problem with
later equations. I've got (amongst others) a field with the expression

=([Hours]*[HourlyRateGBP])+[FixedPrice]

This and all other equations in the report that use [Hours] or
[FixedPrice] simply display a blank if one of these is empty. Is there
a way to make it ignore the blank? I'm guessing it's something to do
with another iif and the NULL thingy but I've never used NULL and
haven't really read much about it either!


Brendan Reynolds wrote:

Make sure that the text box is not also named 'Hours'. If it is, try
changing the name of the text box.

--
Brendan Reynolds
Access MVP


I realise without the entire database there will be some guesswork
involved but I'm hoping to get a few things to check that could be
the
cause of this phenomenon. I have a report displaying data from two
fields: [Hours] and [Fixed price]. Both fields are 'number' fields
using the 'single' field size. The data for both fields originate
from
the same table and are 'quoted' in the same query. The report's
record
source is this query and this query only. I only want the fields to
display if there is data so my text box controls have the following
control sources:

=IIf([FixedPrice]>0,[FixedPrice])
=IIf([Hours]>0,[Hours])

With the 'Fixed price' field this works fine but with the 'Hours'
field
I get #Error. I assume there must be something different about the
two
but can't find anything. Can anyone spot anything obvious that could
be
the cause? (I have also tried the expressions

=IIf([FixedPrice]>0,[FixedPrice],)
=IIf([Hours]>0,[Hours],)

and

=IIf([FixedPrice]>0,[FixedPrice],"")
=IIf([Hours]>0,[Hours],"")