Referencing text boxes

  • Thread starter Thread starter Ivan
  • Start date Start date
I

Ivan

I have a report where I use Dcount in the following formula:
=DCount("[qryTrained]![PersonID]","qryTrained","[TrainingBU]=[Reports]![SSby
TrainingUnit]![TrainingBU]")

it works fine. I would like to copy the textbox with the formula to other
reports but the references don't work. I know the Me! operator can be used
in VBA but I can't get it to work in the expressionbuilder. I'd like to use
something like this, that can be pasted onto any report:

=DCount("[qryTrainedBB]![PersonID]","qryTrainedBB","[Me]![TrainingBU]")

Is this possible? What's the correct syntax?

Thanks,

Ivan
 
Usually DCount() is a horrible waste of resources in a group or report
footer. Most instances allow you to use:
=Sum([your expression here])
If the domain in your DCount() is the same as the record source of the
report ie: your report's record source is "qryTrained", then you can use a
much more efficient solution. We would need to know a little more about your
report's record source and what you wanted returned by your expression.

An example would be if you wanted to count all the female employees whose
Wage > 50000 in your group footer:
=Sum( Abs([Gender]="F" AND [Wage]>50000))
This expression takes advantage of the reports record source rather than
creating a new recordset as DCount() will do.
 
Duane,

The report record source is one query (not qryTrained). Most of the text
boxes pull data from the underlying query but I need a couple of other boxes
to extract data from other unrelated queries to perform calculations.

Is there a way to use the Me reference in forms?

Ivan

Duane Hookom said:
Usually DCount() is a horrible waste of resources in a group or report
footer. Most instances allow you to use:
=Sum([your expression here])
If the domain in your DCount() is the same as the record source of the
report ie: your report's record source is "qryTrained", then you can use a
much more efficient solution. We would need to know a little more about your
report's record source and what you wanted returned by your expression.

An example would be if you wanted to count all the female employees whose
Wage > 50000 in your group footer:
=Sum( Abs([Gender]="F" AND [Wage]>50000))
This expression takes advantage of the reports record source rather than
creating a new recordset as DCount() will do.

--
Duane Hookom
MS Access MVP


Ivan said:
I have a report where I use Dcount in the following formula:
Click to expand...
=DCount("[qryTrained]![PersonID]","qryTrained","[TrainingBU]=[Reports]![SSby
TrainingUnit]![TrainingBU]")

it works fine. I would like to copy the textbox with the formula to other
reports but the references don't work. I know the Me! operator can be used
in VBA but I can't get it to work in the expressionbuilder. I'd like to use
something like this, that can be pasted onto any report:

=DCount("[qryTrainedBB]![PersonID]","qryTrainedBB","[Me]![TrainingBU]")

Is this possible? What's the correct syntax?

Thanks,

Ivan
Click to expand...
Click to expand...
 
There is no need to use Me in a text box. For instance if you want to set
the where clause based on the current value of TrainingBU in the report,
try:
=DCount("[PersonID]", "qryTrained", "[TrainingBU]=" & [TrainingBU])
If TrainingBU is text then try
=DCount("[PersonID]", "qryTrained", "[TrainingBU]=""" & [TrainingBU] & """")
--
Duane Hookom
MS Access MVP


Ivan said:
Duane,

The report record source is one query (not qryTrained). Most of the text
boxes pull data from the underlying query but I need a couple of other boxes
to extract data from other unrelated queries to perform calculations.

Is there a way to use the Me reference in forms?

Ivan

Duane Hookom said:
Usually DCount() is a horrible waste of resources in a group or report
footer. Most instances allow you to use:
=Sum([your expression here])
If the domain in your DCount() is the same as the record source of the
report ie: your report's record source is "qryTrained", then you can use a
much more efficient solution. We would need to know a little more about your
report's record source and what you wanted returned by your expression.

An example would be if you wanted to count all the female employees whose
Wage > 50000 in your group footer:
=Sum( Abs([Gender]="F" AND [Wage]>50000))
This expression takes advantage of the reports record source rather than
creating a new recordset as DCount() will do.

--
Duane Hookom
MS Access MVP


Ivan said:
I have a report where I use Dcount in the following formula:
Click to expand...
Click to expand...
=DCount("[qryTrained]![PersonID]","qryTrained","[TrainingBU]=[Reports]![SSby
TrainingUnit]![TrainingBU]")

it works fine. I would like to copy the textbox with the formula to other
reports but the references don't work. I know the Me! operator can be used
in VBA but I can't get it to work in the expressionbuilder. I'd like
Click to expand...
to
use
something like this, that can be pasted onto any report:

=DCount("[qryTrainedBB]![PersonID]","qryTrainedBB","[Me]![TrainingBU]")

Is this possible? What's the correct syntax?

Thanks,

Ivan
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the solution Duane. I'll try it out at work!

For my clarification though, the [TrainingBU] enclosed in double quotes is
the text box right? The one not surrounded by quotes is the query field
name. Is this how Access determines one from the other?

Duane Hookom said:
There is no need to use Me in a text box. For instance if you want to set
the where clause based on the current value of TrainingBU in the report,
try:
=DCount("[PersonID]", "qryTrained", "[TrainingBU]=" & [TrainingBU])
If TrainingBU is text then try
=DCount("[PersonID]", "qryTrained", "[TrainingBU]=""" & [TrainingBU] & """")
--
Duane Hookom
MS Access MVP


Ivan said:
Duane,

The report record source is one query (not qryTrained). Most of the text
boxes pull data from the underlying query but I need a couple of other boxes
to extract data from other unrelated queries to perform calculations.

Is there a way to use the Me reference in forms?

Ivan

Duane Hookom said:
Usually DCount() is a horrible waste of resources in a group or report
footer. Most instances allow you to use:
=Sum([your expression here])
If the domain in your DCount() is the same as the record source of the
report ie: your report's record source is "qryTrained", then you can
Click to expand...
Click to expand...
use
a
much more efficient solution. We would need to know a little more
Click to expand...
about
your
report's record source and what you wanted returned by your expression.

An example would be if you wanted to count all the female employees whose
Wage > 50000 in your group footer:
=Sum( Abs([Gender]="F" AND [Wage]>50000))
This expression takes advantage of the reports record source rather than
creating a new recordset as DCount() will do.

--
Duane Hookom
MS Access MVP


I have a report where I use Dcount in the following formula:
Click to expand...
Click to expand...
=DCount("[qryTrained]![PersonID]","qryTrained","[TrainingBU]=[Reports]![SSby
TrainingUnit]![TrainingBU]")

it works fine. I would like to copy the textbox with the formula to other
reports but the references don't work. I know the Me! operator can
Click to expand...
be
used
in VBA but I can't get it to work in the expressionbuilder. I'd like to
use
something like this, that can be pasted onto any report:

=DCount("[qryTrainedBB]![PersonID]","qryTrainedBB","[Me]![TrainingBU]")

Is this possible? What's the correct syntax?

Thanks,

Ivan
Click to expand...
Click to expand...
Click to expand...
 
Back
Top