Limit Number of Rows Returned

[Nigel]

I have a report that I wish to limit the number of records returned. I have 3
groupings, Junior, Intermediate and Senior and I want to limit the number of
records to the top 2 people in each group so somehow in the query I have to
rank the top 2 people in each group and then exclude everybody else

Any suggestions appreciated

 

[Duane Hookom]

You can just use the report sorting and grouping to group by the appropriate
field and sort by the appropriate field. Then add a text box to the detail
section:
Name: txtCount
Control Source: =1
Running Sum: Over Group

Then add code to the On Format event of the detail section:
Cancel = Me.txtCount > 2

 

[Dana]

I'm having a similar issue. I have sorted/grouped and have the text box but
when I add the code to the detail On Format section, it tells me that there
isn't a macro called "Cancel....."
What I'm trying to do is create a report that shows the volume of plans by
dealer but for each dealer I only want to show the top 10 records. The
report is set up but currently all the records by dealer show up.

Any help would be GREATLY appreciated!

 

[Duane Hookom]

You must paste the code into the report's module. You don't paste the code
into the event property. Let us know if you have trouble find the
code/module window.

 

[Dana]

Hi Duane...my project got sidetracked for a few months but now I'm back on
it. I found the code/module window and put in the Cancel = Me.txtCount >10
but I'm still receiving an error. I have the text box in the detail section
called 'txtCount' and ControlSource = 1. Does the 'Me' in the code refer to
the query name or field or both? I'm so green when it comes to SQL.
Thanks, Dana

 

[Duane Hookom]

When you look at the properties of the detail section of the report, does
the On Format property show [Event Procedurrrrrre]?

 

[Dana]

Yes it does. Then I clicked on the '...' button to open the module and
pasted in Cancel = Me.txtCount >10.

Dana

When you look at the properties of the detail section of the report, does
the On Format property show [Event Procedurrrrrre]?

--
Duane Hookom
MS Access MVP

Hi Duane...my project got sidetracked for a few months but now I'm back on
it. I found the code/module window and put in the Cancel = Me.txtCount
but I'm still receiving an error. I have the text box in the detail
section
called 'txtCount' and ControlSource = 1. Does the 'Me' in the code refer
to
the query name or field or both? I'm so green when it comes to SQL.
Thanks, Dana

 

[Duane Hookom]

"still receiving an error" what is the error message? What line of code
creates the error.

--
Duane Hookom
MS Access MVP

Yes it does. Then I clicked on the '...' button to open the module and
pasted in Cancel = Me.txtCount >10.

Dana

When you look at the properties of the detail section of the report, does
the On Format property show [Event Procedurrrrrre]?

--
Duane Hookom
MS Access MVP

Hi Duane...my project got sidetracked for a few months but now I'm back
on
it. I found the code/module window and put in the Cancel = Me.txtCount
10
but I'm still receiving an error. I have the text box in the detail
section
called 'txtCount' and ControlSource = 1. Does the 'Me' in the code
refer
to
the query name or field or both? I'm so green when it comes to SQL.
Thanks, Dana

:

You must paste the code into the report's module. You don't paste the
code
into the event property. Let us know if you have trouble find the
code/module window.
--
Duane Hookom
MS Access MVP

I'm having a similar issue. I have sorted/grouped and have the text
box
but
when I add the code to the detail On Format section, it tells me
that
there
isn't a macro called "Cancel....."
What I'm trying to do is create a report that shows the volume of
plans
by
dealer but for each dealer I only want to show the top 10 records.
The
report is set up but currently all the records by dealer show up.

Any help would be GREATLY appreciated!



:

You can just use the report sorting and grouping to group by the
appropriate
field and sort by the appropriate field. Then add a text box to the
detail
section:
Name: txtCount
Control Source: =1
Running Sum: Over Group

Then add code to the On Format event of the detail section:
Cancel = Me.txtCount > 2

--
Duane Hookom
MS Access MVP

I have a report that I wish to limit the number of records
returned.
I
have
3
groupings, Junior, Intermediate and Senior and I want to limit
the
number
of
records to the top 2 people in each group so somehow in the query
I
have
to
rank the top 2 people in each group and then exclude everybody
else

Any suggestions appreciated

 

[Dana]

Sorry Duane. Here is the error message and the code below:

The Expression On Format you entered as the event property setting produced
the following error: Invalid outside procedure.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event or macro.

