Prev: how do I obtain data for Friday & Saturday on a Monday
Next: Quirky Query
From: DMainland on 29 Mar 2010 12:33
I use a crosstab query in a database as a first step in recalling multiple
monetary transactions made on the same day that, when added together, exceed
$10,000.00 and then generate a report based on the results. The query
currently returns multiple transactions made on the current day as per the
design. The OrderDate field in the query's design view is as follows:

Field: OrderDate
Table: Transaction
Total: Where
Crosstab:
Sort:
Criteria: "Date( )"
Or:

I would like to be able to bring this data up from dates in the past and
between dates in the past. Is this possible? Can I modify the 'criteria' in
the OrderDate field to accept varying parameters? Any help would be greatly
appreciated.

--
DMainland
From: Daryl S on 29 Mar 2010 14:02
DMainland -

Make a copy of your query, and in the new version, change your criteria to
be this:

Between [Enter Start Date] and [Enter End Date]

Then run your new query, and it will prompt you to enter both dates.

--
Daryl S


"DMainland" wrote:

> I use a crosstab query in a database as a first step in recalling multiple
> monetary transactions made on the same day that, when added together, exceed
> $10,000.00 and then generate a report based on the results. The query
> currently returns multiple transactions made on the current day as per the
> design. The OrderDate field in the query's design view is as follows:
>
> Field: OrderDate
> Table: Transaction
> Total: Where
> Crosstab:
> Sort:
> Criteria: "Date( )"
> Or:
>
> I would like to be able to bring this data up from dates in the past and
> between dates in the past. Is this possible? Can I modify the 'criteria' in
> the OrderDate field to accept varying parameters? Any help would be greatly
> appreciated.
>
> --
> DMainland
From: John W. Vinson on 29 Mar 2010 16:17
On Mon, 29 Mar 2010 09:33:01 -0700, DMainland
<DMainland(a)discussions.microsoft.com> wrote:

>Field: OrderDate
>Table: Transaction
>Total: Where
>Crosstab:
>Sort:
>Criteria: "Date( )"
>Or:
>
>I would like to be able to bring this data up from dates in the past and
>between dates in the past. Is this possible? Can I modify the 'criteria' in
>the OrderDate field to accept varying parameters? Any help would be greatly
>appreciated.

Sure. A convenient way to collect parameters is with unbound form controls. A
criterion such as

>= [Forms]![YourForm]![txtStart] AND < [Forms]![YourForm]![txtEnd] + 1

will collect all records within the range (the +1 ensures that if there's a
time component to OrderDate that you'll get all records on the last day).

You will need to put [Forms]![YourForm]![txtStart] and
[Forms]![YourForm]![txtEnd] in the query's Parameters collection (this is
always allowed, often helpful, but for crosstab queries it's obligatory). To
do so open the query in design view; right click the background of the tables,
and select Parameters from the dropdown. Copy and paste the form references -
they must match exactly - in the left column and select Date/Time in the
right.
--

John W. Vinson [MVP]
From: John W. Vinson on 29 Mar 2010 17:15
On Mon, 29 Mar 2010 11:02:02 -0700, Daryl S <DarylS(a)discussions.microsoft.com>
wrote:

>DMainland -
>
>Make a copy of your query, and in the new version, change your criteria to
>be this:
>
>Between [Enter Start Date] and [Enter End Date]
>
>Then run your new query, and it will prompt you to enter both dates.

nitpick: if you do this you must also define both parameters in the query's
Parameters collection. It's optional for most queries but required for
crosstabs.
--

John W. Vinson [MVP]
From: Daryl S on 30 Mar 2010 10:42
John -

Is that required now in Access 2007? I am still on Access 2003, and I only
need to define the parameters if I am using the crosstab query as, say, the
basis for another query. Otherwise I have not had to define the parameters
whether they are pulling data from a form or entered manually.

Thanks!
--
Daryl S


"John W. Vinson" wrote:

> On Mon, 29 Mar 2010 11:02:02 -0700, Daryl S <DarylS(a)discussions.microsoft.com>
> wrote:
>
> >DMainland -
> >
> >Make a copy of your query, and in the new version, change your criteria to
> >be this:
> >
> >Between [Enter Start Date] and [Enter End Date]
> >
> >Then run your new query, and it will prompt you to enter both dates.
>
> nitpick: if you do this you must also define both parameters in the query's
> Parameters collection. It's optional for most queries but required for
> crosstabs.
> --
>
> John W. Vinson [MVP]
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: how do I obtain data for Friday & Saturday on a Monday
Next: Quirky Query