Help with expression

[Guest]

I am using the following expression in an unbound textbox in a report based
on a field in a table formated as a Yes/No checkbox. The expression returns
an #Error. Can someone tell me why? Thank you
=Count([Tbl_Rounds].[PHIView])

This expression works fine in a query I made to test it.
Total_PHI: Count([Tbl_Rounds].[PHIView])

 

[David Lloyd]

Jeff:

The DCount function was designed for reports to supply the same
functionality as the Count function does in a query. For example:

=DCount("[PHIView]", "Tbl_Rounds")

The DCount function allows a third parameter which is equivalent to a WHERE
clause. See Access help for more information.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I am using the following expression in an unbound textbox in a report based
on a field in a table formated as a Yes/No checkbox. The expression returns
an #Error. Can someone tell me why? Thank you
=Count([Tbl_Rounds].[PHIView])

This expression works fine in a query I made to test it.
Total_PHI: Count([Tbl_Rounds].[PHIView])

 

[John Vinson]

I am using the following expression in an unbound textbox in a report based
on a field in a table formated as a Yes/No checkbox. The expression returns
an #Error. Can someone tell me why? Thank you
=Count([Tbl_Rounds].[PHIView])

This expression works fine in a query I made to test it.
Total_PHI: Count([Tbl_Rounds].[PHIView])

Well... base your Form on that Query, and bind the textbox to
Total_PHI!

Or, use the DCount() function:

=DCount("*", "Tbl_Rounds", "PHIView = True")

assuming that you want to count records where the value is checked.
Note that Count (or DCount) does not count *values*, it counts
*records* - in your query, I'm guessing that Total_PHI will not be the
count of True values of PHIView, but rather a count of all the records
in the table.

John W. Vinson[MVP]

 

[SusanV]

Hi John,

Reading this thread I find I have a similar but not quite identical
problem - my report repScopeOfPM has the source set as "SELECT this from
thatTable Where..." (full sql below).
I'm using this method as a few users have bad habit of messing with stored
queries, and i need the user input box. It works fine for the report record
details, but i would like a count in the report header of how many reports
were pulled up and no matter whether i use Count or DCount I get the #Error.

I tried:
=Count ([OpStat]) which works fine in theReport Details section, but I don't
want this on every line of the report - just in the report header. As soon
as I move it to the Page header or Report header it goes to #error

After seeing your post, I tried:
=DCount("[Opstat]", "repScopeOfPM"), result is #Error
And also tried (expecting an insane count of all records in the table,
nearly 900,000!)
=DCount("[Opstat]", "[FleetPM]") and even that give me #Error.
<sigh>

Any ideas? This ought to be fairly straight-forward but it's giving me
trouble..

TIA,

Susan

full SQL (report source):
SELECT [FleetPM].[VesselName], [FleetPM].[EquipmentName], [FleetPM].[HSC],
[FleetPM].[MCode], [MCode].[MCodeTitle], [FleetPM].[TaskCode],
[FleetPM].[Frequency], [FleetPM].[OpStat] FROM MCode INNER JOIN FleetPM ON
[MCode].[MCode]=[FleetPM].[MCode] WHERE ((([FleetPM].[MCode])=[Enter the
MCode number to check])) ORDER BY [FleetPM].[VesselName];

I am using the following expression in an unbound textbox in a report
based
on a field in a table formated as a Yes/No checkbox. The expression
returns
an #Error. Can someone tell me why? Thank you
=Count([Tbl_Rounds].[PHIView])

This expression works fine in a query I made to test it.
Total_PHI: Count([Tbl_Rounds].[PHIView])

Well... base your Form on that Query, and bind the textbox to
Total_PHI!

Or, use the DCount() function:

=DCount("*", "Tbl_Rounds", "PHIView = True")

assuming that you want to count records where the value is checked.
Note that Count (or DCount) does not count *values*, it counts
*records* - in your query, I'm guessing that Total_PHI will not be the
count of True values of PHIView, but rather a count of all the records
in the table.

John W. Vinson[MVP]

 

[Guest]

Thank you John and David:

The DCount function works in the report where the Count function works in
the query. I am trying to count all records so that I can come up with a
percentage of "NO" answers to the Yes/No Question.

My first attempt was to build a query but it became too long and I assumed
that if the expressions worked in the query I might as weel just assign them
to unbound text boxes in the report. I appreciate all your help.

I am using the following expression in an unbound textbox in a report based
on a field in a table formated as a Yes/No checkbox. The expression returns
an #Error. Can someone tell me why? Thank you
=Count([Tbl_Rounds].[PHIView])

This expression works fine in a query I made to test it.
Total_PHI: Count([Tbl_Rounds].[PHIView])

Well... base your Form on that Query, and bind the textbox to
Total_PHI!

Or, use the DCount() function:

=DCount("*", "Tbl_Rounds", "PHIView = True")

assuming that you want to count records where the value is checked.
Note that Count (or DCount) does not count *values*, it counts
*records* - in your query, I'm guessing that Total_PHI will not be the
count of True values of PHIView, but rather a count of all the records
in the table.

John W. Vinson[MVP]

 

[SusanV]

Nevermind - by changing it to =Count(*) in the Report Header it works - just
not in Page header, which is fine.

S

Hi John,

Reading this thread I find I have a similar but not quite identical
problem - my report repScopeOfPM has the source set as "SELECT this from
thatTable Where..." (full sql below).
I'm using this method as a few users have bad habit of messing with stored
queries, and i need the user input box. It works fine for the report
record details, but i would like a count in the report header of how many
reports were pulled up and no matter whether i use Count or DCount I get
the #Error.

I tried:
=Count ([OpStat]) which works fine in theReport Details section, but I
don't want this on every line of the report - just in the report header.
As soon as I move it to the Page header or Report header it goes to #error

After seeing your post, I tried:
=DCount("[Opstat]", "repScopeOfPM"), result is #Error
And also tried (expecting an insane count of all records in the table,
nearly 900,000!)
=DCount("[Opstat]", "[FleetPM]") and even that give me #Error.
<sigh>

Any ideas? This ought to be fairly straight-forward but it's giving me
trouble..

TIA,

Susan

full SQL (report source):
SELECT [FleetPM].[VesselName], [FleetPM].[EquipmentName], [FleetPM].[HSC],
[FleetPM].[MCode], [MCode].[MCodeTitle], [FleetPM].[TaskCode],
[FleetPM].[Frequency], [FleetPM].[OpStat] FROM MCode INNER JOIN FleetPM ON
[MCode].[MCode]=[FleetPM].[MCode] WHERE ((([FleetPM].[MCode])=[Enter the
MCode number to check])) ORDER BY [FleetPM].[VesselName];

I am using the following expression in an unbound textbox in a report
based
on a field in a table formated as a Yes/No checkbox. The expression
returns
an #Error. Can someone tell me why? Thank you
=Count([Tbl_Rounds].[PHIView])

This expression works fine in a query I made to test it.
Total_PHI: Count([Tbl_Rounds].[PHIView])

Well... base your Form on that Query, and bind the textbox to
Total_PHI!

Or, use the DCount() function:

=DCount("*", "Tbl_Rounds", "PHIView = True")

assuming that you want to count records where the value is checked.
Note that Count (or DCount) does not count *values*, it counts
*records* - in your query, I'm guessing that Total_PHI will not be the
count of True values of PHIView, but rather a count of all the records
in the table.

John W. Vinson[MVP]