Code:

OPTION Compare Database
End Sub

Provate Sub Detail_Format (Cancel as Integer, FormatCount As Integer)
Cancel = Me.txtcount > 10
End Sub

Dana

"still receiving an error" what is the error message? What line of code
creates the error.

--
Duane Hookom
MS Access MVP

Yes it does. Then I clicked on the '...' button to open the module and
pasted in Cancel = Me.txtCount >10.

Dana

When you look at the properties of the detail section of the report, does
the On Format property show [Event Procedurrrrrre]?

--
Duane Hookom
MS Access MVP

Hi Duane...my project got sidetracked for a few months but now I'm back
on
it. I found the code/module window and put in the Cancel = Me.txtCount
10
but I'm still receiving an error. I have the text box in the detail
section
called 'txtCount' and ControlSource = 1. Does the 'Me' in the code
refer
to
the query name or field or both? I'm so green when it comes to SQL.
Thanks, Dana

:

You must paste the code into the report's module. You don't paste the
code
into the event property. Let us know if you have trouble find the
code/module window.
--
Duane Hookom
MS Access MVP

I'm having a similar issue. I have sorted/grouped and have the text
box
but
when I add the code to the detail On Format section, it tells me
that
there
isn't a macro called "Cancel....."
What I'm trying to do is create a report that shows the volume of
plans
by
dealer but for each dealer I only want to show the top 10 records.
The
report is set up but currently all the records by dealer show up.

Any help would be GREATLY appreciated!



:

You can just use the report sorting and grouping to group by the
appropriate
field and sort by the appropriate field. Then add a text box to the
detail
section:
Name: txtCount
Control Source: =1
Running Sum: Over Group

Then add code to the On Format event of the detail section:
Cancel = Me.txtCount > 2

--
Duane Hookom
MS Access MVP

I have a report that I wish to limit the number of records
returned.
I
have
3
groupings, Junior, Intermediate and Senior and I want to limit
the
number
of
records to the top 2 people in each group so somehow in the query
I
have
to
rank the top 2 people in each group and then exclude everybody
else

Any suggestions appreciated

 

[Duane Hookom]

There is no "Provate". Did you try to compile your code?

--
Duane Hookom
MS Access MVP

Sorry Duane. Here is the error message and the code below:

The Expression On Format you entered as the event property setting
produced
the following error: Invalid outside procedure.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event or macro.

Code:

OPTION Compare Database
End Sub

Provate Sub Detail_Format (Cancel as Integer, FormatCount As Integer)
Cancel = Me.txtcount > 10
End Sub

Dana

"still receiving an error" what is the error message? What line of code
creates the error.

--
Duane Hookom
MS Access MVP

Yes it does. Then I clicked on the '...' button to open the module and
pasted in Cancel = Me.txtCount >10.

Dana

:

When you look at the properties of the detail section of the report,
does
the On Format property show [Event Procedurrrrrre]?

--
Duane Hookom
MS Access MVP

Hi Duane...my project got sidetracked for a few months but now I'm
back
on
it. I found the code/module window and put in the Cancel =
Me.txtCount
10
but I'm still receiving an error. I have the text box in the detail
section
called 'txtCount' and ControlSource = 1. Does the 'Me' in the code
refer
to
the query name or field or both? I'm so green when it comes to SQL.
Thanks, Dana

:

You must paste the code into the report's module. You don't paste
the
code
into the event property. Let us know if you have trouble find the
code/module window.
--
Duane Hookom
MS Access MVP

I'm having a similar issue. I have sorted/grouped and have the
text
box
but
when I add the code to the detail On Format section, it tells me
that
there
isn't a macro called "Cancel....."
What I'm trying to do is create a report that shows the volume of
plans
by
dealer but for each dealer I only want to show the top 10
records.
The
report is set up but currently all the records by dealer show up.

Any help would be GREATLY appreciated!



:

You can just use the report sorting and grouping to group by the
appropriate
field and sort by the appropriate field. Then add a text box to
the
detail
section:
Name: txtCount
Control Source: =1
Running Sum: Over Group

Then add code to the On Format event of the detail section:
Cancel = Me.txtCount > 2

--
Duane Hookom
MS Access MVP

I have a report that I wish to limit the number of records
returned.
I
have
3
groupings, Junior, Intermediate and Senior and I want to limit
the
number
of
records to the top 2 people in each group so somehow in the
query
I
have
to
rank the top 2 people in each group and then exclude everybody
else

