Goto a date

[Hagley]

I am a teacher trying to build a db to record lesson details. I want to open a form to goto now() and be able to move back and forwards through dates/lessons. The best I have done so far is to query for >=now(), but this obviously does not allow me to see previous days or lessons. I have tried a macro for goto, but this will only allow me to select a record number not a date. I would imagine this is a common requirement of a database, but the solution is evading me. Just a teacher. I thank in advanoe anyone for their time and help.

 

[Jon K]

I have attached a sample database, which contains an Orders table and an Order Details table adapted from the Northwind sample.

You can open the Orders main form and click on the "Goto order date" unbound list box to go to the first record of the selected order date. The code used is in the After Update event of the list box.

Note the Record Source property of the main form. The orders are sorted in ascending order of OrderDate.


Edited:
Reloaded a version that will go to today's date or the nearest date when the form opens.
.

 

[boblarson]

I am a teacher trying to build a db to record lesson details. I want to open a form to goto now() and be able to move back and forwards through dates/lessons. The best I have done so far is to query for >=now(), but this obviously does not allow me to see previous days or lessons. I have tried a macro for goto, but this will only allow me to select a record number not a date. I would imagine this is a common requirement of a database, but the solution is evading me. Just a teacher. I thank in advanoe anyone for their time and help.

JonK has the stuff for you, I'm sure but I would like to submit some instruction for a teacher as I like to pass along knowledge as well.

Normally, you would want to use Date() instead of Now() because Now() includes time which becomes more specific when trying to do queries. DATE however, does not depend on a time being present.

 

[Hagley]

Thought I had it.

Thank you for your effort. I have tried and failed, but feel the solution is within my grasp. First I do actaully need to return a date and time so I can go straight to the lesson I am currently in. The table has the date and time of the lesson recorded. How does this change the code line?

set rs = me.Recordset.Clone
re.FindFirst "[date] >= DateValue('" & Date & "')"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

 

[EMP]

Try CDate and Now. They include date and time.

rs.FindFirst "[Date] >= CDate('" & Now & "')"


If it still doesn't work, try using another name for the date/time field because Date is a function name.

^

 

[Hagley]

Works like a charm. Seven science teachers will be in your debt come Monday morning. What is CDate?
Anyway, thank you again.

 

[EMP]

Glad it works for you. The kudos goes to Jon K who, without info about your table structure, had to stab in the dark.

CDate() is a function that converts a string to date/time data type.

In the line -
rs.FindFirst "[Date] >= CDate('" & Now & "')"

though Now() returns date/time data type, the FindFirst method needs the criteria in a string.

^