Matlock,
One of the problems with using a parameter query like this is that you
are never sure of how Access is interpreting the values that you
enter. In my experience, Access will generally interpret date values
entered in the method as text strings, which will cause your query to
fail. Add a parameters clause to your query. This will also ensure
that the value entered can be interpreted as a date. It would look
something like:
PARAMETERS [Some date] DateTime, [Some other date] DateTime;
SELECT tblDates.start
FROM tblDates
WHERE tblDates.start BETWEEN [Some date] AND [Some other date]
If the user just hits [Enter] when these prompts come up, you may get
results you don't want, so you might want to have your WHERE clause
look like:
WHERE tblDates.start BETWEEN NZ([Some date], #1/1/2003#) AND NZ([Some
other date], #12/31/2003#)
Another one of the problems with querying a date/time field that
contains time values is that when you compare it's values to a date
value, you will have some records that are inadvertently left out of
the query. What I mean is that your [To Date m/d/yy] value of 6/12/03
actually looks like: "06/12/03 00:00:00" and any date/time values you
think you are getting for June 12 will actually not show up in the
query because they have values that are greater than midnight on that
day. What I usually do in this case is something like:
WHERE tblDates.start
BETWEEN NZ([Some date], #1/1/2003#)
AND DateAdd("d", 1, NZ([Some other date], #12/31/2003#))
--
HTH
Dale Fye
I have a date/time field stored in a table that maintains
a date&time stamp of a certain activity.
How can I:
1. limit my query/report to print all activities between
given dates. I have tried unsuccessfully with the column
property "Medium Date" and criteria
"Between [From Date m/d/yy] And [To Date m/d/yy]"
2. create a column in a query to print the time lapsed
between transactions.
The raw date/time field looks like: "06/12/2003 1:25:46
PM"
Note: I do not know VB and I am using Access 97.