Any suggestions appreciated

 

[Dana]

I'm not sure what that means. Sorry!

There is no "Provate". Did you try to compile your code?

--
Duane Hookom
MS Access MVP

Sorry Duane. Here is the error message and the code below:

The Expression On Format you entered as the event property setting
produced
the following error: Invalid outside procedure.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event or macro.

Code:

OPTION Compare Database
End Sub

Provate Sub Detail_Format (Cancel as Integer, FormatCount As Integer)
Cancel = Me.txtcount > 10
End Sub

Dana

"still receiving an error" what is the error message? What line of code
creates the error.

--
Duane Hookom
MS Access MVP

Yes it does. Then I clicked on the '...' button to open the module and
pasted in Cancel = Me.txtCount >10.

Dana

:

When you look at the properties of the detail section of the report,
does
the On Format property show [Event Procedurrrrrre]?

--
Duane Hookom
MS Access MVP

Hi Duane...my project got sidetracked for a few months but now I'm
back
on
it. I found the code/module window and put in the Cancel =
Me.txtCount
10
but I'm still receiving an error. I have the text box in the detail
section
called 'txtCount' and ControlSource = 1. Does the 'Me' in the code
refer
to
the query name or field or both? I'm so green when it comes to SQL.
Thanks, Dana

:

You must paste the code into the report's module. You don't paste
the
code
into the event property. Let us know if you have trouble find the
code/module window.
--
Duane Hookom
MS Access MVP

I'm having a similar issue. I have sorted/grouped and have the
text
box
but
when I add the code to the detail On Format section, it tells me
that
there
isn't a macro called "Cancel....."
What I'm trying to do is create a report that shows the volume of
plans
by
dealer but for each dealer I only want to show the top 10
records.
The
report is set up but currently all the records by dealer show up.

Any help would be GREATLY appreciated!



:

You can just use the report sorting and grouping to group by the
appropriate
field and sort by the appropriate field. Then add a text box to
the
detail
section:
Name: txtCount
Control Source: =1
Running Sum: Over Group

Then add code to the On Format event of the detail section:
Cancel = Me.txtCount > 2

--
Duane Hookom
MS Access MVP

I have a report that I wish to limit the number of records
returned.
I
have
3
groupings, Junior, Intermediate and Senior and I want to limit
the
number
of
records to the top 2 people in each group so somehow in the
query
I
have
to
rank the top 2 people in each group and then exclude everybody
else

Any suggestions appreciated

 

[Duane Hookom]

If you actually copied and pasted your code then the following would be a
serious issue:
<QUOTE>
OPTION Compare Database
End Sub

Provate Sub Detail_Format (Cancel as Integer, FormatCount As Integer)
Cancel = Me.txtcount > 10
End Sub
</QUOTE>

You have an "End Sub" without a beginning "Sub". Your "Provate" would need
to be changed to "Private".

If you don't know how to compile your code, check the article at
http://www.hookom.net/access/Articles.htm.

--
Duane Hookom
MS Access MVP

I'm not sure what that means. Sorry!

There is no "Provate". Did you try to compile your code?

--
Duane Hookom
MS Access MVP

Sorry Duane. Here is the error message and the code below:

The Expression On Format you entered as the event property setting
produced
the following error: Invalid outside procedure.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event or macro.

Code:

OPTION Compare Database
End Sub

Provate Sub Detail_Format (Cancel as Integer, FormatCount As Integer)
Cancel = Me.txtcount > 10
End Sub

Dana

:

"still receiving an error" what is the error message? What line of
code
creates the error.

--
Duane Hookom
MS Access MVP

Yes it does. Then I clicked on the '...' button to open the module
and
pasted in Cancel = Me.txtCount >10.

Dana

:

When you look at the properties of the detail section of the
report,
does
the On Format property show [Event Procedurrrrrre]?

--
Duane Hookom
MS Access MVP

Hi Duane...my project got sidetracked for a few months but now
I'm
back
on
it. I found the code/module window and put in the Cancel =
Me.txtCount
10
but I'm still receiving an error. I have the text box in the
detail
section
called 'txtCount' and ControlSource = 1. Does the 'Me' in the
code
refer
to
the query name or field or both? I'm so green when it comes to
SQL.
Thanks, Dana

