Retrieve the records based on the time range

[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

 

[Bob Quintal]

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.

[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]

 

[Jeff Boyce]

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

 

[George Nicholson]

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#"