Split Sorting

[kitty77]

I run a report every month that shows me records that are due based on a due date. So, for next month, I will change my
query to be <=4/30/22. The entire report is descending on the due date.

Anyway to have the current month (April) be ascending and the rest of the report descending?

Thanks.

 

[theDBguy]

You should be able to apply multiple sorting order. Posting a sample db might help us help you figure out how to do it.

 

[kitty77]

I will have to create an example db with that data to send.

 

[kitty77]

So, I would like the report to have the current month on top (ascending) and the rest of the data ascending.
Some way to have them split if possible.

Thanks!

 

[gemma-the-husky]

The only way I could see you doing that is to have a separate column to just store the dates for this month only. (and blanks on other records) (edit - needed a higher date, not a lower date)
Then you could sort the query on the this month column ASC and the other date column DESC

Hardcoded (with US dates) (see below - I didn't actually need to do this)
iif( [recorddate] >= #4/1/2022#, [recorddate], #12/31/2099#)

I didn't expect you to have dates for May onwards, so I changed the above to include a "between" date range

you would need to play around with the it to get the 4/1/22 to pick up the first of the current month each time, but you could change this to use a function to test for a desired date range, and then you wouldn't need to edit the query every month.

Strange. I found didn't need to use #04/01/2022# (US format) - it worked with #01/04/2022# (UK format). I thought I would need a specific date to be entered in US format.

Anyway, here's your database with an additional query to manipulate the sorting. Basically in a new column, use the real dates for the subrange you want, and make every other date a particular set date to force the sorting to work as you require. You can hide the sort columns, then all you see is your sorted [duedate] values.

 

[kitty77]

Can you show me in the report? I still don't see it.

 

[gemma-the-husky]

Can you show me in the report? I still don't see it.

I gave you a new query. Set your report to use the new query, or edit your query to use the changed sorting mechanism.

 

[theDBguy]

So, I would like the report to have the current month on top (ascending) and the rest of the data ascending.
Some way to have them split if possible.

Thanks!

Hi. Hope this is close to what you want.

 

[Pat Hartman]

Getting the current month is far better than hard coding a dat that you need to remember to change every month but even more flexible is to use an unbound control on a form. When the form opens use the expression you were given to assign a default value but allow the user to enter a different date. That way you can look at April's ending value in March if you want to.

You are probably already running the query from a form anyway, right? Users NEVER, EVER get access to queries or anything else except forms and reports.