IIf Expressions gives me #Error

[Guest]

Why does the expression =IIf(IsNull([SumOfHours]),"0", [SumOfHours]) giving
me the answer of #Error on my report? I have two databases linked, and the
values are be pulled from the second database. If the assignment hasn't been
worked on, no hours are entered in the second databases tables. On my report
in the first database, if there are no hours for that project, I want "0"
printed in the appropriate place on the report, otherwise print the sum of
the hours worked. Can I get some help. What am I missing?

 

[Duane Hookom]

First, you should not create an IIf() expression that might return either a
string ("0") or a numeric value ([SumOfHours]). I expect you would want to
use:
=IIf(IsNull([SumOfHours]),0, [SumOfHours])

I am not confident this will fix your issue. If your report doesn't return
any records, you will need to use something like:
=IIf(HasData,[SumOfHours],0)

It isn't clear if there are any subreports involved in this issue.

 

[Guest]

For the report, I'm selecting data from five tables, including two linked
tables. I'm then summing the hours from one of the linked tables, grouped by
an id#, in the query to get the sum of hours for each task. On the report,
the hours are listed by id# and 5 other fields. There can be multiple hours
for an id#, which is why I'm summing the hours.

First, you should not create an IIf() expression that might return either a
string ("0") or a numeric value ([SumOfHours]). I expect you would want to
use:
=IIf(IsNull([SumOfHours]),0, [SumOfHours])

I am not confident this will fix your issue. If your report doesn't return
any records, you will need to use something like:
=IIf(HasData,[SumOfHours],0)

It isn't clear if there are any subreports involved in this issue.

--
Duane Hookom
MS Access MVP
--

Why does the expression =IIf(IsNull([SumOfHours]),"0", [SumOfHours])
giving
me the answer of #Error on my report? I have two databases linked, and the
values are be pulled from the second database. If the assignment hasn't
been
worked on, no hours are entered in the second databases tables. On my
report
in the first database, if there are no hours for that project, I want "0"
printed in the appropriate place on the report, otherwise print the sum of
the hours worked. Can I get some help. What am I missing?

 

[Guest]

I tried Duane method and I still got an #Error. When I tried your method
Ofer, I got a 0 in every row. I only want a 0 in the row, if there are no
values. If there are values, I want that value displayed.

I would try Duane solution first with the HasData, but if you still get an
error, you can try this, to replace the error with 0

=IIf(IsError(SumOfHours),0,SumOfHours)


--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck

Why does the expression =IIf(IsNull([SumOfHours]),"0", [SumOfHours]) giving
me the answer of #Error on my report? I have two databases linked, and the
values are be pulled from the second database. If the assignment hasn't been
worked on, no hours are entered in the second databases tables. On my report
in the first database, if there are no hours for that project, I want "0"
printed in the appropriate place on the report, otherwise print the sum of
the hours worked. Can I get some help. What am I missing?

 

[Guest]

I would try Duane solution first with the HasData, but if you still get an
error, you can try this, to replace the error with 0

=IIf(IsError(SumOfHours),0,SumOfHours)

 

[Duane Hookom]

What is the field in your report's record source that you want on your
report and where on your report? Is the value to display a Sum() or straight
value?

--
Duane Hookom
MS Access MVP

For the report, I'm selecting data from five tables, including two linked
tables. I'm then summing the hours from one of the linked tables, grouped
by
an id#, in the query to get the sum of hours for each task. On the
report,
the hours are listed by id# and 5 other fields. There can be multiple
hours
for an id#, which is why I'm summing the hours.

First, you should not create an IIf() expression that might return either
a
string ("0") or a numeric value ([SumOfHours]). I expect you would want
to
use:
=IIf(IsNull([SumOfHours]),0, [SumOfHours])

I am not confident this will fix your issue. If your report doesn't
return
any records, you will need to use something like:
=IIf(HasData,[SumOfHours],0)

It isn't clear if there are any subreports involved in this issue.

--
Duane Hookom
MS Access MVP
--

