Sorting queries

[mjj4golf]

I need to sort a query depending on what option is picked. I have a label
report that uses a query. The user uses a menu to pick different options.
One option is to sort on last name, another is id. I tried an iif in the
order by property of the report, but no sort atall took place. Any thoughts
would be appreciated.

Mike

 

[Duane Hookom]

Report sorting must be done in the report design view. You can assign
different columns to your sort by using the Choose() function in the query.
For instance a form named frmRpt with an option group grpSortBy and
values/labels like:
1/Last Name
2/City
3/Zip Code
Add a column in your report's record source with an expression like:
SortBy:Choose(Forms!frmRpt!grpSortBy, [LastNameFld], [CityField],
[ZipCodeField])

In your report design, make sure you sort/group by the column SortBy.

 

[mjj4golf]

Thanks. But I tried doing this by adding another column in a query like:
Sort_it: Choose(Forms!Rpt_Menu!Sort_option, ID,Last Name,Zipcode).
When trying it, I get the following:
'The specified " [ID]" could refer to more than one table listed in the
FROM clause of your SQL stmnt'

Mike

Report sorting must be done in the report design view. You can assign
different columns to your sort by using the Choose() function in the query.
For instance a form named frmRpt with an option group grpSortBy and
values/labels like:
1/Last Name
2/City
3/Zip Code
Add a column in your report's record source with an expression like:
SortBy:Choose(Forms!frmRpt!grpSortBy, [LastNameFld], [CityField],
[ZipCodeField])

In your report design, make sure you sort/group by the column SortBy.

--
Duane Hookom
MS Access MVP

I need to sort a query depending on what option is picked. I have a label
report that uses a query. The user uses a menu to pick different options.
One option is to sort on last name, another is id. I tried an iif in the
order by property of the report, but no sort atall took place. Any
thoughts
would be appreciated.

Mike

 

[Dale Fye]

mjj4golf,

Apparantly, you are using more than one table in your query that contains an
ID field. Access will be confused until you tell it which tables ID column
you want to use, as below.

Sort_it: Choose(Forms!Report_Menu!Sort_Option, [TableName].[ID], [Last
Name], [ZipCode])

HTH
Dale

Thanks. But I tried doing this by adding another column in a query like:
Sort_it: Choose(Forms!Rpt_Menu!Sort_option, ID,Last Name,Zipcode).
When trying it, I get the following:
'The specified " [ID]" could refer to more than one table listed in the
FROM clause of your SQL stmnt'

Mike

Report sorting must be done in the report design view. You can assign
different columns to your sort by using the Choose() function in the
query.
For instance a form named frmRpt with an option group grpSortBy and
values/labels like:
1/Last Name
2/City
3/Zip Code
Add a column in your report's record source with an expression like:
SortBy:Choose(Forms!frmRpt!grpSortBy, [LastNameFld], [CityField],
[ZipCodeField])

In your report design, make sure you sort/group by the column SortBy.

--
Duane Hookom
MS Access MVP

I need to sort a query depending on what option is picked. I have a
label
report that uses a query. The user uses a menu to pick different
options.
One option is to sort on last name, another is id. I tried an iif in
the
order by property of the report, but no sort atall took place. Any
thoughts
would be appreciated.

Mike

 

[Duane Hookom]

This is a good reason why I no longer create two fields in the same database
file with the same name.

--
Duane Hookom
MS Access MVP

mjj4golf,

Apparantly, you are using more than one table in your query that contains
an ID field. Access will be confused until you tell it which tables ID
column you want to use, as below.

Sort_it: Choose(Forms!Report_Menu!Sort_Option, [TableName].[ID], [Last
Name], [ZipCode])

HTH
Dale

Thanks. But I tried doing this by adding another column in a query like:
Sort_it: Choose(Forms!Rpt_Menu!Sort_option, ID,Last Name,Zipcode).
When trying it, I get the following:
'The specified " [ID]" could refer to more than one table listed in the
FROM clause of your SQL stmnt'

Mike

Report sorting must be done in the report design view. You can assign
different columns to your sort by using the Choose() function in the
query.
For instance a form named frmRpt with an option group grpSortBy and
values/labels like:
1/Last Name
2/City
3/Zip Code
Add a column in your report's record source with an expression like:
SortBy:Choose(Forms!frmRpt!grpSortBy, [LastNameFld], [CityField],
[ZipCodeField])

In your report design, make sure you sort/group by the column SortBy.

--
Duane Hookom
MS Access MVP


I need to sort a query depending on what option is picked. I have a
label
report that uses a query. The user uses a menu to pick different
options.
One option is to sort on last name, another is id. I tried an iif in
the
order by property of the report, but no sort atall took place. Any
thoughts
would be appreciated.

Mike