Retrieve the records based on the time range

  • Thread starter Thread starter Ac
  • Start date Start date
A

Ac

Hi

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.

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


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
 
Hi

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.
Click to expand...
[code snipped]

The year() function extracts that part of the date.
The stLinkCriteria can be based on that, no need for any if
statements.

stLinkCriteria= "year([EffectiveDate]) = " & me.[cboYear]
 
Why are you using code to do this?

Take a look at "queries" -- they should be much easier to implement. You
may also want to use the Year([YourDateField]) function in your query so you
can get the year's data.

If you need to use a date range as a selection criterion, you could use
something like:

Between [Enter start date] And [Enter end date]

in the selection criterion for your date field in the query. This is a
"paramaterized query", and will prompt you for the dates (not the years).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Try adding date delimiters (and quotes):

stLinkCriteria = "[EffectiveDate] >= #1/1/2000# And [ExpirationDate]
<=#12 /31/2001#"

if Expiration Date includes Time data, take that into account:
"... And [ExpirationDate] < #1/1/2002#"
 
Back
Top