count function troubleshooting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report based on a query. In the report I'm trying to create a text
box that will count the number of records in the query, however the =Count(*)
expression, when used in the report, results in an error. Any help?

Also, in the same report I'd like to be able to create another text box that
would sum a value based on Count number along with the prompted values of
several unboud text boxes, along with a constant (the fax cover page).
Specifically, the report is for refill requests, and displays 14 records per
page. If the count is <=14, I'd like the value to be 1, if 15 through 28,
I'd like it to be 2, etc. etc. So I'm thinking the expression would go
something like this:

=1+[New Prescriptions]+[Med Info Sheets]+[Provider Records]+(IIf([Total
Refills]<=14, "1", IIf([Total Refills] Between 15 And 28, "2", IIf([Total
Refills] Between 29 And 35, "3", "4"))))

I know this doesn't work though, so any correction would be great.

thanks,
Mike
 
Mike,

Don't use count in the report. Instead, use

=RecordSource.RecordCount

in the property sheet or in vba.

For the other text box, I think that what you are looking for is a fomula. I
think this might work:

=Int(RecordSource.RecordCount / 14) + IIf(RecordSource.RecordCount Mod14 > 0,
1, 0) + 1

in the property sheet or in vba.

To explain briefly, You're dividing your RecordCount by 14 and adding 1 to
that number if - and only if - the RecordCount is not evenly divisible by 14.
Then you're adding 1 to the sum for the fax cover page. This does not take
into account any prompted values from other text boxes on the form. You will
have to add them into the equation as well.

Hope this helps,

Sam
I have a report based on a query. In the report I'm trying to create a text
box that will count the number of records in the query, however the =Count(*)
expression, when used in the report, results in an error. Any help?

Also, in the same report I'd like to be able to create another text box that
would sum a value based on Count number along with the prompted values of
several unboud text boxes, along with a constant (the fax cover page).
Specifically, the report is for refill requests, and displays 14 records per
page. If the count is <=14, I'd like the value to be 1, if 15 through 28,
I'd like it to be 2, etc. etc. So I'm thinking the expression would go
something like this:

=1+[New Prescriptions]+[Med Info Sheets]+[Provider Records]+(IIf([Total
Refills]<=14, "1", IIf([Total Refills] Between 15 And 28, "2", IIf([Total
Refills] Between 29 And 35, "3", "4"))))

I know this doesn't work though, so any correction would be great.

thanks,
Mike
Click to expand...
 
Ok. Bear with me. I'm still new to Access.
=RecordSource.RecordCount
in the property sheet or in vba.
Click to expand...

Where exactly do I enter this expression in the property sheet?
=Int(RecordSource.RecordCount / 14) + IIf(RecordSource.RecordCount Mod14 > 0,
1, 0) + 1
in the property sheet or in vba.

To explain briefly, You're dividing your RecordCount by 14 and adding 1 to
that number if - and only if - the RecordCount is not evenly divisible by 14.
Then you're adding 1 to the sum for the fax cover page. This does not take
into account any prompted values from other text boxes on the form. You will
have to add them into the equation as well.
Click to expand...

Ok, that makes sense. But I'm assuming I need to have the above expression
done first, right? Just fyi, this is just a report I'm talking about. There
is no corresponding form; I'm prompted for the values when I open the report.
Will I still be able to use the prompt fields for the expression?

Thanks,
Mike
 
I experimented with it, and you need to put it in VBA. The Property Sheet
entry won't work. Keep the text box unbound, and in the report's Detail
section, make a VBA procedure that contains the line of code (slightly
different from my original)

Me.TextBoxName=Me.RecordSource.RecordCount

Of course, substitute the real name for TextBoxName.

For the other text box, instead of using RecordSource.RecordCount, use the
name of the text box being populated by VBA. In other words, use

=Int(TextBoxName / 14) + IIf(TextBoxName Mod14 > 0, 1, 0) + 1

with the real name right after the above line of code.

In order to properly capture the prompted information, create text boxes in
the Detail section of your report, bound to the fields you're prompting for.
In these text boxes' Property sheet, set the Visible property to No. Now you
can properly re-do the statement above.

Sam
Ok. Bear with me. I'm still new to Access.
=RecordSource.RecordCount
in the property sheet or in vba.
Click to expand...

Where exactly do I enter this expression in the property sheet?
=Int(RecordSource.RecordCount / 14) + IIf(RecordSource.RecordCount Mod14 > 0,
1, 0) + 1
Click to expand...
[quoted text clipped - 5 lines]
into account any prompted values from other text boxes on the form. You will
have to add them into the equation as well.
Click to expand...