Why does the expression =IIf(IsNull([SumOfHours]),"0", [SumOfHours])
giving
me the answer of #Error on my report? I have two databases linked, and
the
values are be pulled from the second database. If the assignment hasn't
been
worked on, no hours are entered in the second databases tables. On my
report
in the first database, if there are no hours for that project, I want
"0"
printed in the appropriate place on the report, otherwise print the sum
of
the hours worked. Can I get some help. What am I missing?

 

[Guest]

The record source for the report is SELECT [tblIssueData].[RegNumber],
[tblIssueData].[ModCenterRef], [tblIssueData].[Status],
[tblIssueData].[CauseType], [tblIssueData].[ID], [tblIssueData].[Originator],
[tblIssueData].[DesignChange], Sum([tblTimeBilled].[Hours]) AS SumOfHours,
[tblIssueData].[Cause of Issue] AS CauseDesc, [tblIssueData].[ResPrimaryDoc]
FROM tblCauseData INNER JOIN ((tblIssueData INNER JOIN tblTasks ON
[tblIssueData].[TaskID]=[tblTasks].[TaskID]) INNER JOIN tblTimeBilled ON
[tblTasks].[TaskID]=[tblTimeBilled].[TaskID]) ON
[tblCauseData].[CauseID]=[tblIssueData].[CauseType] GROUP BY
[tblIssueData].[RegNumber], [tblIssueData].[ModCenterRef],
[tblIssueData].[Status], [tblIssueData].[CauseType], [tblIssueData].[ID],
[tblIssueData].[Originator], [tblIssueData].[DesignChange],
[tblIssueData].[Cause of Issue], [tblIssueData].[ResPrimaryDoc]; I want to
get a value in the report, not a sum.

What is the field in your report's record source that you want on your
report and where on your report? Is the value to display a Sum() or straight
value?

--
Duane Hookom
MS Access MVP

For the report, I'm selecting data from five tables, including two linked
tables. I'm then summing the hours from one of the linked tables, grouped
by
an id#, in the query to get the sum of hours for each task. On the
report,
the hours are listed by id# and 5 other fields. There can be multiple
hours
for an id#, which is why I'm summing the hours.

First, you should not create an IIf() expression that might return either
a
string ("0") or a numeric value ([SumOfHours]). I expect you would want
to
use:
=IIf(IsNull([SumOfHours]),0, [SumOfHours])

I am not confident this will fix your issue. If your report doesn't
return
any records, you will need to use something like:
=IIf(HasData,[SumOfHours],0)

It isn't clear if there are any subreports involved in this issue.

--
Duane Hookom
MS Access MVP
--

Why does the expression =IIf(IsNull([SumOfHours]),"0", [SumOfHours])
giving
me the answer of #Error on my report? I have two databases linked, and
the
values are be pulled from the second database. If the assignment hasn't
been
worked on, no hours are entered in the second databases tables. On my
report
in the first database, if there are no hours for that project, I want
"0"
printed in the appropriate place on the report, otherwise print the sum
of
the hours worked. Can I get some help. What am I missing?

 

[Duane Hookom]

I would first look to see if the name of the control is also the name of
your field. If so, change the control name.

I assume your objective is to display 0 if the SumOfHours column/field is
Null. If so, use the Format property to display a 0 and don't mess with the
Control Source property. If you need help on this press F1 and search for
"Format Numeric".

--
Duane Hookom
MS Access MVP
--

