Open form 'close to' today

M

Maarkr

My db is sorted by date desc, but the db is getting bigger and the date range
is increasing from 2005-2011, so when the form (single form) is opened, I get
records from 2011, and have to scroll thru many records to view some recent
records. I can code the form to find 'today', but there may be a couple of
weeks between some records, so the find record will fail looking for 'today'
in many cases. So I was curious if anyone used a 'find recordset' sub to
open the form to a date that is CLOSE to today (without looping thru date-1,
date-2 etc).
I thought finding 'this month', but you may still have to scroll thru many
records to get to the end of the month. I may add a couple of dropdowns to
jump quickly to a specific mo & yr. I just want the form to open to a date
nearest to today.
 
B

Beetle

You say you have records through 2011, so when you say you want a
record "close to today" (assuming there is no record for todays date)
do you mean prior to today, after today, or some range of dates?
 
M

Maarkr

prior to, i guess, I can always modify the code depending on what people
want, but if i just get close to today, it will be better than always opening
the form with a 2011 date.
 
B

Beetle

One option would be to filter the recordset in the open event of your
form using code like the following;

Private Sub Form_Open (Cancel As Integer)

Dim strFilter As String
strFilter = "[YourDateField]=#" & DMax("[YourDateField]", "[YourTable]", _
& "[YourDateField] <=#" & Date & "#") & "#"
Me.Filter = strFilter
Me.FilterOn = True

End

This would return one record form your table (the max record that is
either equal to, or less than, todays date)

You could then have a command button that would show all records again
by turning off the filter;

Private Sub cmdShowAll_Click()

Me.FilterOn = False

End Sub

You may also want an unbound text box where the users could enter a
date to search for. The code might look like;

Private Sub txtSearch_AfterUpdate()

Me.FilterOn = False

With Me.RecordsetClone
FindFirst "[YourDateField]=#" & Me.txtSearch & "#"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
MsgBox "Date Not Found"
End If
End With

End Sub

Now, having said all that, the above may not necessarily be the *best*
approach if you have a multiuser application and are dealing with a large
recordset. In that case it may be slower than you like because you are still
pulling the entire record set (which is usually not desireable with large sets
of records) and just filtering it. If performance is not acceptable, you may
want to explore other options using SQL to only pull the records that
are requested each time.
 
D

Douglas J. Steele

<picky>
Some users have their Short Date format set to a format that Access won't
recognize correctly in a Where clause.

To be usable by all users, it's better to use

Private Sub Form_Open (Cancel As Integer)

Dim strFilter As String
strFilter = "[YourDateField]=" & Format(DMax("[YourDateField]", _
"[YourTable]", "[YourDateField] <=" & _
Format(Date, "\#yyyy\-mm\-dd\#")), "\#yyyy\-mm\-dd\#")
Me.Filter = strFilter
Me.FilterOn = True

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Beetle said:
One option would be to filter the recordset in the open event of your
form using code like the following;

Private Sub Form_Open (Cancel As Integer)

Dim strFilter As String
strFilter = "[YourDateField]=#" & DMax("[YourDateField]", "[YourTable]",
_
& "[YourDateField] <=#" & Date & "#") & "#"
Me.Filter = strFilter
Me.FilterOn = True

End

This would return one record form your table (the max record that is
either equal to, or less than, todays date)

You could then have a command button that would show all records again
by turning off the filter;

Private Sub cmdShowAll_Click()

Me.FilterOn = False

End Sub

You may also want an unbound text box where the users could enter a
date to search for. The code might look like;

Private Sub txtSearch_AfterUpdate()

Me.FilterOn = False

With Me.RecordsetClone
FindFirst "[YourDateField]=#" & Me.txtSearch & "#"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
MsgBox "Date Not Found"
End If
End With

End Sub

Now, having said all that, the above may not necessarily be the *best*
approach if you have a multiuser application and are dealing with a large
recordset. In that case it may be slower than you like because you are
still
pulling the entire record set (which is usually not desireable with large
sets
of records) and just filtering it. If performance is not acceptable, you
may
want to explore other options using SQL to only pull the records that
are requested each time.

--
_________

Sean Bailey