Ok, that makes sense. But I'm assuming I need to have the above expression
done first, right? Just fyi, this is just a report I'm talking about. There
is no corresponding form; I'm prompted for the values when I open the report.
Will I still be able to use the prompt fields for the expression?

Thanks,
Mike
Click to expand...
 
=Count(*) should work in any section of your report other than that Page
Header or Page Footer.
Regarding your expression, don't return string/text values where you need to
return numbers. A quick glance at your calculation

=1+[New Prescriptions]+[Med Info Sheets]+[Provider Records]+
(IIf([Total Refills]<=14, 1, IIf([Total Refills] Between 15 And 28, 2,
IIf([Total
Refills] Between 29 And 35, 3, 4))))

I try to never hard-code values in expressions like 14, 15, 28, 29, 35.
Consider creating a small function that accepts the number of Total Refills
and returns the number.
 
Thanks Duane. I had it in the page footer. That was the problem.
I try to never hard-code values in expressions like 14, 15, 28, 29, 35.
Consider creating a small function that accepts the number of Total Refills
and returns the number.
Click to expand...

*considering* Any suggestions?
 
A simple function that you would send the totalse refill requests and return
your number would be:

Function RefillCalc(intNumRequests As Integer) As Integer
Select Case intNumRequests
Case Is <= 14
RefillCalc = 1
Case 15 To 28
RefillCalc = 2
Case 29 To 35
RefillCalc = 3
Case Else
RefillCalc = 4
End Select
End Function
 
Where would this expression be entered? It looks more like an SQL than one I
could enter directly into a text box on a report, but I don't have a field
for this value, it only shows up on the report.

Duane Hookom said:
A simple function that you would send the totalse refill requests and return
your number would be:

Function RefillCalc(intNumRequests As Integer) As Integer
Select Case intNumRequests
Case Is <= 14
RefillCalc = 1
Case 15 To 28
RefillCalc = 2
Case 29 To 35
RefillCalc = 3
Case Else
RefillCalc = 4
End Select
End Function
--
Duane Hookom
MS Access MVP

Mike said:
I have a report based on a query. In the report I'm trying to create a
text
box that will count the number of records in the query, however the
=Count(*)
expression, when used in the report, results in an error. Any help?

Also, in the same report I'd like to be able to create another text box
that
would sum a value based on Count number along with the prompted values of
several unboud text boxes, along with a constant (the fax cover page).
Specifically, the report is for refill requests, and displays 14 records
per
page. If the count is <=14, I'd like the value to be 1, if 15 through 28,
I'd like it to be 2, etc. etc. So I'm thinking the expression would go
something like this:

=1+[New Prescriptions]+[Med Info Sheets]+[Provider Records]+(IIf([Total
Refills]<=14, "1", IIf([Total Refills] Between 15 And 28, "2", IIf([Total
Refills] Between 29 And 35, "3", "4"))))

I know this doesn't work though, so any correction would be great.

thanks,
Mike
Click to expand...
Click to expand...
 
You can use this function almost anywhere you could use any other function.
Where do you want to use it? What value are you using to determine the
"intNumRequests"?
--
Duane Hookom
MS Access MVP

Mike said:
Where would this expression be entered? It looks more like an SQL than
one I
could enter directly into a text box on a report, but I don't have a field
for this value, it only shows up on the report.

Duane Hookom said:
A simple function that you would send the totalse refill requests and
return
your number would be:

Function RefillCalc(intNumRequests As Integer) As Integer
Select Case intNumRequests
Case Is <= 14
RefillCalc = 1
Case 15 To 28
RefillCalc = 2
Case 29 To 35
RefillCalc = 3
Case Else
RefillCalc = 4
End Select
End Function
--
Duane Hookom
MS Access MVP

Mike said:
I have a report based on a query. In the report I'm trying to create a
text
box that will count the number of records in the query, however the
=Count(*)
expression, when used in the report, results in an error. Any help?

Also, in the same report I'd like to be able to create another text box
that
would sum a value based on Count number along with the prompted values
of
several unboud text boxes, along with a constant (the fax cover page).
Specifically, the report is for refill requests, and displays 14
records
per
page. If the count is <=14, I'd like the value to be 1, if 15 through
28,
I'd like it to be 2, etc. etc. So I'm thinking the expression would go
something like this:

=1+[New Prescriptions]+[Med Info Sheets]+[Provider Records]+(IIf([Total
Refills]<=14, "1", IIf([Total Refills] Between 15 And 28, "2",
IIf([Total
Refills] Between 29 And 35, "3", "4"))))

I know this doesn't work though, so any correction would be great.

thanks,
Mike
Click to expand...
Click to expand...
Click to expand...
 
Back
Top