Order By Question

[JamesJoey]

My subform doesn't sort the way I want. It was created with much help form this forum.
To make it easier for me I attached the pertinent objects.
As you can see the subform fsubWeeklyAgenda orders by date fine, but I want to order by the Reminder field also.
You canb see this in each days list.

Any help will be appreciated,
James

 

[Gasman]

So this is your query ATM

SELECT tblReminders.ReminderDate, tblReminders.ReminderID, tblReminders.ReminderTypeID, tblReminders.ReminderTime, tblReminders.Reminder, (SELECT Count(ReminderID) +1 As HowMany FROM tblReminders AS Dupe WHERE Dupe.ReminderID> tblReminders.ReminderID AND Dupe.ReminderDate= tblReminders.ReminderDate) AS RankByDay
FROM tblReminders
WHERE (((tblReminders.ReminderDate)<Date()+7))
ORDER BY tblReminders.ReminderDate;

Add the reminder field to the query ORDER BY ?

TBH I seem to recall seeing this form before, and thinking I would prefer it sorted by time of reminder?

 

[isladogs]

Thought I recognised this
Have you tried adding Reminder as an extra sort field in the query used as record source for the subform?

EDIT: Sorting alphabetically may mean altering the RankByDay field in the query.
I can look at it later but doing that may possibly be a bit more tricky

 

[theDBguy]

Hi James. Does this help?

 

[JamesJoey]

When I sort by Reminder on my app it looks like this:

 

[Gasman]

Yes, you need to then change your conditional formatting for the date field?, so it appears in the correct place?

 

[theDBguy]

When I sort by Reminder on my app it looks like this:

View attachment 94811

Did you try the file I posted?

 

[JamesJoey]

Yes. But when I add the sorting to Reminders it looks like the above screen shot.
Gasman, change the for the date field to what?

 

[Gasman]

Look at theDBguy's upload.

 

[JamesJoey]

It seems to be ok now. Not sure why it didn't work when I applied the Reminder sort.
Also, in my upload I deleted many of the records in tblReminders and left only reminders within the next 6 or 7 days. The
tables in the upload were imported directly (not linked).
When I opened theDBguy's upload and deleted the 2 tables and reimported them but linked them.
That's when it didn't sort properly as per my image.
So, I created a new database and linked my tables and then imported theDBguy's upload.
And it worked. Not sure what happened before but all is well now.

Thanks,
James

 

[JamesJoey]

I spoke too soon.
It still isn't sorting properly.
It's putting Bu... before Ba...

 

[theDBguy]

I spoke too soon.
It still isn't sorting properly.
It's putting Bu... before Ba...

Can you post the entire data set? (Or at least enough to see all possible problems.)

 

[theDBguy]

I spoke too soon.
It still isn't sorting properly.
It's putting Bu... before Ba...

Okay, see if this one helps...

 

[JamesJoey]

Here's all the records in tblReminders. Only difference is that in my working db the tables would be linked as apposed to embedded.
The first 3 records in fsubWeekleyAgenda are scheduled for Mon Sep 27.

 

[isladogs]

Try changing the subform properties as shown as both sorting and filtering are done via the QUERY

I just added a few test records and got this. I've made the rank by day order visible for testing

If you still get incorrect sorting then, as I mentioned back in post #3, the RankByDay function will need modifying

 

[JamesJoey]

Didn't help.
I'm assuming you changed the schedule for my first 3 records on the subform from Mon to Sun?

 

[Gasman]

Is this what you are after?

 

[JamesJoey]

Yes. Exactly. How did you accomplish this?

 

[Gasman]

Did as Colin has been telling you, you need to alter the calculation for RankByDay ?

Try this as the query and make sure no sort order is on the form.

SELECT tblReminders.ReminderDate, tblReminders.ReminderID, tblReminders.ReminderTypeID, tblReminders.ReminderTime, tblReminders.Reminder, (SELECT Count(ReminderID) +1 As HowMany FROM tblReminders AS Dupe WHERE Dupe.Reminder < tblReminders.Reminder AND Dupe.ReminderDate= tblReminders.ReminderDate) AS RankByDay
FROM tblReminders
WHERE (((tblReminders.ReminderDate)<Date()+7))
ORDER BY tblReminders.ReminderDate, tblReminders.Reminder;

See if you can understand what has changed?

What was the original name of this DB?, as I seem to recall I changed the download version to be sorted by time within day, after downloading it in the original question?

 

[JamesJoey]

That seems to have done the trick.
As far as differences between the old and new, (forgive me if I don't use the proper terminology)
The WHERE clause is different.
WHERE Dupe.Reminder as opposed to WHERE Dupe.ReminderID.
AND instead of greater than/less than.
Not sure what the term for DUPE would be (Clause?).
Those are the only differences I can see without getting as head ache.

SQL was never my strong point if I have any.

But seems ok now.