Count function causing error

[Guest]

I have a text box in the report footer that I want to count the instance "Y"s
or "N"s in a text box in the detail section.

It should be as easy as =-Sum([AttitudeComp]="Y"), but when I run the
report I get an input box to enter the parameter value of AttitudeComp. I
have rewritten the code several times, checked the spelling and even copied
and pasted the name, but I keep getting the request for the value. If I
enter the "Y" (or "N") I get a count of all the records.

What am I doing wrong??

 

[Ken Snell \(MVP\)]

What is the name of the textbox in the detail section? What is the Control
Source of that textbox?

 

[Guest]

The name of the text box is AttitudeComp. It is an unbound text box that
gets it's value from visual basic code:

If [1st Eval.AttitudeNum] < [End Eval.AttitudeNum] Then
[AttitudeComp] = "Y"
ElseIf [1st Eval.AttitudeNum] > [End Eval.AttitudeNum] Then
[AttitudeComp] = "N"
Else
[AttitudeComp] = "S"
End If

What is the name of the textbox in the detail section? What is the Control
Source of that textbox?

--

Ken Snell
<MS ACCESS MVP>

I have a text box in the report footer that I want to count the instance
"Y"s
or "N"s in a text box in the detail section.

It should be as easy as =-Sum([AttitudeComp]="Y"), but when I run the
report I get an input box to enter the parameter value of AttitudeComp. I
have rewritten the code several times, checked the spelling and even
copied
and pasted the name, but I keep getting the request for the value. If I
enter the "Y" (or "N") I get a count of all the records.

What am I doing wrong??

 

[Ken Snell \(MVP\)]

The Sum function will work only with a field that is in the report's
RecordSource query; it will not work for a textbox on the report itself.

Therefore, you'll need a completely different approach to this problem. What
is the VBA code that sets the value in the textbox?

--

Ken Snell
<MS ACCESS MVP>

The name of the text box is AttitudeComp. It is an unbound text box that
gets it's value from visual basic code:

If [1st Eval.AttitudeNum] < [End Eval.AttitudeNum] Then
[AttitudeComp] = "Y"
ElseIf [1st Eval.AttitudeNum] > [End Eval.AttitudeNum] Then
[AttitudeComp] = "N"
Else
[AttitudeComp] = "S"
End If

What is the name of the textbox in the detail section? What is the
Control
Source of that textbox?

--

Ken Snell
<MS ACCESS MVP>

I have a text box in the report footer that I want to count the instance
"Y"s
or "N"s in a text box in the detail section.

It should be as easy as =-Sum([AttitudeComp]="Y"), but when I run the
report I get an input box to enter the parameter value of AttitudeComp.
I
have rewritten the code several times, checked the spelling and even
copied
and pasted the name, but I keep getting the request for the value. If
I
enter the "Y" (or "N") I get a count of all the records.

What am I doing wrong??

 

[Guest]

It's below:

If [1st Eval.AttitudeNum] < [End Eval.AttitudeNum] Then

[AttitudeComp] = "Y"
ElseIf [1st Eval.AttitudeNum] > [End Eval.AttitudeNum] Then
[AttitudeComp] = "N"
Else
[AttitudeComp] = "S"
End If

1st Eval.AttitudeNum and End Eval.AttitudeNum both come from the underlying
query. They are Number fields that are entered via a form. What I am trying
to do is show an improvement or decline by comparing the numbers.

What would you suggest?

The Sum function will work only with a field that is in the report's
RecordSource query; it will not work for a textbox on the report itself.

Therefore, you'll need a completely different approach to this problem. What
is the VBA code that sets the value in the textbox?

--

Ken Snell
<MS ACCESS MVP>

The name of the text box is AttitudeComp. It is an unbound text box that
gets it's value from visual basic code:

If [1st Eval.AttitudeNum] < [End Eval.AttitudeNum] Then
[AttitudeComp] = "Y"
ElseIf [1st Eval.AttitudeNum] > [End Eval.AttitudeNum] Then
[AttitudeComp] = "N"
Else
[AttitudeComp] = "S"
End If

What is the name of the textbox in the detail section? What is the
Control
Source of that textbox?

--

Ken Snell
<MS ACCESS MVP>


I have a text box in the report footer that I want to count the instance
"Y"s
or "N"s in a text box in the detail section.

