DCount in Text box from a parameter query

#1 (**permalink**) 28-Jul-2006, 08:40 AM

I set up a report using a simple query as the control source. I used text
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13769-dcount-in-text-box-parameter-query.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13769
boxes with a DCount expression and it worked. I then added a parameter to
the query ie.[Enter Account Number], the query returns the correct
information but my Report text boxes (with DCount expressions) all say error?
--
PRD

*

#2 (**permalink**) 28-Jul-2006, 08:40 AM

PD,

What are the DCount expressions?

--
Steve Schapel, Microsoft Access MVP

PD wrote:
> I set up a report using a simple query as the control source. I used text
> boxes with a DCount expression and it worked. I then added a parameter to
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13769
> the query ie.[Enter Account Number], the query returns the correct
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13769
> information but my Report text boxes (with DCount expressions) all say error?

#3 (**permalink**) 28-Jul-2006, 08:40 AM

=DCount("[Cal]","Query for Inspection Type Report","[Cal] = 'Dec'")
--
PRD

"Steve Schapel" wrote:

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13769
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13769
> PD,
>
> What are the DCount expressions?
>
> --
> Steve Schapel, Microsoft Access MVP
>
> PD wrote:
> > I set up a report using a simple query as the control source. I used text
> > boxes with a DCount expression and it worked. I then added a parameter to
> > the query ie.[Enter Account Number], the query returns the correct
> > information but my Report text boxes (with DCount expressions) all say error?
>

#4 (**permalink**) 28-Jul-2006, 08:40 AM

PD,

Thanks. Ok, is "Query for Inspection Type Report" the name of the query
that the report is based on? If so, it makes sense that Access can't
evaluate the DCount if there is an unevaluated Parameter.

One approach would be to forget the Parameter Query idea altogether, and
instead put an unbound terxtbox on a form which the user can use to
enter the Account Number. Then, in the query, replace the parameter
prompt with a reference to the textbox, using syntax such as...
[Forms]![NameOfForm]![NameOfTextbox]

In fact, I would recommmend doing this in any case.

Then, you could further simplify by changing the expression for your Dec
textbox to like this...
=-Sum([Cal]='Dec')

--
Steve Schapel, Microsoft Access MVP

PD wrote:
> =DCount("[Cal]","Query for Inspection Type Report","[Cal] = 'Dec'")
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13769
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13769

#5 (**permalink**) 28-Jul-2006, 08:40 AM

Ah ha!
Perfect and thank you very much.
--
PRD

"Steve Schapel" wrote:

> PD,
>
> Thanks. Ok, is "Query for Inspection Type Report" the name of the query
> that the report is based on? If so, it makes sense that Access can't
> evaluate the DCount if there is an unevaluated Parameter.
>
> One approach would be to forget the Parameter Query idea altogether, and
> instead put an unbound terxtbox on a form which the user can use to
> enter the Account Number. Then, in the query, replace the parameter
> prompt with a reference to the textbox, using syntax such as...
> [Forms]![NameOfForm]![NameOfTextbox]
>
> In fact, I would recommmend doing this in any case.
>
> Then, you could further simplify by changing the expression for your Dec
> textbox to like this...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13769
> =-Sum([Cal]='Dec')
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13769
>
> --
> Steve Schapel, Microsoft Access MVP
>
> PD wrote:
> > =DCount("[Cal]","Query for Inspection Type Report","[Cal] = 'Dec'")
>