report/subreport

[Guest]

TIA:

I have a report with subreport (they are not linked). The subreport is
based on a query that queries a text field. The subreport has a control
with source=count([textfield]). When there is 1 or more records, I get a
count in the subform and see it on the main form. when there are no records
i get an #error on the subform and no data on the main form. An IIF clause in
the subform control to test for error and then return 0 if true, works on the
subform but I still see nothing on the main form.

So...how can i get a 0 value on the main form when a subforms' query count
has no records??

Thanks,

Joel

 

[Guest]

You can use an expression like:
=IIf(subrptcntrl.report.HasData, subrptcntrl.report.txtOnSub, 0)

 

[Guest]

Thanks Duane:

I have tried several options but get an "naming error".

My main form is "f&b"

I have another form named "f&b injuries" with a field "primary_cause"

On the main form I added a subform control and have the source as Report.f&b
injuries.

I assume the iif statement is for the control source for the subform.

I have =iif(report.f&b injuries.hasdata, report.f&b injuries, report.f&b
injuries.primary_cause=0) I get error, "doen't follow naming rules...'

Any additional help on syntax??

Thanks,

Joel

You can use an expression like:
=IIf(subrptcntrl.report.HasData, subrptcntrl.report.txtOnSub, 0)
--
Duane Hookom
Microsoft Access MVP

TIA:

I have a report with subreport (they are not linked). The subreport is
based on a query that queries a text field. The subreport has a control
with source=count([textfield]). When there is 1 or more records, I get a
count in the subform and see it on the main form. when there are no records
i get an #error on the subform and no data on the main form. An IIF clause in
the subform control to test for error and then return 0 if true, works on the
subform but I still see nothing on the main form.

So...how can i get a 0 value on the main form when a subforms' query count
has no records??

Thanks,

Joel

 

[Guest]

The "&" symbol is used to concatenate strings together like:
FirstName & " " & LastName
You should find and use a naming convention that doesn't allow spaces and
symbols. The only non alphanumeric that I find acceptable is the underscore.
I also recommend naming forms with something that identifies it as a form.
IMHO looking at the name of an object or variable should provide a clue as to
what type of object or variable is.

If you can't change your names, then you will have to place []s around them.
[f&b injuries].

One other point, I use forms as forms and reports as reports. I don't know
if you misspoke regarding "main form" and "subform".
--
Duane Hookom
Microsoft Access MVP

Thanks Duane:

I have tried several options but get an "naming error".

My main form is "f&b"

I have another form named "f&b injuries" with a field "primary_cause"

On the main form I added a subform control and have the source as Report.f&b
injuries.

I assume the iif statement is for the control source for the subform.

I have =iif(report.f&b injuries.hasdata, report.f&b injuries, report.f&b
injuries.primary_cause=0) I get error, "doen't follow naming rules...'

Any additional help on syntax??

Thanks,

Joel

You can use an expression like:
=IIf(subrptcntrl.report.HasData, subrptcntrl.report.txtOnSub, 0)
--
Duane Hookom
Microsoft Access MVP

TIA:

I have a report with subreport (they are not linked). The subreport is
based on a query that queries a text field. The subreport has a control
with source=count([textfield]). When there is 1 or more records, I get a
count in the subform and see it on the main form. when there are no records
i get an #error on the subform and no data on the main form. An IIF clause in
the subform control to test for error and then return 0 if true, works on the
subform but I still see nothing on the main form.

So...how can i get a 0 value on the main form when a subforms' query count
has no records??

Thanks,

Joel

 

[Guest]

Thanks Duane:

I understand about & but someone else used in in the report names so I
understand using []'s.

but I'm still having trouble.

I have:

=iif([primary_cause].[F&B Kitchen Cuts Injury].hasdata,[primary_cause].[F&B
Kitchen Cuts Injury].txtonsub,0)