It should be as easy as =-Sum([AttitudeComp]="Y"), but when I run the
report I get an input box to enter the parameter value of AttitudeComp.
I
have rewritten the code several times, checked the spelling and even
copied
and pasted the name, but I keep getting the request for the value. If
I
enter the "Y" (or "N") I get a count of all the records.

What am I doing wrong??

 

[Ken Snell \(MVP\)]

I recommend that you not use the VBA code at all. Instead, use this
expression for the Control Source property of the AttitudeComp textbox:

=IIf([1st Eval.AttitudeNum] < [End Eval.AttitudeNum], "Y", IIf([1st
Eval.AttitudeNum] > [End Eval.AttitudeNum], "N", "S"))


Then, use this expression as the Control Source property of the textbox in
the report's footer section:

=Abs(Sum(([1st Eval.AttitudeNum] < [End Eval.AttitudeNum]) = True))


--

Ken Snell
<MS ACCESS MVP>

It's below:

If [1st Eval.AttitudeNum] < [End Eval.AttitudeNum] Then

[AttitudeComp] = "Y"
ElseIf [1st Eval.AttitudeNum] > [End Eval.AttitudeNum] Then
[AttitudeComp] = "N"
Else
[AttitudeComp] = "S"
End If

1st Eval.AttitudeNum and End Eval.AttitudeNum both come from the
underlying
query. They are Number fields that are entered via a form. What I am
trying
to do is show an improvement or decline by comparing the numbers.

What would you suggest?

The Sum function will work only with a field that is in the report's
RecordSource query; it will not work for a textbox on the report itself.

Therefore, you'll need a completely different approach to this problem.
What
is the VBA code that sets the value in the textbox?

--

Ken Snell
<MS ACCESS MVP>

The name of the text box is AttitudeComp. It is an unbound text box
that
gets it's value from visual basic code:

If [1st Eval.AttitudeNum] < [End Eval.AttitudeNum] Then
[AttitudeComp] = "Y"
ElseIf [1st Eval.AttitudeNum] > [End Eval.AttitudeNum] Then
[AttitudeComp] = "N"
Else
[AttitudeComp] = "S"
End If

:

What is the name of the textbox in the detail section? What is the
Control
Source of that textbox?

--

Ken Snell
<MS ACCESS MVP>


I have a text box in the report footer that I want to count the
instance
"Y"s
or "N"s in a text box in the detail section.

It should be as easy as =-Sum([AttitudeComp]="Y"), but when I run
the
report I get an input box to enter the parameter value of
AttitudeComp.
I
have rewritten the code several times, checked the spelling and even
copied
and pasted the name, but I keep getting the request for the value.
If
I
enter the "Y" (or "N") I get a count of all the records.

What am I doing wrong??

 

[Guest]

Hi Ken,

Thanks for the bit of code and sorry that it took me so long to get back to
you. More than one Iron in the fire so to speak and the other had a deadline
of today. Now that I've had time I tried the code in the "AttitudeComp" text
box like you suggested and I get a parameter box that askes for the value of
"1st" and then the AttitudeComp Box in the Print/View says #Name?. I've
checked and checked and nowhere do I have anything named 1st. I even deleted
the text boxes and redid them from scratch, but i keep getting the same
thing.

I can't figure this out...I'm thinking at this point maybe I'll just delete
this form and start over. Maybe the form is corrupted?

I recommend that you not use the VBA code at all. Instead, use this
expression for the Control Source property of the AttitudeComp textbox:

=IIf([1st Eval.AttitudeNum] < [End Eval.AttitudeNum], "Y", IIf([1st
Eval.AttitudeNum] > [End Eval.AttitudeNum], "N", "S"))


Then, use this expression as the Control Source property of the textbox in
the report's footer section:

=Abs(Sum(([1st Eval.AttitudeNum] < [End Eval.AttitudeNum]) = True))


--

Ken Snell
<MS ACCESS MVP>

It's below:

If [1st Eval.AttitudeNum] < [End Eval.AttitudeNum] Then

[AttitudeComp] = "Y"
ElseIf [1st Eval.AttitudeNum] > [End Eval.AttitudeNum] Then
[AttitudeComp] = "N"
Else
[AttitudeComp] = "S"
End If

1st Eval.AttitudeNum and End Eval.AttitudeNum both come from the
underlying
query. They are Number fields that are entered via a form. What I am
trying
to do is show an improvement or decline by comparing the numbers.

What would you suggest?

The Sum function will work only with a field that is in the report's
RecordSource query; it will not work for a textbox on the report itself.

