count function troubleshooting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 17 May 2006 15:23 GMT

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

Reply to this Message

OfficeDev18 - 17 May 2006 17:36 GMT

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(*)
[quoted text clipped - 16 lines]
>thanks,
>Mike

Signature

Sam

Reply to this Message

Mike - 17 May 2006 18:47 GMT

Ok. Bear with me. I'm still new to Access.

> =RecordSource.RecordCount
> in the property sheet or in vba.

Where exactly do I enter this expression in the property sheet?

> =Int(RecordSource.RecordCount / 14) + IIf(RecordSource.RecordCount Mod14 > 0,
> 1, 0) + 1
[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.

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

Reply to this Message

OfficeDev18 - 18 May 2006 17:49 GMT

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.
>
[quoted text clipped - 16 lines]
>Thanks,
>Mike

Signature

Sam

Reply to this Message

Duane Hookom - 18 May 2006 22:35 GMT

=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.

Signature

Duane Hookom
MS Access MVP

>I have a report based on a query. In the report I'm trying to create a
>text
[quoted text clipped - 20 lines]
> thanks,
> Mike

Reply to this Message

Mike - 22 May 2006 14:10 GMT

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.

*considering* Any suggestions?

Reply to this Message

Duane Hookom - 22 May 2006 14:49 GMT

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

Signature

Duane Hookom
MS Access MVP

>I have a report based on a query. In the report I'm trying to create a
>text
[quoted text clipped - 20 lines]
> thanks,
> Mike

Reply to this Message

Mike - 22 May 2006 17:09 GMT

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.

> A simple function that you would send the totalse refill requests and return
> your number would be:
[quoted text clipped - 35 lines]
> > thanks,
> > Mike

Reply to this Message

Duane Hookom - 22 May 2006 21:09 GMT

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"?

Signature

Duane Hookom
MS Access MVP

> Where would this expression be entered? It looks more like an SQL than
> one I
[quoted text clipped - 45 lines]
>> > thanks,
>> > Mike

Reply to this Message