Prompt in Report Header cancels query sort (1 Viewer)

JudyHNM

Registered User.
Local time
Today, 04:37
Joined
Oct 5, 2006
Messages
37
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.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 03:37
Joined
Oct 29, 2018
Messages
19,134
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

Passionate Learner
Local time
Today, 03:37
Joined
Sep 22, 2014
Messages
1,140
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

Registered User.
Local time
Today, 04:37
Joined
Oct 5, 2006
Messages
37
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’m here to help
Staff member
Local time
Today, 03:37
Joined
Oct 29, 2018
Messages
19,134
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

Registered User.
Local time
Today, 04:37
Joined
Oct 5, 2006
Messages
37
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

 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 06:37
Joined
Jan 23, 2006
Messages
14,425
??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

Registered User.
Local time
Today, 04:37
Joined
Oct 5, 2006
Messages
37
I have included a sample of my database. The query and report in question are:
1639854400270.png

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.
 

Attachments

  • FeltenVillage 2017 6.0 - 11-23-21 - Sample.accdb
    896 KB · Views: 226

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:37
Joined
Feb 19, 2002
Messages
36,837
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.
 

Users who are viewing this thread

Top Bottom