Therefore, you'll need a completely different approach to this problem.
What
is the VBA code that sets the value in the textbox?

--

Ken Snell
<MS ACCESS MVP>


The name of the text box is AttitudeComp. It is an unbound text box
that
gets it's value from visual basic code:

If [1st Eval.AttitudeNum] < [End Eval.AttitudeNum] Then
[AttitudeComp] = "Y"
ElseIf [1st Eval.AttitudeNum] > [End Eval.AttitudeNum] Then
[AttitudeComp] = "N"
Else
[AttitudeComp] = "S"
End If

:

What is the name of the textbox in the detail section? What is the
Control
Source of that textbox?

--

Ken Snell
<MS ACCESS MVP>


I have a text box in the report footer that I want to count the
instance
"Y"s
or "N"s in a text box in the detail section.

It should be as easy as =-Sum([AttitudeComp]="Y"), but when I run
the
report I get an input box to enter the parameter value of
AttitudeComp.
I
have rewritten the code several times, checked the spelling and even
copied
and pasted the name, but I keep getting the request for the value.
If
I
enter the "Y" (or "N") I get a count of all the records.

What am I doing wrong??

 

[Guest]

Okay, I got it. Just typed it in instead of copying and pasting. Don't
know why that made a difference.

Thanks For your help!!!!

Hi Ken,

Thanks for the bit of code and sorry that it took me so long to get back to
you. More than one Iron in the fire so to speak and the other had a deadline
of today. Now that I've had time I tried the code in the "AttitudeComp" text
box like you suggested and I get a parameter box that askes for the value of
"1st" and then the AttitudeComp Box in the Print/View says #Name?. I've
checked and checked and nowhere do I have anything named 1st. I even deleted
the text boxes and redid them from scratch, but i keep getting the same
thing.

I can't figure this out...I'm thinking at this point maybe I'll just delete
this form and start over. Maybe the form is corrupted?

I recommend that you not use the VBA code at all. Instead, use this
expression for the Control Source property of the AttitudeComp textbox:

=IIf([1st Eval.AttitudeNum] < [End Eval.AttitudeNum], "Y", IIf([1st
Eval.AttitudeNum] > [End Eval.AttitudeNum], "N", "S"))


Then, use this expression as the Control Source property of the textbox in
the report's footer section:

=Abs(Sum(([1st Eval.AttitudeNum] < [End Eval.AttitudeNum]) = True))


--

Ken Snell
<MS ACCESS MVP>

It's below:

If [1st Eval.AttitudeNum] < [End Eval.AttitudeNum] Then
[AttitudeComp] = "Y"
ElseIf [1st Eval.AttitudeNum] > [End Eval.AttitudeNum] Then
[AttitudeComp] = "N"
Else
[AttitudeComp] = "S"
End If

1st Eval.AttitudeNum and End Eval.AttitudeNum both come from the
underlying
query. They are Number fields that are entered via a form. What I am
trying
to do is show an improvement or decline by comparing the numbers.

What would you suggest?


:

The Sum function will work only with a field that is in the report's
RecordSource query; it will not work for a textbox on the report itself.

Therefore, you'll need a completely different approach to this problem.
What
is the VBA code that sets the value in the textbox?

--

Ken Snell
<MS ACCESS MVP>


The name of the text box is AttitudeComp. It is an unbound text box
that
gets it's value from visual basic code:

If [1st Eval.AttitudeNum] < [End Eval.AttitudeNum] Then
[AttitudeComp] = "Y"
ElseIf [1st Eval.AttitudeNum] > [End Eval.AttitudeNum] Then
[AttitudeComp] = "N"
Else
[AttitudeComp] = "S"
End If

:

What is the name of the textbox in the detail section? What is the
Control
Source of that textbox?

--

Ken Snell
<MS ACCESS MVP>


I have a text box in the report footer that I want to count the
instance
"Y"s
or "N"s in a text box in the detail section.

It should be as easy as =-Sum([AttitudeComp]="Y"), but when I run
the
report I get an input box to enter the parameter value of
AttitudeComp.
I
have rewritten the code several times, checked the spelling and even
copied
and pasted the name, but I keep getting the request for the value.
If
I
enter the "Y" (or "N") I get a count of all the records.

What am I doing wrong??

 

[Ken Snell \(MVP\)]

You're welcome.
--

Ken Snell
<MS ACCESS MVP>

Okay, I got it. Just typed it in instead of copying and pasting. Don't
know why that made a difference.

Thanks For your help!!!!