Parameter Query and Report

[Lesley]

Hi

This may already be answered in other threads but I can't get my head round
the answers so hope you can help.

I have a very simple report in Access 2000 and a simple parameter query
which when opened prompts for start date and end date and successfully works
when run.

I have added the parameter query to the record source of the report but it
does not select the records asked for. In summary the parameter query works
when you run it on its own but when I open the report (even though the record
source is the correct query) it does not work. I simply get the report
displaying all record to date.

I am a novice so i would really appreciate a very simple answer (if poss!)


Many thanks
Lesley

 

[Ken Sheridan]

Lesley:

With date/time parameters in a query its always a good idea to declare them
as otherwise a date entered in a format such as 7/4/2008 could be interpreted
as an arithmetical expression (7 divided by 4 divided by 2008). This won't
cause an error as dates are actually stored as 64 bit numbers, but it could
give the wrong result. I can't see why this would cause the query to behave
differently in the two contexts, however. Its worth a try, though. Say the
parameters are [Start Date:] and [End Date:], then in query design view
select Parameters from the Query menu. In the dialogue you'd enter [Start
Date:] and [End Date:] on separate rows in the Parameter column and select
Date/Time for each in the Data Type column. Close the dialogue and save the
query.

If this doesn't do the trick (and I'm not optimistic) are you sure that the
RecordSource property of the report is exactly the same query as the one with
the parameters? If so then are you testing both the query and the report
with the same date parameter values entered in the same format?

One other thing to note about date ranges is that if any rows in the table
contain dates on the last day of the range and these contain a non-zero time
of day element, which can easily happen inadvertently (the inappropriate use
of the Now() function to enter a default value of the current date is a
common culprit), then the rows with the dates on the last day of the range
won't be returned if the range is defined by a BETWEEN….AND operation. This
wouldn't explain the behaviour you are experiencing, as you are getting
unwanted rows returned, not too few, but its something to be aware of. You
can ensure that all dates on the last day of the range are picked up,
regardless of whether their time of day is zero or not, by defining the range
differently, restricting it to dates on or after the start date and before
the day after end date. To do this put something like this in the criteria
row of the date/time column in query design view:

= [Start Date:] And [YourDateField] < [End Date:] + 1

Note that you have to put the name of the field in the second part of the
expression. If you do this and save the query and then open it again in
design view you'll find Access will have moved things round a bit, but it
will work just the same.

Ken Sheridan
Stafford, England

 

[Tom van Stiphout]

On Tue, 29 Jan 2008 05:24:00 -0800, Lesley

Bring the query in design mode, and select (off the top of my head)
Query > Parameters. Specify that the two parameters are Date. The
default would be text, which sorts differently.
Also double-check that the field you are filtering on has a Date/Time
data type.

-Tom.