Maarkr said:
prior to, i guess, I can always modify the code depending on what people
want, but if i just get close to today, it will be better than always
opening
the form with a 2011 date.
Click to expand...
Click to expand...
 
B

Beetle

Thanks for the tip Doug. This would apply to (guessing) European date
format?

Could the same problem potentially happen with;

.FindFirst "[YourDateField]=#" & Me!txtSearch & "#"

or not, because the Date function is not involved?
--
_________

Sean Bailey


Douglas J. Steele said:
<picky>
Some users have their Short Date format set to a format that Access won't
recognize correctly in a Where clause.

To be usable by all users, it's better to use

Private Sub Form_Open (Cancel As Integer)

Dim strFilter As String
strFilter = "[YourDateField]=" & Format(DMax("[YourDateField]", _
"[YourTable]", "[YourDateField] <=" & _
Format(Date, "\#yyyy\-mm\-dd\#")), "\#yyyy\-mm\-dd\#")
Me.Filter = strFilter
Me.FilterOn = True

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Beetle said:
One option would be to filter the recordset in the open event of your
form using code like the following;

Private Sub Form_Open (Cancel As Integer)

Dim strFilter As String
strFilter = "[YourDateField]=#" & DMax("[YourDateField]", "[YourTable]",
_
& "[YourDateField] <=#" & Date & "#") & "#"
Me.Filter = strFilter
Me.FilterOn = True

End

This would return one record form your table (the max record that is
either equal to, or less than, todays date)

You could then have a command button that would show all records again
by turning off the filter;

Private Sub cmdShowAll_Click()

Me.FilterOn = False

End Sub

You may also want an unbound text box where the users could enter a
date to search for. The code might look like;

Private Sub txtSearch_AfterUpdate()

Me.FilterOn = False

With Me.RecordsetClone
FindFirst "[YourDateField]=#" & Me.txtSearch & "#"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
MsgBox "Date Not Found"
End If
End With

End Sub

Now, having said all that, the above may not necessarily be the *best*
approach if you have a multiuser application and are dealing with a large
recordset. In that case it may be slower than you like because you are
still
pulling the entire record set (which is usually not desireable with large
sets
of records) and just filtering it. If performance is not acceptable, you
may
want to explore other options using SQL to only pull the records that
are requested each time.

--
_________

Sean Bailey


Maarkr said:
prior to, i guess, I can always modify the code depending on what people
want, but if i just get close to today, it will be better than always
opening
the form with a 2011 date.

:

You say you have records through 2011, so when you say you want a
record "close to today" (assuming there is no record for todays date)
do you mean prior to today, after today, or some range of dates?
--
_________

Sean Bailey


:

My db is sorted by date desc, but the db is getting bigger and the
date range
is increasing from 2005-2011, so when the form (single form) is
opened, I get
records from 2011, and have to scroll thru many records to view some
recent
records. I can code the form to find 'today', but there may be a
couple of
weeks between some records, so the find record will fail looking for
'today'
in many cases. So I was curious if anyone used a 'find recordset'
sub to
open the form to a date that is CLOSE to today (without looping thru
date-1,
date-2 etc).
I thought finding 'this month', but you may still have to scroll thru
many
records to get to the end of the month. I may add a couple of
dropdowns to
jump quickly to a specific mo & yr. I just want the form to open to
a date
nearest to today.
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

The problem is pretty much restricted to users who have dd/mm/yyyy as their
short date format, but I wouldn't say that's a European issue. Most of the
British Commonwealth countries use that format. (I can't remember whether
using a non-standard date separator, such as dots or spaces, causes problems
as well)

The problem with your other example is that should the user expect to be
able to put the date in dd/mm/yyyy format, that's what's going to end up in
the Where clause, so it will fail for the first 12 days of each month (i.e.:
they'll put 05/01/2008, meaning 05 Jan, 2008, and Access will ALWAYS treat
it as 01 May, 2008, regardless of their Regional Settings. However, Access
is smart enough to know that 13/01/2008 isn't valid as mm/dd/yyyy, so will
ALWAYS treat that as 31 Jan, 2008)

The CDate function respects the user's Short Date setting, so you should
use:

..FindFirst "[YourDateField]=" & Format(CDate(Me!txtSearch),
"\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Beetle said:
Thanks for the tip Doug. This would apply to (guessing) European date
format?

Could the same problem potentially happen with;

.FindFirst "[YourDateField]=#" & Me!txtSearch & "#"

or not, because the Date function is not involved?
--
_________

Sean Bailey


Douglas J. Steele said:
<picky>
Some users have their Short Date format set to a format that Access won't
recognize correctly in a Where clause.

To be usable by all users, it's better to use

Private Sub Form_Open (Cancel As Integer)

Dim strFilter As String
strFilter = "[YourDateField]=" & Format(DMax("[YourDateField]", _
"[YourTable]", "[YourDateField] <=" & _
Format(Date, "\#yyyy\-mm\-dd\#")), "\#yyyy\-mm\-dd\#")
Me.Filter = strFilter
Me.FilterOn = True

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Beetle said:
One option would be to filter the recordset in the open event of your
form using code like the following;

Private Sub Form_Open (Cancel As Integer)

Dim strFilter As String
strFilter = "[YourDateField]=#" & DMax("[YourDateField]",
"[YourTable]",
_
& "[YourDateField] <=#" & Date & "#") & "#"
Me.Filter = strFilter
Me.FilterOn = True

End

This would return one record form your table (the max record that is
either equal to, or less than, todays date)

You could then have a command button that would show all records again
by turning off the filter;

Private Sub cmdShowAll_Click()

Me.FilterOn = False

End Sub

You may also want an unbound text box where the users could enter a
date to search for. The code might look like;

Private Sub txtSearch_AfterUpdate()

Me.FilterOn = False

With Me.RecordsetClone
FindFirst "[YourDateField]=#" & Me.txtSearch & "#"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
MsgBox "Date Not Found"
End If
End With

End Sub

Now, having said all that, the above may not necessarily be the *best*
approach if you have a multiuser application and are dealing with a
large
recordset. In that case it may be slower than you like because you are
still
pulling the entire record set (which is usually not desireable with
large
sets
of records) and just filtering it. If performance is not acceptable,
you
may
want to explore other options using SQL to only pull the records that
are requested each time.

--
_________

Sean Bailey


:

prior to, i guess, I can always modify the code depending on what
people
want, but if i just get close to today, it will be better than always
opening
the form with a 2011 date.

:

You say you have records through 2011, so when you say you want a
record "close to today" (assuming there is no record for todays
date)
do you mean prior to today, after today, or some range of dates?
--
_________

Sean Bailey


:

My db is sorted by date desc, but the db is getting bigger and the
date range
is increasing from 2005-2011, so when the form (single form) is
opened, I get
records from 2011, and have to scroll thru many records to view
some
recent
records. I can code the form to find 'today', but there may be a
couple of
weeks between some records, so the find record will fail looking
for
'today'
in many cases. So I was curious if anyone used a 'find
recordset'
sub to
open the form to a date that is CLOSE to today (without looping
thru
date-1,
date-2 etc).
I thought finding 'this month', but you may still have to scroll
thru
many
records to get to the end of the month. I may add a couple of
dropdowns to
jump quickly to a specific mo & yr. I just want the form to open
to
a date
nearest to today.
Click to expand...
Click to expand...
Click to expand...
 
B

Beetle

Thanks for taking the time Doug

Cheers
--
_________

Sean Bailey


Douglas J. Steele said:
The problem is pretty much restricted to users who have dd/mm/yyyy as their
short date format, but I wouldn't say that's a European issue. Most of the
British Commonwealth countries use that format. (I can't remember whether
using a non-standard date separator, such as dots or spaces, causes problems
as well)

The problem with your other example is that should the user expect to be
able to put the date in dd/mm/yyyy format, that's what's going to end up in
the Where clause, so it will fail for the first 12 days of each month (i.e.:
they'll put 05/01/2008, meaning 05 Jan, 2008, and Access will ALWAYS treat
it as 01 May, 2008, regardless of their Regional Settings. However, Access
is smart enough to know that 13/01/2008 isn't valid as mm/dd/yyyy, so will
ALWAYS treat that as 31 Jan, 2008)

The CDate function respects the user's Short Date setting, so you should
use:

..FindFirst "[YourDateField]=" & Format(CDate(Me!txtSearch),
"\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Beetle said:
Thanks for the tip Doug. This would apply to (guessing) European date
format?

Could the same problem potentially happen with;

.FindFirst "[YourDateField]=#" & Me!txtSearch & "#"

or not, because the Date function is not involved?
--
_________

Sean Bailey


Douglas J. Steele said:
<picky>
Some users have their Short Date format set to a format that Access won't
recognize correctly in a Where clause.

To be usable by all users, it's better to use

Private Sub Form_Open (Cancel As Integer)

Dim strFilter As String
strFilter = "[YourDateField]=" & Format(DMax("[YourDateField]", _
"[YourTable]", "[YourDateField] <=" & _
Format(Date, "\#yyyy\-mm\-dd\#")), "\#yyyy\-mm\-dd\#")
Me.Filter = strFilter
Me.FilterOn = True

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


One option would be to filter the recordset in the open event of your
form using code like the following;

Private Sub Form_Open (Cancel As Integer)

Dim strFilter As String
strFilter = "[YourDateField]=#" & DMax("[YourDateField]",
"[YourTable]",
_
& "[YourDateField] <=#" & Date & "#") & "#"
Me.Filter = strFilter
Me.FilterOn = True

End

This would return one record form your table (the max record that is
either equal to, or less than, todays date)

You could then have a command button that would show all records again
by turning off the filter;

Private Sub cmdShowAll_Click()

Me.FilterOn = False

End Sub

You may also want an unbound text box where the users could enter a
date to search for. The code might look like;

Private Sub txtSearch_AfterUpdate()

Me.FilterOn = False

With Me.RecordsetClone
FindFirst "[YourDateField]=#" & Me.txtSearch & "#"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
MsgBox "Date Not Found"
End If
End With

End Sub

Now, having said all that, the above may not necessarily be the *best*
approach if you have a multiuser application and are dealing with a
large
recordset. In that case it may be slower than you like because you are
still
pulling the entire record set (which is usually not desireable with
large
sets
of records) and just filtering it. If performance is not acceptable,
you
may
want to explore other options using SQL to only pull the records that
are requested each time.

--
_________

Sean Bailey


:

prior to, i guess, I can always modify the code depending on what
people
want, but if i just get close to today, it will be better than always
opening
the form with a 2011 date.

:

You say you have records through 2011, so when you say you want a
record "close to today" (assuming there is no record for todays
date)
do you mean prior to today, after today, or some range of dates?
--
_________

Sean Bailey


:

My db is sorted by date desc, but the db is getting bigger and the
date range
is increasing from 2005-2011, so when the form (single form) is
opened, I get
records from 2011, and have to scroll thru many records to view
some
recent
records. I can code the form to find 'today', but there may be a
couple of
weeks between some records, so the find record will fail looking
for
'today'
in many cases. So I was curious if anyone used a 'find
recordset'
sub to
open the form to a date that is CLOSE to today (without looping
thru
date-1,
date-2 etc).
I thought finding 'this month', but you may still have to scroll
thru
many
records to get to the end of the month. I may add a couple of
dropdowns to
jump quickly to a specific mo & yr. I just want the form to open
to
a date
nearest to today.
Click to expand...
Click to expand...
Click to expand...
 
M

Maarkr

Well, Sean, between your two examples, it gave me exactly what I wanted.
Thanks...
Final (breaks omitted):

Private Sub Form_Open(Cancel As Integer)
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

With rs

rs.FindFirst "[Start_Date]= #" & DMax("[Start_Date]", "[qryChrono]",
"[Start_Date] <=#" & Date & "#") & "#"

If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

I also appreciate Doug's input, but since this proudly made in the USA, and
the date formatting is mm/dd/yyyy, I'll only make it for local use.
Douglas J. Steele said:
The problem is pretty much restricted to users who have dd/mm/yyyy as their
short date format, but I wouldn't say that's a European issue. Most of the
British Commonwealth countries use that format. (I can't remember whether
using a non-standard date separator, such as dots or spaces, causes problems
as well)

The problem with your other example is that should the user expect to be
able to put the date in dd/mm/yyyy format, that's what's going to end up in
the Where clause, so it will fail for the first 12 days of each month (i.e.:
they'll put 05/01/2008, meaning 05 Jan, 2008, and Access will ALWAYS treat
it as 01 May, 2008, regardless of their Regional Settings. However, Access
is smart enough to know that 13/01/2008 isn't valid as mm/dd/yyyy, so will
ALWAYS treat that as 31 Jan, 2008)

The CDate function respects the user's Short Date setting, so you should
use:

..FindFirst "[YourDateField]=" & Format(CDate(Me!txtSearch),
"\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Beetle said:
Thanks for the tip Doug. This would apply to (guessing) European date
format?

Could the same problem potentially happen with;

.FindFirst "[YourDateField]=#" & Me!txtSearch & "#"

or not, because the Date function is not involved?
--
_________

Sean Bailey


:

<picky>
Some users have their Short Date format set to a format that Access won't
recognize correctly in a Where clause.

To be usable by all users, it's better to use

Private Sub Form_Open (Cancel As Integer)

Dim strFilter As String
strFilter = "[YourDateField]=" & Format(DMax("[YourDateField]", _
"[YourTable]", "[YourDateField] <=" & _
Format(Date, "\#yyyy\-mm\-dd\#")), "\#yyyy\-mm\-dd\#")
Me.Filter = strFilter
Me.FilterOn = True

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


One option would be to filter the recordset in the open event of your
form using code like the following;

Private Sub Form_Open (Cancel As Integer)

Dim strFilter As String
strFilter = "[YourDateField]=#" & DMax("[YourDateField]",
"[YourTable]",
_
& "[YourDateField] <=#" & Date & "#") & "#"
Me.Filter = strFilter
Me.FilterOn = True

End

This would return one record form your table (the max record that is
either equal to, or less than, todays date)

You could then have a command button that would show all records again
by turning off the filter;

Private Sub cmdShowAll_Click()

Me.FilterOn = False

End Sub

You may also want an unbound text box where the users could enter a
date to search for. The code might look like;

Private Sub txtSearch_AfterUpdate()

Me.FilterOn = False

With Me.RecordsetClone
FindFirst "[YourDateField]=#" & Me.txtSearch & "#"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
MsgBox "Date Not Found"
End If
End With

End Sub

Now, having said all that, the above may not necessarily be the *best*
approach if you have a multiuser application and are dealing with a
large
recordset. In that case it may be slower than you like because you are
still
pulling the entire record set (which is usually not desireable with
large
sets
of records) and just filtering it. If performance is not acceptable,
you
may
want to explore other options using SQL to only pull the records that
are requested each time.

--
_________

Sean Bailey


:

prior to, i guess, I can always modify the code depending on what
people
want, but if i just get close to today, it will be better than always
opening
the form with a 2011 date.

:

You say you have records through 2011, so when you say you want a
record "close to today" (assuming there is no record for todays
date)
do you mean prior to today, after today, or some range of dates?
--
_________

Sean Bailey


:

My db is sorted by date desc, but the db is getting bigger and the
date range
is increasing from 2005-2011, so when the form (single form) is
opened, I get
records from 2011, and have to scroll thru many records to view
some
recent
records. I can code the form to find 'today', but there may be a
couple of
weeks between some records, so the find record will fail looking
for
'today'
in many cases. So I was curious if anyone used a 'find
recordset'
sub to
open the form to a date that is CLOSE to today (without looping
thru
date-1,
date-2 etc).
I thought finding 'this month', but you may still have to scroll
thru
many
records to get to the end of the month. I may add a couple of
dropdowns to
jump quickly to a specific mo & yr. I just want the form to open
to
a date
nearest to today.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Separate Form 0
SCROLL THRU RECORDS ENTERED IN FORM 1
Refresh Form Update Query 1
append multiselect to table 8
Excel VBA to go to specific sheet & cell in the current workbook not working. 2
Open Form Displaying Records that Meet the Criteria selected 2
Using a form to Search 2
Allowing Temporary Sorting 1

Top