Date sort?

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

I have a form that I use to make appointments. I have lots
of history and a fair amount of future appointments. I
know how to sort the form by date but I would like to sort
it to open or begin at the record that is as close to
today as possible. That way I do not have to scroll or use
find to bring that days appointments up. My question is
Can I put a line of code in my select query or is there a
better way? Below is my query in case a line is all I need.

SELECT Resource.ResourceType, Resource.ScheduleDate,
Resource.DrName
FROM Resource
ORDER BY Resource.ScheduleDate DESC;

Thanks,

Dennis
 
I have a form that I use to make appointments. I have lots
of history and a fair amount of future appointments. I
know how to sort the form by date but I would like to sort
it to open or begin at the record that is as close to
today as possible. That way I do not have to scroll or use
find to bring that days appointments up. My question is
Can I put a line of code in my select query or is there a
better way? Below is my query in case a line is all I need.

SELECT Resource.ResourceType, Resource.ScheduleDate,
Resource.DrName
FROM Resource
ORDER BY Resource.ScheduleDate DESC;

Thanks,

Dennis
Click to expand...

Don't sort the records Descending, sort them Ascending:

ORDER BY Resource.ScheduleDate

Code the Load event of the form:
DoCmd.RunCommand acCmdRecordsGoToLast

The newest ScheduleDate will be at the end of the list.
When you open the form, it will display the newest date.
 
Dennis said:
I have a form that I use to make appointments. I have lots
of history and a fair amount of future appointments. I
know how to sort the form by date but I would like to sort
it to open or begin at the record that is as close to
today as possible. That way I do not have to scroll or use
find to bring that days appointments up. My question is
Can I put a line of code in my select query or is there a
better way? Below is my query in case a line is all I need.

SELECT Resource.ResourceType, Resource.ScheduleDate,
Resource.DrName
FROM Resource
ORDER BY Resource.ScheduleDate DESC;
Click to expand...
Hi Dennis,

I could be wrong, but it sure sounds like you
wanted something like below in Form_Open
event:


Me.RecordsetClone.FindFirst "[ScheduleDate]=" & Date()
If Me.RecordsetClone.NoMatch Then
'do something else?
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If


I did not test this so you may want in another Form event
(is "clone" available on Form_Open event, etc.), plus maybe
come up with "something else" if no match. FindFirst
starts at beginning of recordset so whether you use DESC
(or not) may be important for getting what you want to do.
Also, maybe Date() needs to wrapped with date literals
Me.RecordsetClone.FindFirst "[ScheduleDate]=#" & Date() & "#"
(I don't know for sure and didn't test).
Maybe it works "better" if use ">=" instead of "="
and recordset is sorted w/o DESC.


Just another idea,

Gary Walter
 
Back
Top