The record source for the report is SELECT [tblIssueData].[RegNumber],
[tblIssueData].[ModCenterRef], [tblIssueData].[Status],
[tblIssueData].[CauseType], [tblIssueData].[ID],
[tblIssueData].[Originator],
[tblIssueData].[DesignChange], Sum([tblTimeBilled].[Hours]) AS SumOfHours,
[tblIssueData].[Cause of Issue] AS CauseDesc,
[tblIssueData].[ResPrimaryDoc]
FROM tblCauseData INNER JOIN ((tblIssueData INNER JOIN tblTasks ON
[tblIssueData].[TaskID]=[tblTasks].[TaskID]) INNER JOIN tblTimeBilled ON
[tblTasks].[TaskID]=[tblTimeBilled].[TaskID]) ON
[tblCauseData].[CauseID]=[tblIssueData].[CauseType] GROUP BY
[tblIssueData].[RegNumber], [tblIssueData].[ModCenterRef],
[tblIssueData].[Status], [tblIssueData].[CauseType], [tblIssueData].[ID],
[tblIssueData].[Originator], [tblIssueData].[DesignChange],
[tblIssueData].[Cause of Issue], [tblIssueData].[ResPrimaryDoc]; I want to
get a value in the report, not a sum.

What is the field in your report's record source that you want on your
report and where on your report? Is the value to display a Sum() or
straight
value?

--
Duane Hookom
MS Access MVP

For the report, I'm selecting data from five tables, including two
linked
tables. I'm then summing the hours from one of the linked tables,
grouped
by
an id#, in the query to get the sum of hours for each task. On the
report,
the hours are listed by id# and 5 other fields. There can be multiple
hours
for an id#, which is why I'm summing the hours.

:

First, you should not create an IIf() expression that might return
either
a
string ("0") or a numeric value ([SumOfHours]). I expect you would
want
to
use:
=IIf(IsNull([SumOfHours]),0, [SumOfHours])

I am not confident this will fix your issue. If your report doesn't
return
any records, you will need to use something like:
=IIf(HasData,[SumOfHours],0)

It isn't clear if there are any subreports involved in this issue.

--
Duane Hookom
MS Access MVP
--

Why does the expression =IIf(IsNull([SumOfHours]),"0", [SumOfHours])
giving
me the answer of #Error on my report? I have two databases linked,
and
the
values are be pulled from the second database. If the assignment
hasn't
been
worked on, no hours are entered in the second databases tables. On
my
report
in the first database, if there are no hours for that project, I
want
"0"
printed in the appropriate place on the report, otherwise print the
sum
of
the hours worked. Can I get some help. What am I missing?

 

[Guest]

I want to display 0 if the field is Null, otherwise, I want to display
whatever value is in the field at that time. What am I doing wrong?

I would first look to see if the name of the control is also the name of
your field. If so, change the control name.

I assume your objective is to display 0 if the SumOfHours column/field is
Null. If so, use the Format property to display a 0 and don't mess with the
Control Source property. If you need help on this press F1 and search for
"Format Numeric".

--
Duane Hookom
MS Access MVP
--

The record source for the report is SELECT [tblIssueData].[RegNumber],
[tblIssueData].[ModCenterRef], [tblIssueData].[Status],
[tblIssueData].[CauseType], [tblIssueData].[ID],
[tblIssueData].[Originator],
[tblIssueData].[DesignChange], Sum([tblTimeBilled].[Hours]) AS SumOfHours,
[tblIssueData].[Cause of Issue] AS CauseDesc,
[tblIssueData].[ResPrimaryDoc]
FROM tblCauseData INNER JOIN ((tblIssueData INNER JOIN tblTasks ON
[tblIssueData].[TaskID]=[tblTasks].[TaskID]) INNER JOIN tblTimeBilled ON
[tblTasks].[TaskID]=[tblTimeBilled].[TaskID]) ON
[tblCauseData].[CauseID]=[tblIssueData].[CauseType] GROUP BY
[tblIssueData].[RegNumber], [tblIssueData].[ModCenterRef],
[tblIssueData].[Status], [tblIssueData].[CauseType], [tblIssueData].[ID],
[tblIssueData].[Originator], [tblIssueData].[DesignChange],
[tblIssueData].[Cause of Issue], [tblIssueData].[ResPrimaryDoc]; I want to
get a value in the report, not a sum.

What is the field in your report's record source that you want on your
report and where on your report? Is the value to display a Sum() or
straight
value?

--
Duane Hookom
MS Access MVP


