Hillary,
You didn't indicate whether your date field is strictly dates, or
includes a time stamp (other than 00:00:00) as well? If it has a time
value, you must be very cautions when creating your queries, to ensure
that you don't inadvertantly filter out pertinent records.
Let me give you a brief primer on dates.
1. All dates in access are actually stored as 64 bit floating point
numbers (the integer part represents the number of days, the decimal
part represents the percentage of the day). When you store a datetime
value (10/17/03 7:49:51 AM) it is actually stored as
(37911.3262847222). How the date is displayed depends on the Format
property you use, so although what you see when you look at the field
may be 10/17/03 (short date format), what is actually stored may be
10/17/03 6:23:17 PM, depending on what function Date() or Now() is
used to write that value.
2. You can use the DateValue() and TimeValue() functions, to return
only that portion of the date value that you want.
3. You can use the DateSerial() function to create a date value if
you know the value of the Year, Month, and Day that are associated
with it.
4. You can use the DatePart(), Year(), Month(), and Day(), Hour(),
Minute(), and Second() functions to determine those values of a
date/time value.
5. To perform arithmetic operations on dates (add or subtract a
number of days, months, years, etc...) you could just subtract the
apprpriate numeric value from the date #10/17/03# - 1.5 = #10/15/03
12:00 PM, but this is not recommended. The preferred method is to use
the DateAdd() function, which accepts parameters for day, month, year,
hour, minute, second.
6. When comparing date values that also contain time stamps, you can
inadvertantly filter out records that you really want to get. If, for
example, we have a record in you table which has a fldTransDateTime of
10/17/03 8:00 AM, then the following select statment will not include
that record, because the value actually stored will be 37911.333 and
the number you are comparing it to is actually 37911.00
SELECT * FROM yourTable WHERE fldTransDateTime <= #10/17/03#
To make sure you get all the records that were written today, you
should modify your query to:
SELECT * FROM yourTable WHERE fldTransDateTime < #10/18/03#
--
HTH
Dale Fye
Hilary-
You can use the Year function to extract the year value from a
date/time
field, but the query might run slowly if you have thousands of rows.
On
your query grid, create a calculated field:
StartYear: Year(StartDate)
... and put your criteria for the year you want under that. If you
want to
prompt the user for the year value, you can enter a parameter such as:
[Enter Start Year:]
A more efficient way would be to put the following expression on the
Criteria line under your StartDate field:
Between DateSerial([Enter Start Year],1,1) And DateSerial([Enter Start
Year],12,31)
Doing a range is a bit more difficult. Do you want all programs that
ran at
any time between a start year and an end year? If so, put this under
StartDate:
<=DateSerial([Enter End Year], 12, 31)
... and put this under EndDate:
=DateSerial([Enter Start Year], 1, 1)
Hope that helps...
--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=143441
1
