Prompt in Report Header cancels query sort (1 Viewer)

[JudyHNM]

I have a query that I have created for a table that will sort text data, including values that look like numbers into a proper sort, i.e., 300, 385 427, 3085,, etc. rather than the default sort. I have two questions about creating a report using this query.
1. I created a report that has a prompt in the report header. The prompt is for data that is not in the underlying table and is just used for extra information. I created a text field for the prompt. When I run it, the prompt works fine, but it is overriding the sort in the query. If I put the prompt in the page header, my query sort works fine. Why is this happening?
2. Using the same query, I want to create a report that groups the data on the sorted field that I described above. However, when grouping is set in an Access report, it automatically sorts the data which overrides my query sort; so, the results are in the wrong order. Is there any way around this?

Thanks Judy

Update: I have uploaded a sample of my database in a separate message in this thread.

 

[theDBguy]

Hi. Not sure I understand your question, but reports have their own sorting facility. It ignores the sort order from the query. So, if you need to sort the report, you will have to use its own sorting feature.

 

[oleronesoftwares]

The prompt is for data that is not in the underlying table and is just used for extra information. I created a text field for the prompt.

You can use a dlookup function for this aspect., though better clarification like pictures will be appreciated.

Using the same query, I want to create a report that groups the data on the sorted field that I described above. However, when grouping is set in an Access report, it automatically sorts the data which overrides my query sort; so, the results are in the wrong order. Is there any way around this?

Can you try limiting the grouping to the query, then create the report using the query as record source

 

[JudyHNM]

Hi. Not sure I understand your question, but reports have their own sorting facility. It ignores the sort order from the query. So, if you need to sort the report, you will have to use its own sorting feature.

I have a lot of reports that use the sort from the query and work just fine. It just doesn't work if I put a prompt in the Report Header or Report Footer. The default sorts from the report don't work with my datat.

 

[theDBguy]

I have a lot of reports that use the sort from the query and work just fine. It just doesn't work if I put a prompt in the Report Header or Report Footer. The default sorts from the report don't work with my datat.

To help us understand your situation, would you mind posting a sample copy of your db with test data?

 

[JudyHNM]

You can use a dlookup function for this aspect., though better clarification like pictures will be appreciated.


Can you try limiting the grouping to the query, then create the report using the query as record source

 

[jdraw]

??The info theDBGuy gave is pretty standard info. I'm surprised lots of your reports work (and sort) just fine without use of the report sorting facility.

The default sorts from the report don't work with my datat.

What makes your data special? Some ample data or copy of the database would be helpful.

 

[JudyHNM]

I have included a sample of my database. The query and report in question are:

The sort is defined in the query in the last 3 fields. As you can see the report sorts the data properly, and the data is grouped accurately on the DMC num. How do I suppress the duplicate DMC number so it looks like a grouped report that Access would display? As stated above, I can't use the Report group/sort as it then ignores the sort defined in my query. I hope this makes more sense now.

 

[Pat Hartman]

I have a lot of reports that use the sort from the query and work just fine.

You've been lucky. If you want to sort a report - and every report should be sorted - then you MUST use the report's sorting and grouping options, you cannot put the sort in the RecordSource query.

If you have special calculations to make the text fields sort properly, do the calculations in the query BUT, in the report, choose the calculated fields for sorting and grouping.