:

You must paste the code into the report's module. You don't
paste
the
code
into the event property. Let us know if you have trouble find
the
code/module window.
--
Duane Hookom
MS Access MVP

I'm having a similar issue. I have sorted/grouped and have
the
text
box
but
when I add the code to the detail On Format section, it tells
me
that
there
isn't a macro called "Cancel....."
What I'm trying to do is create a report that shows the volume
of
plans
by
dealer but for each dealer I only want to show the top 10
records.
The
report is set up but currently all the records by dealer show
up.

Any help would be GREATLY appreciated!



:

You can just use the report sorting and grouping to group by
the
appropriate
field and sort by the appropriate field. Then add a text box
to
the
detail
section:
Name: txtCount
Control Source: =1
Running Sum: Over Group

Then add code to the On Format event of the detail section:
Cancel = Me.txtCount > 2

--
Duane Hookom
MS Access MVP

I have a report that I wish to limit the number of records
returned.
I
have
3
groupings, Junior, Intermediate and Senior and I want to
limit
the
number
of
records to the top 2 people in each group so somehow in the
query
I
have
to
rank the top 2 people in each group and then exclude
everybody
else

Any suggestions appreciated

 

[Dana]

Wow Duane!!! After all this time it FINALLY works! I read your article and
changed the declaration as stated, compiled and it worked! You rock! Thank
You so much!!!

Dana

If you actually copied and pasted your code then the following would be a
serious issue:
<QUOTE>
OPTION Compare Database
End Sub

Provate Sub Detail_Format (Cancel as Integer, FormatCount As Integer)
Cancel = Me.txtcount > 10
End Sub
</QUOTE>

You have an "End Sub" without a beginning "Sub". Your "Provate" would need
to be changed to "Private".

If you don't know how to compile your code, check the article at
http://www.hookom.net/access/Articles.htm.

--
Duane Hookom
MS Access MVP

I'm not sure what that means. Sorry!

There is no "Provate". Did you try to compile your code?

--
Duane Hookom
MS Access MVP

Sorry Duane. Here is the error message and the code below:

The Expression On Format you entered as the event property setting
produced
the following error: Invalid outside procedure.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event or macro.

Code:

OPTION Compare Database
End Sub

Provate Sub Detail_Format (Cancel as Integer, FormatCount As Integer)
Cancel = Me.txtcount > 10
End Sub

Dana

:

"still receiving an error" what is the error message? What line of
code
creates the error.

--
Duane Hookom
MS Access MVP

Yes it does. Then I clicked on the '...' button to open the module
and
pasted in Cancel = Me.txtCount >10.

Dana

:

When you look at the properties of the detail section of the
report,
does
the On Format property show [Event Procedurrrrrre]?

--
Duane Hookom
MS Access MVP

Hi Duane...my project got sidetracked for a few months but now
I'm
back
on
it. I found the code/module window and put in the Cancel =
Me.txtCount
10
but I'm still receiving an error. I have the text box in the
detail
section
called 'txtCount' and ControlSource = 1. Does the 'Me' in the
code
refer
to
the query name or field or both? I'm so green when it comes to
SQL.
Thanks, Dana

:

You must paste the code into the report's module. You don't
paste
the
code
into the event property. Let us know if you have trouble find
the
code/module window.
--
Duane Hookom
MS Access MVP

I'm having a similar issue. I have sorted/grouped and have
the
text
box
but
when I add the code to the detail On Format section, it tells
me
that
there
isn't a macro called "Cancel....."
What I'm trying to do is create a report that shows the volume
of
plans
by
dealer but for each dealer I only want to show the top 10
records.
The
report is set up but currently all the records by dealer show
up.

Any help would be GREATLY appreciated!



:

You can just use the report sorting and grouping to group by
the
appropriate
field and sort by the appropriate field. Then add a text box
to
the
detail
section:
Name: txtCount
Control Source: =1
Running Sum: Over Group

Then add code to the On Format event of the detail section:
Cancel = Me.txtCount > 2

--
Duane Hookom
MS Access MVP

I have a report that I wish to limit the number of records
returned.
I
have
3
groupings, Junior, Intermediate and Senior and I want to
limit
the
number
of
records to the top 2 people in each group so somehow in the
query
I
have
to
rank the top 2 people in each group and then exclude
everybody
else

Any suggestions appreciated