What's my problem?
([primary_cause] is the text box on the subreport named [F&B Kitchen Cuts
Injury].

The main report is named f&b

Thanks again,

Joel

The "&" symbol is used to concatenate strings together like:
FirstName & " " & LastName
You should find and use a naming convention that doesn't allow spaces and
symbols. The only non alphanumeric that I find acceptable is the underscore.
I also recommend naming forms with something that identifies it as a form.
IMHO looking at the name of an object or variable should provide a clue as to
what type of object or variable is.

If you can't change your names, then you will have to place []s around them.
[f&b injuries].

One other point, I use forms as forms and reports as reports. I don't know
if you misspoke regarding "main form" and "subform".
--
Duane Hookom
Microsoft Access MVP

Thanks Duane:

I have tried several options but get an "naming error".

My main form is "f&b"

I have another form named "f&b injuries" with a field "primary_cause"

On the main form I added a subform control and have the source as Report.f&b
injuries.

I assume the iif statement is for the control source for the subform.

I have =iif(report.f&b injuries.hasdata, report.f&b injuries, report.f&b
injuries.primary_cause=0) I get error, "doen't follow naming rules...'

Any additional help on syntax??

Thanks,

Joel

You can use an expression like:
=IIf(subrptcntrl.report.HasData, subrptcntrl.report.txtOnSub, 0)
--
Duane Hookom
Microsoft Access MVP


:

TIA:

I have a report with subreport (they are not linked). The subreport is
based on a query that queries a text field. The subreport has a control
with source=count([textfield]). When there is 1 or more records, I get a
count in the subform and see it on the main form. when there are no records
i get an #error on the subform and no data on the main form. An IIF clause in
the subform control to test for error and then return 0 if true, works on the
subform but I still see nothing on the main form.

So...how can i get a 0 value on the main form when a subforms' query count
has no records??

Thanks,

Joel

 

[Guest]

Try:
=iif([F&B Kitchen Cuts Injury].Report.hasdata, [F&B Kitchen Cuts
Injury].Report.[primary_cause],0)

--
Duane Hookom
Microsoft Access MVP

Thanks Duane:

I understand about & but someone else used in in the report names so I
understand using []'s.

but I'm still having trouble.

I have:

=iif([primary_cause].[F&B Kitchen Cuts Injury].hasdata,[primary_cause].[F&B
Kitchen Cuts Injury].txtonsub,0)

What's my problem?
([primary_cause] is the text box on the subreport named [F&B Kitchen Cuts
Injury].

The main report is named f&b

Thanks again,

Joel

The "&" symbol is used to concatenate strings together like:
FirstName & " " & LastName
You should find and use a naming convention that doesn't allow spaces and
symbols. The only non alphanumeric that I find acceptable is the underscore.
I also recommend naming forms with something that identifies it as a form.
IMHO looking at the name of an object or variable should provide a clue as to
what type of object or variable is.

If you can't change your names, then you will have to place []s around them.
[f&b injuries].

One other point, I use forms as forms and reports as reports. I don't know
if you misspoke regarding "main form" and "subform".
--
Duane Hookom
Microsoft Access MVP

Thanks Duane:

I have tried several options but get an "naming error".

My main form is "f&b"

I have another form named "f&b injuries" with a field "primary_cause"

On the main form I added a subform control and have the source as Report.f&b
injuries.

I assume the iif statement is for the control source for the subform.

I have =iif(report.f&b injuries.hasdata, report.f&b injuries, report.f&b
injuries.primary_cause=0) I get error, "doen't follow naming rules...'

Any additional help on syntax??

Thanks,

Joel



:

You can use an expression like:
=IIf(subrptcntrl.report.HasData, subrptcntrl.report.txtOnSub, 0)
--
Duane Hookom
Microsoft Access MVP


:

TIA:

I have a report with subreport (they are not linked). The subreport is
based on a query that queries a text field. The subreport has a control
with source=count([textfield]). When there is 1 or more records, I get a
count in the subform and see it on the main form. when there are no records
i get an #error on the subform and no data on the main form. An IIF clause in
the subform control to test for error and then return 0 if true, works on the
subform but I still see nothing on the main form.

So...how can i get a 0 value on the main form when a subforms' query count
has no records??

Thanks,

Joel

 

[Guest]

Hello againa and TIA!

Is the expression below to go into the Source Object property for the
subform??

Still getting error about naming convention.

Joel

Try:
=iif([F&B Kitchen Cuts Injury].Report.hasdata, [F&B Kitchen Cuts
Injury].Report.[primary_cause],0)

--
Duane Hookom
Microsoft Access MVP

Thanks Duane:

I understand about & but someone else used in in the report names so I
understand using []'s.

but I'm still having trouble.

I have:

=iif([primary_cause].[F&B Kitchen Cuts Injury].hasdata,[primary_cause].[F&B
Kitchen Cuts Injury].txtonsub,0)

What's my problem?
([primary_cause] is the text box on the subreport named [F&B Kitchen Cuts
Injury].

The main report is named f&b

Thanks again,

Joel

The "&" symbol is used to concatenate strings together like:
FirstName & " " & LastName
You should find and use a naming convention that doesn't allow spaces and
symbols. The only non alphanumeric that I find acceptable is the underscore.
I also recommend naming forms with something that identifies it as a form.
IMHO looking at the name of an object or variable should provide a clue as to
what type of object or variable is.

If you can't change your names, then you will have to place []s around them.
[f&b injuries].

One other point, I use forms as forms and reports as reports. I don't know
if you misspoke regarding "main form" and "subform".
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane:

I have tried several options but get an "naming error".

My main form is "f&b"

I have another form named "f&b injuries" with a field "primary_cause"

On the main form I added a subform control and have the source as Report.f&b
injuries.

I assume the iif statement is for the control source for the subform.

I have =iif(report.f&b injuries.hasdata, report.f&b injuries, report.f&b
injuries.primary_cause=0) I get error, "doen't follow naming rules...'

Any additional help on syntax??

Thanks,

Joel



:

You can use an expression like:
=IIf(subrptcntrl.report.HasData, subrptcntrl.report.txtOnSub, 0)
--
Duane Hookom
Microsoft Access MVP


:

TIA:

I have a report with subreport (they are not linked). The subreport is
based on a query that queries a text field. The subreport has a control
with source=count([textfield]). When there is 1 or more records, I get a
count in the subform and see it on the main form. when there are no records
i get an #error on the subform and no data on the main form. An IIF clause in
the subform control to test for error and then return 0 if true, works on the
subform but I still see nothing on the main form.

So...how can i get a 0 value on the main form when a subforms' query count
has no records??

Thanks,

Joel

 

[Guest]

Ok, assuming:
You have a text box on a subreport on a main report.
Text Box Name: txtMyTotal
Subreport Control Name on Main Report: srptMySubRptCtl
You could place a text box on the main report with a control source of:
=IIf(srptMySubRptCtl.report.HasData, srptMySubRptCtl.Report.txtMyTotal, 0)

Keep in mind that a subreport control on the main report can be given almost
any name you want. You can name it PieAndIceCream if you want. If you do
this, your above expression would be:
=IIf(PieAndIceCream.report.HasData, PieAndIceCream.Report.txtMyTotal, 0)

--
Duane Hookom
Microsoft Access MVP

Hello againa and TIA!

Is the expression below to go into the Source Object property for the
subform??

Still getting error about naming convention.

Joel

Try:
=iif([F&B Kitchen Cuts Injury].Report.hasdata, [F&B Kitchen Cuts
Injury].Report.[primary_cause],0)

--
Duane Hookom
Microsoft Access MVP

Thanks Duane:

I understand about & but someone else used in in the report names so I
understand using []'s.

but I'm still having trouble.

I have:

=iif([primary_cause].[F&B Kitchen Cuts Injury].hasdata,[primary_cause].[F&B
Kitchen Cuts Injury].txtonsub,0)

What's my problem?
([primary_cause] is the text box on the subreport named [F&B Kitchen Cuts
Injury].

The main report is named f&b

Thanks again,

Joel


:

The "&" symbol is used to concatenate strings together like:
FirstName & " " & LastName
You should find and use a naming convention that doesn't allow spaces and
symbols. The only non alphanumeric that I find acceptable is the underscore.
I also recommend naming forms with something that identifies it as a form.
IMHO looking at the name of an object or variable should provide a clue as to
what type of object or variable is.

If you can't change your names, then you will have to place []s around them.
[f&b injuries].

One other point, I use forms as forms and reports as reports. I don't know
if you misspoke regarding "main form" and "subform".
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane:

I have tried several options but get an "naming error".

My main form is "f&b"

I have another form named "f&b injuries" with a field "primary_cause"

On the main form I added a subform control and have the source as Report.f&b
injuries.

I assume the iif statement is for the control source for the subform.

I have =iif(report.f&b injuries.hasdata, report.f&b injuries, report.f&b
injuries.primary_cause=0) I get error, "doen't follow naming rules...'

Any additional help on syntax??

Thanks,

Joel



:

You can use an expression like:
=IIf(subrptcntrl.report.HasData, subrptcntrl.report.txtOnSub, 0)
--
Duane Hookom
Microsoft Access MVP


:

TIA:

I have a report with subreport (they are not linked). The subreport is
based on a query that queries a text field. The subreport has a control
with source=count([textfield]). When there is 1 or more records, I get a
count in the subform and see it on the main form. when there are no records
i get an #error on the subform and no data on the main form. An IIF clause in
the subform control to test for error and then return 0 if true, works on the
subform but I still see nothing on the main form.

So...how can i get a 0 value on the main form when a subforms' query count
has no records??

Thanks,

Joel

 

[Guest]

Hello Duane:

Thanks for your patience with my ignorance. I was trying to control the
subreport itself on a main report...not a text box on the main report.

I understand what you are talking about.

What I actually have (now I tell you) is the subreport has
count(primary_cause) in the report footer section as that it is the actual
value shown on the Main report. When there are no records the sub report has
error and the main report shows nothing so once again there is the problem.

I also realized I had an error with the object name for the subreport...

I tried your suggestion so here is what I have and now get #error

=IIf([F&B Kitchen Cuts].[Report].[HasData],Count([F&B Kitchen
Cuts].[primary_cause]),0)

Once again I was getting count via the subreport report footer.

TIA..

Joel

Ok, assuming:
You have a text box on a subreport on a main report.
Text Box Name: txtMyTotal
Subreport Control Name on Main Report: srptMySubRptCtl
You could place a text box on the main report with a control source of:
=IIf(srptMySubRptCtl.report.HasData, srptMySubRptCtl.Report.txtMyTotal, 0)

Keep in mind that a subreport control on the main report can be given almost
any name you want. You can name it PieAndIceCream if you want. If you do
this, your above expression would be:
=IIf(PieAndIceCream.report.HasData, PieAndIceCream.Report.txtMyTotal, 0)

--
Duane Hookom
Microsoft Access MVP

Hello againa and TIA!

Is the expression below to go into the Source Object property for the
subform??

Still getting error about naming convention.

Joel

Try:
=iif([F&B Kitchen Cuts Injury].Report.hasdata, [F&B Kitchen Cuts
Injury].Report.[primary_cause],0)

--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane:

I understand about & but someone else used in in the report names so I
understand using []'s.

but I'm still having trouble.

I have:

=iif([primary_cause].[F&B Kitchen Cuts Injury].hasdata,[primary_cause].[F&B
Kitchen Cuts Injury].txtonsub,0)

What's my problem?
([primary_cause] is the text box on the subreport named [F&B Kitchen Cuts
Injury].

The main report is named f&b

Thanks again,

Joel


:

The "&" symbol is used to concatenate strings together like:
FirstName & " " & LastName
You should find and use a naming convention that doesn't allow spaces and
symbols. The only non alphanumeric that I find acceptable is the underscore.
I also recommend naming forms with something that identifies it as a form.
IMHO looking at the name of an object or variable should provide a clue as to
what type of object or variable is.

If you can't change your names, then you will have to place []s around them.
[f&b injuries].

One other point, I use forms as forms and reports as reports. I don't know
if you misspoke regarding "main form" and "subform".
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane:

I have tried several options but get an "naming error".

My main form is "f&b"

I have another form named "f&b injuries" with a field "primary_cause"

On the main form I added a subform control and have the source as Report.f&b
injuries.

I assume the iif statement is for the control source for the subform.

I have =iif(report.f&b injuries.hasdata, report.f&b injuries, report.f&b
injuries.primary_cause=0) I get error, "doen't follow naming rules...'

Any additional help on syntax??

Thanks,

Joel



:

You can use an expression like:
=IIf(subrptcntrl.report.HasData, subrptcntrl.report.txtOnSub, 0)
--
Duane Hookom
Microsoft Access MVP


:

TIA:

I have a report with subreport (they are not linked). The subreport is
based on a query that queries a text field. The subreport has a control
with source=count([textfield]). When there is 1 or more records, I get a
count in the subform and see it on the main form. when there are no records
i get an #error on the subform and no data on the main form. An IIF clause in
the subform control to test for error and then return 0 if true, works on the
subform but I still see nothing on the main form.

So...how can i get a 0 value on the main form when a subforms' query count
has no records??

Thanks,

Joel

 

[Guest]

Are you suggesting
- the subreport has records and is displayed and
- the main report has no records and
- the subreport has a text box that references a value from a text box on
the main report and
- the text box on the subreport displays error?

If so, what is the control source of the text box on the main report? You
should be able to use IIf(...hasdata...) in the control source of the text
box on the main report.
--
Duane Hookom
Microsoft Access MVP

Hello Duane:

Thanks for your patience with my ignorance. I was trying to control the
subreport itself on a main report...not a text box on the main report.

I understand what you are talking about.

What I actually have (now I tell you) is the subreport has
count(primary_cause) in the report footer section as that it is the actual
value shown on the Main report. When there are no records the sub report has
error and the main report shows nothing so once again there is the problem.

I also realized I had an error with the object name for the subreport...

I tried your suggestion so here is what I have and now get #error

=IIf([F&B Kitchen Cuts].[Report].[HasData],Count([F&B Kitchen
Cuts].[primary_cause]),0)

Once again I was getting count via the subreport report footer.

TIA..

Joel

Ok, assuming:
You have a text box on a subreport on a main report.
Text Box Name: txtMyTotal
Subreport Control Name on Main Report: srptMySubRptCtl
You could place a text box on the main report with a control source of:
=IIf(srptMySubRptCtl.report.HasData, srptMySubRptCtl.Report.txtMyTotal, 0)

Keep in mind that a subreport control on the main report can be given almost
any name you want. You can name it PieAndIceCream if you want. If you do
this, your above expression would be:
=IIf(PieAndIceCream.report.HasData, PieAndIceCream.Report.txtMyTotal, 0)

--
Duane Hookom
Microsoft Access MVP

Hello againa and TIA!

Is the expression below to go into the Source Object property for the
subform??

Still getting error about naming convention.

Joel

:

Try:
=iif([F&B Kitchen Cuts Injury].Report.hasdata, [F&B Kitchen Cuts
Injury].Report.[primary_cause],0)

--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane:

I understand about & but someone else used in in the report names so I
understand using []'s.

but I'm still having trouble.

I have:

=iif([primary_cause].[F&B Kitchen Cuts Injury].hasdata,[primary_cause].[F&B
Kitchen Cuts Injury].txtonsub,0)

What's my problem?
([primary_cause] is the text box on the subreport named [F&B Kitchen Cuts
Injury].

The main report is named f&b

Thanks again,

Joel


:

The "&" symbol is used to concatenate strings together like:
FirstName & " " & LastName
You should find and use a naming convention that doesn't allow spaces and
symbols. The only non alphanumeric that I find acceptable is the underscore.
I also recommend naming forms with something that identifies it as a form.
IMHO looking at the name of an object or variable should provide a clue as to
what type of object or variable is.

If you can't change your names, then you will have to place []s around them.
[f&b injuries].

One other point, I use forms as forms and reports as reports. I don't know
if you misspoke regarding "main form" and "subform".
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane:

I have tried several options but get an "naming error".

My main form is "f&b"

I have another form named "f&b injuries" with a field "primary_cause"

On the main form I added a subform control and have the source as Report.f&b
injuries.

I assume the iif statement is for the control source for the subform.

I have =iif(report.f&b injuries.hasdata, report.f&b injuries, report.f&b
injuries.primary_cause=0) I get error, "doen't follow naming rules...'

Any additional help on syntax??

Thanks,

Joel



:

You can use an expression like:
=IIf(subrptcntrl.report.HasData, subrptcntrl.report.txtOnSub, 0)
--
Duane Hookom
Microsoft Access MVP


:

TIA:

I have a report with subreport (they are not linked). The subreport is
based on a query that queries a text field. The subreport has a control
with source=count([textfield]). When there is 1 or more records, I get a
count in the subform and see it on the main form. when there are no records
i get an #error on the subform and no data on the main form. An IIF clause in
the subform control to test for error and then return 0 if true, works on the
subform but I still see nothing on the main form.

So...how can i get a 0 value on the main form when a subforms' query count
has no records??

Thanks,

Joel

 

[Guest]

Hello Duane:

The main report and subreport are not linked first of all. The main report
has records in detail section. I have subreport in report header section.

The subreport was used to get a total of some other unrelated data relative
to the main report. The subreports' source is a query to get a list of
records. Then I have Count records (primary_cause) in the subreports footer.
There are no controls in the detail section. I get the total number of
records from the query to show in the subreport which in turn shows on the
main report but only if records in query otherwise nothing as the issue is.

Joel

Are you suggesting
- the subreport has records and is displayed and
- the main report has no records and
- the subreport has a text box that references a value from a text box on
the main report and
- the text box on the subreport displays error?

If so, what is the control source of the text box on the main report? You
should be able to use IIf(...hasdata...) in the control source of the text
box on the main report.
--
Duane Hookom
Microsoft Access MVP

Hello Duane:

Thanks for your patience with my ignorance. I was trying to control the
subreport itself on a main report...not a text box on the main report.

I understand what you are talking about.

What I actually have (now I tell you) is the subreport has
count(primary_cause) in the report footer section as that it is the actual
value shown on the Main report. When there are no records the sub report has
error and the main report shows nothing so once again there is the problem.

I also realized I had an error with the object name for the subreport...

I tried your suggestion so here is what I have and now get #error

=IIf([F&B Kitchen Cuts].[Report].[HasData],Count([F&B Kitchen
Cuts].[primary_cause]),0)

Once again I was getting count via the subreport report footer.

TIA..

Joel

Ok, assuming:
You have a text box on a subreport on a main report.
Text Box Name: txtMyTotal
Subreport Control Name on Main Report: srptMySubRptCtl
You could place a text box on the main report with a control source of:
=IIf(srptMySubRptCtl.report.HasData, srptMySubRptCtl.Report.txtMyTotal, 0)

Keep in mind that a subreport control on the main report can be given almost
any name you want. You can name it PieAndIceCream if you want. If you do
this, your above expression would be:
=IIf(PieAndIceCream.report.HasData, PieAndIceCream.Report.txtMyTotal, 0)

--
Duane Hookom
Microsoft Access MVP


:

Hello againa and TIA!

Is the expression below to go into the Source Object property for the
subform??

Still getting error about naming convention.

Joel

:

Try:
=iif([F&B Kitchen Cuts Injury].Report.hasdata, [F&B Kitchen Cuts
Injury].Report.[primary_cause],0)

--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane:

I understand about & but someone else used in in the report names so I
understand using []'s.

but I'm still having trouble.

I have:

=iif([primary_cause].[F&B Kitchen Cuts Injury].hasdata,[primary_cause].[F&B
Kitchen Cuts Injury].txtonsub,0)

What's my problem?
([primary_cause] is the text box on the subreport named [F&B Kitchen Cuts
Injury].

The main report is named f&b

Thanks again,

Joel


:

The "&" symbol is used to concatenate strings together like:
FirstName & " " & LastName
You should find and use a naming convention that doesn't allow spaces and
symbols. The only non alphanumeric that I find acceptable is the underscore.
I also recommend naming forms with something that identifies it as a form.
IMHO looking at the name of an object or variable should provide a clue as to
what type of object or variable is.

If you can't change your names, then you will have to place []s around them.
[f&b injuries].

One other point, I use forms as forms and reports as reports. I don't know
if you misspoke regarding "main form" and "subform".
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane:

I have tried several options but get an "naming error".

My main form is "f&b"

I have another form named "f&b injuries" with a field "primary_cause"

On the main form I added a subform control and have the source as Report.f&b
injuries.

I assume the iif statement is for the control source for the subform.

I have =iif(report.f&b injuries.hasdata, report.f&b injuries, report.f&b
injuries.primary_cause=0) I get error, "doen't follow naming rules...'

Any additional help on syntax??

Thanks,

Joel



:

You can use an expression like:
=IIf(subrptcntrl.report.HasData, subrptcntrl.report.txtOnSub, 0)
--
Duane Hookom
Microsoft Access MVP


:

TIA:

I have a report with subreport (they are not linked). The subreport is
based on a query that queries a text field. The subreport has a control
with source=count([textfield]). When there is 1 or more records, I get a
count in the subform and see it on the main form. when there are no records
i get an #error on the subform and no data on the main form. An IIF clause in
the subform control to test for error and then return 0 if true, works on the
subform but I still see nothing on the main form.

So...how can i get a 0 value on the main form when a subforms' query count
has no records??

Thanks,

Joel

 

[Guest]

Hello Duane:

With all of your help I have figured it out.

What I did was use iif and iserror to test textbox on subform, if iserror is
true, 0 otherwise return value of textbox on subform (which is count)..I
think I was really mixed up about "Name".

I will test you logic of hasdata...

Thanks a million,
Joel

Joel

Are you suggesting
- the subreport has records and is displayed and
- the main report has no records and
- the subreport has a text box that references a value from a text box on
the main report and
- the text box on the subreport displays error?

If so, what is the control source of the text box on the main report? You
should be able to use IIf(...hasdata...) in the control source of the text
box on the main report.
--
Duane Hookom
Microsoft Access MVP

Hello Duane:

Thanks for your patience with my ignorance. I was trying to control the
subreport itself on a main report...not a text box on the main report.

I understand what you are talking about.

What I actually have (now I tell you) is the subreport has
count(primary_cause) in the report footer section as that it is the actual
value shown on the Main report. When there are no records the sub report has
error and the main report shows nothing so once again there is the problem.

I also realized I had an error with the object name for the subreport...

I tried your suggestion so here is what I have and now get #error

=IIf([F&B Kitchen Cuts].[Report].[HasData],Count([F&B Kitchen
Cuts].[primary_cause]),0)

Once again I was getting count via the subreport report footer.

TIA..

Joel

Ok, assuming:
You have a text box on a subreport on a main report.
Text Box Name: txtMyTotal
Subreport Control Name on Main Report: srptMySubRptCtl
You could place a text box on the main report with a control source of:
=IIf(srptMySubRptCtl.report.HasData, srptMySubRptCtl.Report.txtMyTotal, 0)

Keep in mind that a subreport control on the main report can be given almost
any name you want. You can name it PieAndIceCream if you want. If you do
this, your above expression would be:
=IIf(PieAndIceCream.report.HasData, PieAndIceCream.Report.txtMyTotal, 0)

--
Duane Hookom
Microsoft Access MVP


:

Hello againa and TIA!

Is the expression below to go into the Source Object property for the
subform??

Still getting error about naming convention.

Joel

:

Try:
=iif([F&B Kitchen Cuts Injury].Report.hasdata, [F&B Kitchen Cuts
Injury].Report.[primary_cause],0)

--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane:

I understand about & but someone else used in in the report names so I
understand using []'s.

but I'm still having trouble.

I have:

=iif([primary_cause].[F&B Kitchen Cuts Injury].hasdata,[primary_cause].[F&B
Kitchen Cuts Injury].txtonsub,0)

What's my problem?
([primary_cause] is the text box on the subreport named [F&B Kitchen Cuts
Injury].

The main report is named f&b

Thanks again,

Joel


:

The "&" symbol is used to concatenate strings together like:
FirstName & " " & LastName
You should find and use a naming convention that doesn't allow spaces and
symbols. The only non alphanumeric that I find acceptable is the underscore.
I also recommend naming forms with something that identifies it as a form.
IMHO looking at the name of an object or variable should provide a clue as to
what type of object or variable is.

If you can't change your names, then you will have to place []s around them.
[f&b injuries].

One other point, I use forms as forms and reports as reports. I don't know
if you misspoke regarding "main form" and "subform".
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane:

I have tried several options but get an "naming error".

My main form is "f&b"

I have another form named "f&b injuries" with a field "primary_cause"

On the main form I added a subform control and have the source as Report.f&b
injuries.

I assume the iif statement is for the control source for the subform.

I have =iif(report.f&b injuries.hasdata, report.f&b injuries, report.f&b
injuries.primary_cause=0) I get error, "doen't follow naming rules...'

Any additional help on syntax??

Thanks,

Joel



:

You can use an expression like:
=IIf(subrptcntrl.report.HasData, subrptcntrl.report.txtOnSub, 0)
--
Duane Hookom
Microsoft Access MVP


:

TIA:

I have a report with subreport (they are not linked). The subreport is
based on a query that queries a text field. The subreport has a control
with source=count([textfield]). When there is 1 or more records, I get a
count in the subform and see it on the main form. when there are no records
i get an #error on the subform and no data on the main form. An IIF clause in
the subform control to test for error and then return 0 if true, works on the
subform but I still see nothing on the main form.

So...how can i get a 0 value on the main form when a subforms' query count
has no records??

Thanks,

Joel