prompt for date range

[jallen919]

I have a cross tab query setup but I need to only include certain records in the query based on a date range. I know how to setup the date range parameters but when I include my date column (not to show) with the criteria &quot;>=[Enter beginning date] And <=[Enter ending date]&quot; in my cross tab query I get the error &quot;database jet engine does not recognize [Enter beginning date] as valid field name or expression&quot;

Any help?

 

[ghubbell]

Hi J Allen, Assumption here is that date is a row heading..
Try this: add another column in your crosstab (your date) and set the &quot;Total&quot; row to &quot;Where&quot; and add:

Between [Enter beginning date] And [Enter ending date]

to its first criteria line. Might work for you! Gord
ghubbell@total.net

 

[jallen919]

I don't want the date to be a row heading. I just want to pull a group of records based on a date range then create my cross tab query from there.

 

[ghubbell]

Alright, if you have this applied in a select query, on its own does it work? I don't believe this will be obtainable when applied to a crosstab. You would have to have your first query a &quot;Make table&quot; query. Then run a crosstab on the table instead of the query. The table's contents can be deleted as you choose if you are working from a form: a delete query as the form closes or opens... Gord
ghubbell@total.net

 

[MichaelRed]

Gord,

Close. It dosn't need to be a make table, just a select query w/ the criteria set to the date range. Base the XTab on the select w/ the data range. (Layering? - Anyone?)


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ghubbell]

Hi Michael! It won't work if the date is the value in the crosstab. Row or Column you can do it directly but for some reason it doesn't like it in the middle... Gord
ghubbell@total.net

 

[MichaelRed]

Gord,

I didn't say (or mean) to put the date range in the crosstab. Put it in a SELECT query as the selection criteria for th date column. Base the XTab on the (pre-)SELECTed query.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ghubbell]

Exactly Mike. It still won't work (and I don't understand why!) Gord
ghubbell@total.net

 

[MichaelRed]

... but I have done this the proverbial thousand times and -other than my own fumblings and foibles - it works quite wekk. So What, more or less EXACTLY doesn't work?

Did you make a sample table and the corresponding sample queries? WHERE are the errors occuring? What WRONG results sets are you getting? THIS HAS TO WORK (otherwise I'm not going to be able to keep on working for much longer)!!!


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ghubbell]

Sorry Mike, had to step out for a quick trip to the Vet. (No, not me...sick kitty but he's ok). Northwinds Db, and paste this for &quot;Query1&quot;:

SELECT Orders.OrderID, Orders.OrderDate, [Order Details].ProductID
FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE (((Orders.OrderDate) Between [enter 1/1/1997] And [Enter 12/31/1997]));
Runs perfect on its own.

And this for Query2

TRANSFORM Count(Query1.OrderDate) AS CountOfOrderDate
SELECT Query1.OrderID, Count(Query1.OrderDate) AS [Total Of OrderDate]
FROM Query1
GROUP BY Query1.OrderID
PIVOT Query1.ProductID;

Won't work. It's of course caused by the date being the value. While you're in Northwinds look at Quarterly Order by Products. A fine crosstab that you can paste in the &quot;Between...And...&quot; in the criteria and will run perfectly without a first query. Rows or Columns work fine although there is a limit in the number of columns.
Weird!



Gord
ghubbell@total.net