For the report, I'm selecting data from five tables, including two
linked
tables. I'm then summing the hours from one of the linked tables,
grouped
by
an id#, in the query to get the sum of hours for each task. On the
report,
the hours are listed by id# and 5 other fields. There can be multiple
hours
for an id#, which is why I'm summing the hours.

:

First, you should not create an IIf() expression that might return
either
a
string ("0") or a numeric value ([SumOfHours]). I expect you would
want
to
use:
=IIf(IsNull([SumOfHours]),0, [SumOfHours])

I am not confident this will fix your issue. If your report doesn't
return
any records, you will need to use something like:
=IIf(HasData,[SumOfHours],0)

It isn't clear if there are any subreports involved in this issue.

--
Duane Hookom
MS Access MVP
--

Why does the expression =IIf(IsNull([SumOfHours]),"0", [SumOfHours])
giving
me the answer of #Error on my report? I have two databases linked,
and
the
values are be pulled from the second database. If the assignment
hasn't
been
worked on, no hours are entered in the second databases tables. On
my
report
in the first database, if there are no hours for that project, I
want
"0"
printed in the appropriate place on the report, otherwise print the
sum
of
the hours worked. Can I get some help. What am I missing?

 

[Duane Hookom]

You didn't tell us if your control name is the name of a field.
What are these properties for your text box:

Name:
Control Source:
Format:

And the resulting display:

--
Duane Hookom
MS Access MVP
--

I want to display 0 if the field is Null, otherwise, I want to display
whatever value is in the field at that time. What am I doing wrong?

I would first look to see if the name of the control is also the name of
your field. If so, change the control name.

I assume your objective is to display 0 if the SumOfHours column/field is
Null. If so, use the Format property to display a 0 and don't mess with
the
Control Source property. If you need help on this press F1 and search for
"Format Numeric".

--
Duane Hookom
MS Access MVP
--

The record source for the report is SELECT [tblIssueData].[RegNumber],
[tblIssueData].[ModCenterRef], [tblIssueData].[Status],
[tblIssueData].[CauseType], [tblIssueData].[ID],
[tblIssueData].[Originator],
[tblIssueData].[DesignChange], Sum([tblTimeBilled].[Hours]) AS
SumOfHours,
[tblIssueData].[Cause of Issue] AS CauseDesc,
[tblIssueData].[ResPrimaryDoc]
FROM tblCauseData INNER JOIN ((tblIssueData INNER JOIN tblTasks ON
[tblIssueData].[TaskID]=[tblTasks].[TaskID]) INNER JOIN tblTimeBilled
ON
[tblTasks].[TaskID]=[tblTimeBilled].[TaskID]) ON
[tblCauseData].[CauseID]=[tblIssueData].[CauseType] GROUP BY
[tblIssueData].[RegNumber], [tblIssueData].[ModCenterRef],
[tblIssueData].[Status], [tblIssueData].[CauseType],
[tblIssueData].[ID],
[tblIssueData].[Originator], [tblIssueData].[DesignChange],
[tblIssueData].[Cause of Issue], [tblIssueData].[ResPrimaryDoc]; I want
to
get a value in the report, not a sum.

:

What is the field in your report's record source that you want on your
report and where on your report? Is the value to display a Sum() or
straight
value?

--
Duane Hookom
MS Access MVP


For the report, I'm selecting data from five tables, including two
linked
tables. I'm then summing the hours from one of the linked tables,
grouped
by
an id#, in the query to get the sum of hours for each task. On the
report,
the hours are listed by id# and 5 other fields. There can be
multiple
hours
for an id#, which is why I'm summing the hours.

:

First, you should not create an IIf() expression that might return
either
a
string ("0") or a numeric value ([SumOfHours]). I expect you would
want
to
use:
=IIf(IsNull([SumOfHours]),0, [SumOfHours])

I am not confident this will fix your issue. If your report doesn't
return
any records, you will need to use something like:
=IIf(HasData,[SumOfHours],0)

It isn't clear if there are any subreports involved in this issue.

