Show the records from table based on time range

[Ac]

Hello,

I created a form will display the records from a table. The records in the
table contain years (mm/dd/yyyy) information. I would like to write the code
to retrieve the records based on the year range. For example, retrieve all
data from year 2000 or data from 2001. Could some one help me?

Thanks!

 

[Klatuu]

There are several ways to implement this, but the basic filtering is:

Year([YearInfoDate]) = SomeYear.

You could use the Where argument of the OpenForm method.
You could use the Form's Filter and FilterOn properties.
You could include it in a query to use as the form's recordsource.

Which way you do it will depend on how your design works.

 

[Allen Browne]

The most efficient (excution-wise) query would be to type something like
this into the Criteria row:

= #1/1/2000# And < #1/1/2001#

The expression is designed to return values form Dec 31, even if they have a
time as well as a date.

If you want the query to pop up a parameter asking the user to enter the
year:
1. Choose Parameters on the Query menu (in query design.)
Access pops up the parameter dialog.
Enter:
WotYear Long Integer

2. In the Criteria row, enter:

= DateSerial([WotYear],1,1) And < DateSerial([WotYear]+1,1,1)

 

[Ac]

Thank you for your quick response.

Here is my original idea, and it does not work. Please help me.

Private Sub Form_Load()

Dim stDocName As String
Dim stLinkCriteria As String


stDocName = "AllRecords"


If Form!Start![cboYear] = 2000 Then ‘based on the combo box
selection


stLinkCriteria = [EffectiveDate] >= 1 / 1 / 2000 And [ExpirationDate] <=
12 / 31 / 2001 ‘select the data based on the year range



DoCmd.OpenForm stDocName, , , stLinkCriteria
'lblAll = "2000 All"
'lblTotal = "Total (2000 only)"

End If


End Sub


Thanks