|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
July 31st, 2007, 06:26 PM
posted to microsoft.public.access.reports
|
|||
|
|||
Count function causing error
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?? 
|
|
#2
July 31st, 2007, 07:29 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Count function causing error
What is the name of the textbox in the detail section? What is the Control
Source of that textbox? -- Ken Snell MS ACCESS MVP "Betsy" wrote in message ... 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??
|
|
#3
July 31st, 2007, 07:38 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Count function causing error
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 "Ken Snell (MVP)" wrote: What is the name of the textbox in the detail section? What is the Control Source of that textbox? -- Ken Snell MS ACCESS MVP "Betsy" wrote in message ... 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??
|
|
#4
July 31st, 2007, 09:45 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Count function causing error
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 "Betsy" wrote in message ... 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 "Ken Snell (MVP)" wrote: What is the name of the textbox in the detail section? What is the Control Source of that textbox? -- Ken Snell MS ACCESS MVP "Betsy" wrote in message ... 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??
|
|
#5
August 1st, 2007, 12:48 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Count function causing error
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? "Ken Snell (MVP)" wrote: 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 "Betsy" wrote in message ... 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 "Ken Snell (MVP)" wrote: What is the name of the textbox in the detail section? What is the Control Source of that textbox? -- Ken Snell MS ACCESS MVP "Betsy" wrote in message ... 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??
|
|
#6
August 1st, 2007, 02:28 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Count function causing error
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 "Betsy" wrote in message ... 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? "Ken Snell (MVP)" wrote: 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 "Betsy" wrote in message ... 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 "Ken Snell (MVP)" wrote: What is the name of the textbox in the detail section? What is the Control Source of that textbox? -- Ken Snell MS ACCESS MVP "Betsy" wrote in message ... 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??
|
|
#7
August 3rd, 2007, 06:32 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Count function causing error
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? "Ken Snell (MVP)" wrote: 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 "Betsy" wrote in message ... 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? "Ken Snell (MVP)" wrote: 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 "Betsy" wrote in message ... 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 "Ken Snell (MVP)" wrote: What is the name of the textbox in the detail section? What is the Control Source of that textbox? -- Ken Snell MS ACCESS MVP "Betsy" wrote in message ... 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??
|
|
#8
August 3rd, 2007, 07:08 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Count function causing error
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!!!! "Betsy" wrote: 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? "Ken Snell (MVP)" wrote: 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 "Betsy" wrote in message ... 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? "Ken Snell (MVP)" wrote: 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 "Betsy" wrote in message ... 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 "Ken Snell (MVP)" wrote: What is the name of the textbox in the detail section? What is the Control Source of that textbox? -- Ken Snell MS ACCESS MVP "Betsy" wrote in message ... 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??
|
|
#9
August 3rd, 2007, 08:01 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Count function causing error
You're welcome.
-- Ken Snell MS ACCESS MVP "Betsy" wrote in message ... 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!!!! "Betsy" wrote: 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?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