--
Duane Hookom
MS Access MVP
--

Why does the expression =IIf(IsNull([SumOfHours]),"0",
[SumOfHours])
giving
me the answer of #Error on my report? I have two databases
linked,
and
the
values are be pulled from the second database. If the assignment
hasn't
been
worked on, no hours are entered in the second databases tables.
On
my
report
in the first database, if there are no hours for that project, I
want
"0"
printed in the appropriate place on the report, otherwise print
the
sum
of
the hours worked. Can I get some help. What am I missing?

 

[Guest]

I've tried it 2 different ways. I used the Name:SumOfHours, Control
Source:Hours No Format. Display is the Hours, whatever the are. no zeros.
I've tried Name:SumOfHours, Control
Source:=IIf(IsNull(SumOfBillableHours]),0,[SumOfBillableHours]), No Format.
Display is #Error

You didn't tell us if your control name is the name of a field.
What are these properties for your text box:

Name:
Control Source:
Format:

And the resulting display:

--
Duane Hookom
MS Access MVP
--

I want to display 0 if the field is Null, otherwise, I want to display
whatever value is in the field at that time. What am I doing wrong?

I would first look to see if the name of the control is also the name of
your field. If so, change the control name.

I assume your objective is to display 0 if the SumOfHours column/field is
Null. If so, use the Format property to display a 0 and don't mess with
the
Control Source property. If you need help on this press F1 and search for
"Format Numeric".

--
Duane Hookom
MS Access MVP
--

The record source for the report is SELECT [tblIssueData].[RegNumber],
[tblIssueData].[ModCenterRef], [tblIssueData].[Status],
[tblIssueData].[CauseType], [tblIssueData].[ID],
[tblIssueData].[Originator],
[tblIssueData].[DesignChange], Sum([tblTimeBilled].[Hours]) AS
SumOfHours,
[tblIssueData].[Cause of Issue] AS CauseDesc,
[tblIssueData].[ResPrimaryDoc]
FROM tblCauseData INNER JOIN ((tblIssueData INNER JOIN tblTasks ON
[tblIssueData].[TaskID]=[tblTasks].[TaskID]) INNER JOIN tblTimeBilled
ON
[tblTasks].[TaskID]=[tblTimeBilled].[TaskID]) ON
[tblCauseData].[CauseID]=[tblIssueData].[CauseType] GROUP BY
[tblIssueData].[RegNumber], [tblIssueData].[ModCenterRef],
[tblIssueData].[Status], [tblIssueData].[CauseType],
[tblIssueData].[ID],
[tblIssueData].[Originator], [tblIssueData].[DesignChange],
[tblIssueData].[Cause of Issue], [tblIssueData].[ResPrimaryDoc]; I want
to
get a value in the report, not a sum.

:

What is the field in your report's record source that you want on your
report and where on your report? Is the value to display a Sum() or
straight
value?

--
Duane Hookom
MS Access MVP


For the report, I'm selecting data from five tables, including two
linked
tables. I'm then summing the hours from one of the linked tables,
grouped
by
an id#, in the query to get the sum of hours for each task. On the
report,
the hours are listed by id# and 5 other fields. There can be
multiple
hours
for an id#, which is why I'm summing the hours.

:

First, you should not create an IIf() expression that might return
either
a
string ("0") or a numeric value ([SumOfHours]). I expect you would
want
to
use:
=IIf(IsNull([SumOfHours]),0, [SumOfHours])

I am not confident this will fix your issue. If your report doesn't
return
any records, you will need to use something like:
=IIf(HasData,[SumOfHours],0)

It isn't clear if there are any subreports involved in this issue.

--
Duane Hookom
MS Access MVP
--

Why does the expression =IIf(IsNull([SumOfHours]),"0",
[SumOfHours])
giving
me the answer of #Error on my report? I have two databases
linked,
and
the
values are be pulled from the second database. If the assignment
hasn't
been
worked on, no hours are entered in the second databases tables.
On
my
report
in the first database, if there are no hours for that project, I
want
"0"
printed in the appropriate place on the report, otherwise print
the
sum
of
the hours worked. Can I get some help. What am I missing?

 

