Sorting/Grouping report on column(1) of combo box

Jimmy

Is there a way to sort/group a report based on the second column of a combo
box, i.e. the text associated with the primary key number?

Jan 20 '07 #1

Jimmy

Sorry, posted to quickly. I'm doing this through the sorting/grouping window
and not through code. I don't know if it is possible to group and sort a
report through code or not but I can try if it is an option.

"Jimmy" <do**@email.mewrote in message
news:Qe*********************@fe08.news.easynews.co m...

Is there a way to sort/group a report based on the second column of a
combo box, i.e. the text associated with the primary key number?

Jan 20 '07 #2

Marshall Barton

Jimmy wrote:

>Is there a way to sort/group a report based on the second column of a combo
box, i.e. the text associated with the primary key number?

Sorting and grouping is normally done using a field in the
report's record source table/query.

Sorting and grouping can be done on an expression based on
fields in the record source, not controls on a form or in
the report. The expression can include the use of user
defined functions, which provides a hook into using a VBA
procedure for the values, but this is rarely necessary.

It is far more straightforward to create a record source
query for the report that joins the combo box's row source
table and provides the desired field directly to the report.

Where the #%!& is this combo box? If it is a Lookup field
in a table. Change it back to a text box so you can see
what's really going on.

--
Marsh

Jan 20 '07 #3

Bob Quintal

"Jimmy" <do**@email.mewrote in
news:Qe*********************@fe08.news.easynews.co m:

Is there a way to sort/group a report based on the second
column of a combo box, i.e. the text associated with the
primary key number?

very easy.
Open report in design view. Open (if not already) the properties
popup.
Select the recordsource property from the data tab.
Click the elipsis (...) button. If the existing recordset is a
table you will be prompted "do you want to create a query based
on the table?". Say yes, or if the recordset is already a
query,.the query design tool will open.

Add the table which is the source for the combobox.
Create a join by dragging the cursor from the primary key of the
lookup table to the relevand foreign key in the other table if
it didn't show automatically.
Double-click the field name for your text to put it in the
fields section of the query builder.
Set the sort to ascending. Drag this column to the left so it's
the first column in the grid.
Close the query builder.

Click on the sorting/grouping icon in the Report toolbar.
Select the column you just added in the top combobox.
Turn on group header and/or footer as required.
Close the Sorting and Grouping popup.
Save the form.

It's actually faster to do than to describe.


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Jan 20 '07 #4