Date time picker - findrecord based on value

[4004]

Where am I going wrong?

I have a form which has a record for each date on which I teach. It
has a subform which shows the classes that I teach that day. So I want
to be able to click on a calendar and jump to the selected day, so that
I can make plans for each class etc.

My form has a field TeachDayDate which is Date/Time, which comes from
an underlying table/query.

I want to be able to click on a control, such as DTPicker3, or the
Calendar control so that I can view the record for the chosen date.

I have followed the help and done the module stuff and pressed F5 so
that the ActiveX control is recognised.

All I can do is have a txtDTpicker show the date picked - but only
after I manually refresh the records from the toolbar. How can I force
a refresh/whatever from code?

I have tried using a filter criteria in the underlying queries
TeachDayDate: [txtDTPicker3] but this doesn't work.

I have tried
Set rs = Me.Recordset.Clone
rs.Findfirst "[TeachDayDate] = '" & Me![DTPicker3] & "'"
Me.Bookmark = rs.Bookmark
and that doesn't work either.

How can I use DTPicker3 in either expressions or queries so that I can
move to the record for the selected day?

[salad]

4004 wrote:

Where am I going wrong?

I have a form which has a record for each date on which I teach. It
has a subform which shows the classes that I teach that day. So I want
to be able to click on a calendar and jump to the selected day, so that
I can make plans for each class etc.

My form has a field TeachDayDate which is Date/Time, which comes from
an underlying table/query.

I want to be able to click on a control, such as DTPicker3, or the
Calendar control so that I can view the record for the chosen date.

I have followed the help and done the module stuff and pressed F5 so
that the ActiveX control is recognised.

All I can do is have a txtDTpicker show the date picked - but only
after I manually refresh the records from the toolbar. How can I force
a refresh/whatever from code?

I have tried using a filter criteria in the underlying queries
TeachDayDate: [txtDTPicker3] but this doesn't work.

I have tried
Set rs = Me.Recordset.Clone
rs.Findfirst "[TeachDayDate] = '" & Me![DTPicker3] & "'"
Me.Bookmark = rs.Bookmark
and that doesn't work either.

How can I use DTPicker3 in either expressions or queries so that I can
move to the record for the selected day?

What is the value of DTPicker3? Is it a date? Try
rs.Findfirst "[TeachDayDate] = #" & Me![DTPicker3] & "#"
it it is a date field.

Text fields are surrounded by quotes, dates by #, and numerics by nothing.

[4004]

Yes, it is a date/time field.

I have just tried your suggestion, without any luck.

My DateTimePicker is working - it sets the value in my text box
txtDTPicker. However, I cannot use this value in the form, as
described; nothing appears to happen when the code is run.

[4004]

I have just tried using the wizard to create a cmd button to open
another form where a filter is applied according to the value of
dtpicker.

frmwithDtpicker opens frmWithDates

What I see in the properties window is that the filter is attempting to
match not only the date but the time as well - hence it probably cannot
find a record that matches.

So, how do I clean up the dates/force an ignore of the time part of
the field/find a nearest match?

Suggestions gratefully recieved.

[4004]

Sorry, I should have waited a few minutes until I found a bit more of
the answer myself:

part of the answer is that DateTimePicker, as its name implies, returns
a value that is a date and a time. When I changed it for an ActiveX
calendar control I found I was able to open the second form with the
correct filter - so something is working.

However, that leaves the findrecord still doing nothing.

[salad]

4004 wrote:

Sorry, I should have waited a few minutes until I found a bit more of
the answer myself:

part of the answer is that DateTimePicker, as its name implies, returns
a value that is a date and a time. When I changed it for an ActiveX
calendar control I found I was able to open the second form with the
correct filter - so something is working.

However, that leaves the findrecord still doing nothing.

A Date field has a time of 00:00:00

A field that stores a value like Now() will, for the most part never
have a value of 00:00:00 except by luck

You could create a column in your query recordsource.
DateWOTime : Cdate(format([txtDTPicker3],"short date"))
This basically strips the time off your field. Now search for DateWOTime.

Some people can get confused on dates that store Now() data. For
example, you may want to select all records between 1/1/2006 and
1/2/2006. Then enter
Between #1/1/2006# And #1/2/2006# in the query.
Then they get surprised because only recs with for 1/1 are returned.
That's because they're asking for records between 1/1/2006 00:00:00 and
1/2/2006 00:00:00. Better to make it 1/3/2006 to select the records for
1/2/2006.

[4004]

Thanks for that. However, while the DTPicker appears to return a date
and time (no surprise there I suppose) and so will not match the field
I want, I believe that the ActiveX calendar control returns just a date
- so should not cause a problem requiring stripping off the time to
leave the date.

So, my Calendar control gives me the date, as I want it.

My txtDate which is fed by the Calendar shows me that the Calendar is
working and can be read.

However, I cannot then either use the date as a filter in my form or
use the FindRecord code (as above) to move to the first instance of a
date.

The code works for other field types in other forms.

But...having just copied it below, perhaps the problem lies in the
(Code as Integer) part? Does that mean that I will need to convert my
DateTime field in my recordset query to an Integer so that I can match
it?

Private Sub Calendar3_Updated(Code As Integer)
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.Findfirst "[Date] = #" & Me![Calendar3] & "#"

Me.Bookmark = rs.Bookmark

End Sub

[Lyle Fairfield]

Many experienced Access users do not use the Calendar Control. You are
finding out why, perhaps.

1. Is your function actually called? Try inserting
MsgBox "[Date] = #" & Me![Calendar3] & "#"
As its first line and then run it to see. If it's not actually called
see if you can use some other event of of the control or the textbox to
do what you want.

2. Is your criteria string in the correct format? The message box
should show something like:
[Date] = #1/15/2006#

Typically Access/Jet needs the date as
[Date] = #1/15/2006#
or
[Date] =#2006-01-15#.

It can also deal with a numerical value:
[Date] =38732