[Duane Hookom]

Looking at your record source SQL view, I can't find: Hours or
SumOfBillableHours.

--
Duane Hookom
MS Access MVP
--

I've tried it 2 different ways. I used the Name:SumOfHours, Control
Source:Hours No Format. Display is the Hours, whatever the are. no zeros.
I've tried Name:SumOfHours, Control
Source:=IIf(IsNull(SumOfBillableHours]),0,[SumOfBillableHours]), No
Format.
Display is #Error

You didn't tell us if your control name is the name of a field.
What are these properties for your text box:

Name:
Control Source:
Format:

And the resulting display:

--
Duane Hookom
MS Access MVP
--

I want to display 0 if the field is Null, otherwise, I want to display
whatever value is in the field at that time. What am I doing wrong?

:

I would first look to see if the name of the control is also the name
of
your field. If so, change the control name.

I assume your objective is to display 0 if the SumOfHours column/field
is
Null. If so, use the Format property to display a 0 and don't mess
with
the
Control Source property. If you need help on this press F1 and search
for
"Format Numeric".

--
Duane Hookom
MS Access MVP
--

The record source for the report is SELECT
[tblIssueData].[RegNumber],
[tblIssueData].[ModCenterRef], [tblIssueData].[Status],
[tblIssueData].[CauseType], [tblIssueData].[ID],
[tblIssueData].[Originator],
[tblIssueData].[DesignChange], Sum([tblTimeBilled].[Hours]) AS
SumOfHours,
[tblIssueData].[Cause of Issue] AS CauseDesc,
[tblIssueData].[ResPrimaryDoc]
FROM tblCauseData INNER JOIN ((tblIssueData INNER JOIN tblTasks ON
[tblIssueData].[TaskID]=[tblTasks].[TaskID]) INNER JOIN
tblTimeBilled
ON
[tblTasks].[TaskID]=[tblTimeBilled].[TaskID]) ON
[tblCauseData].[CauseID]=[tblIssueData].[CauseType] GROUP BY
[tblIssueData].[RegNumber], [tblIssueData].[ModCenterRef],
[tblIssueData].[Status], [tblIssueData].[CauseType],
[tblIssueData].[ID],
[tblIssueData].[Originator], [tblIssueData].[DesignChange],
[tblIssueData].[Cause of Issue], [tblIssueData].[ResPrimaryDoc]; I
want
to
get a value in the report, not a sum.

:

What is the field in your report's record source that you want on
your
report and where on your report? Is the value to display a Sum() or
straight
value?

--
Duane Hookom
MS Access MVP


For the report, I'm selecting data from five tables, including
two
linked
tables. I'm then summing the hours from one of the linked tables,
grouped
by
an id#, in the query to get the sum of hours for each task. On
the
report,
the hours are listed by id# and 5 other fields. There can be
multiple
hours
for an id#, which is why I'm summing the hours.

:

First, you should not create an IIf() expression that might
return
either
a
string ("0") or a numeric value ([SumOfHours]). I expect you
would
want
to
use:
=IIf(IsNull([SumOfHours]),0, [SumOfHours])

I am not confident this will fix your issue. If your report
doesn't
return
any records, you will need to use something like:
=IIf(HasData,[SumOfHours],0)

It isn't clear if there are any subreports involved in this
issue.

--
Duane Hookom
MS Access MVP
--

Why does the expression =IIf(IsNull([SumOfHours]),"0",
[SumOfHours])
giving
me the answer of #Error on my report? I have two databases
linked,
and
the
values are be pulled from the second database. If the
assignment
hasn't
been
worked on, no hours are entered in the second databases
tables.
On
my
report
in the first database, if there are no hours for that project,
I
want
"0"
printed in the appropriate place on the report, otherwise
print
the
sum
of
the hours worked. Can I get some help. What am I missing?