Query based on a Date Range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm working with a linked table that has order line history information. I
need to specify a date range for example 20040109 - 20041231 and have results
sorted and totaled by Month. Example... Sept2004, Oct2004, Nov2004, Dec2004.
The range can be up to 12 months. Any ideas?
 
Hi mccloud,

This should wrok.

Select Format(HistTable.OrderDate, "mmmmyyyy") As MonYear,
Sum(HistTable.OrderSales)
From HistTable
Where HistTable.OrderDate >= [StartDateRange] and HistTable.OrderDate <=
[EndDateRange]
Group By MonYear;

Hope this will help.
 
You tried to execute a query that does not include the specified expression
Format(sales_by_date.billed_dt,"yyyymmdd") as part of an aggregate function.

Here is my SQL statment; SELECT Format(sales_by_date.billed_dt,"yyyymmdd")
AS MonYear, Sum(sales_by_date.cost_amt) AS SumOfcost_amt, MonYear
FROM sales_by_date
WHERE (((sales_by_date.billed_dt)>=[StartDateRange] And
(sales_by_date.billed_dt)<=[EndDateRange]))
GROUP BY MonYear;


JL said:
Hi mccloud,

This should wrok.

Select Format(HistTable.OrderDate, "mmmmyyyy") As MonYear,
Sum(HistTable.OrderSales)
From HistTable
Where HistTable.OrderDate >= [StartDateRange] and HistTable.OrderDate <=
[EndDateRange]
Group By MonYear;

Hope this will help.

mccloud said:
I'm working with a linked table that has order line history information. I
need to specify a date range for example 20040109 - 20041231 and have results
sorted and totaled by Month. Example... Sept2004, Oct2004, Nov2004, Dec2004.
The range can be up to 12 months. Any ideas?
Click to expand...
Click to expand...
 
Try the following.

SELECT Format(sales_by_date.billed_dt,"yyyymmdd") AS MonYear,
Sum(sales_by_date.cost_amt) AS SumOfcost_amt
FROM sales_by_date
WHERE (((sales_by_date.billed_dt)>=[StartDateRange]
And (sales_by_date.billed_dt)<=[EndDateRange]))
GROUP BY Format(sales_by_date.billed_dt,"yyyymmdd")
You tried to execute a query that does not include the specified expression
Format(sales_by_date.billed_dt,"yyyymmdd") as part of an aggregate function.

Here is my SQL statment; SELECT Format(sales_by_date.billed_dt,"yyyymmdd")
AS MonYear, Sum(sales_by_date.cost_amt) AS SumOfcost_amt, MonYear
FROM sales_by_date
WHERE (((sales_by_date.billed_dt)>=[StartDateRange] And
(sales_by_date.billed_dt)<=[EndDateRange]))
GROUP BY MonYear;

JL said:
Hi mccloud,

This should wrok.

Select Format(HistTable.OrderDate, "mmmmyyyy") As MonYear,
Sum(HistTable.OrderSales)
From HistTable
Where HistTable.OrderDate >= [StartDateRange] and HistTable.OrderDate <=
[EndDateRange]
Group By MonYear;

Hope this will help.

mccloud said:
I'm working with a linked table that has order line history information. I
need to specify a date range for example 20040109 - 20041231 and have results
sorted and totaled by Month. Example... Sept2004, Oct2004, Nov2004, Dec2004.
The range can be up to 12 months. Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
Hi mccloud,

I am surprised that it did not work. I did not get a change to execute my
query before I post it.
I guess John already have something that I would try next. Give that a shot.
Surprised that "Group By" does not take alias. Oh, well, that Microsoft for
you.


mccloud said:
You tried to execute a query that does not include the specified expression
Format(sales_by_date.billed_dt,"yyyymmdd") as part of an aggregate function.

Here is my SQL statment; SELECT Format(sales_by_date.billed_dt,"yyyymmdd")
AS MonYear, Sum(sales_by_date.cost_amt) AS SumOfcost_amt, MonYear
FROM sales_by_date
WHERE (((sales_by_date.billed_dt)>=[StartDateRange] And
(sales_by_date.billed_dt)<=[EndDateRange]))
GROUP BY MonYear;


JL said:
Hi mccloud,

This should wrok.

Select Format(HistTable.OrderDate, "mmmmyyyy") As MonYear,
Sum(HistTable.OrderSales)
From HistTable
Where HistTable.OrderDate >= [StartDateRange] and HistTable.OrderDate <=
[EndDateRange]
Group By MonYear;

Hope this will help.

mccloud said:
I'm working with a linked table that has order line history information. I
need to specify a date range for example 20040109 - 20041231 and have results
sorted and totaled by Month. Example... Sept2004, Oct2004, Nov2004, Dec2004.
The range can be up to 12 months. Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
Ok that got me a little futher but now I'm getting "This expression is typed
incorrectly, or it is too complex to be evaluated. For example, a numeric
expression may contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to variables."

Help!

John Spencer (MVP) said:
Try the following.

SELECT Format(sales_by_date.billed_dt,"yyyymmdd") AS MonYear,
Sum(sales_by_date.cost_amt) AS SumOfcost_amt
FROM sales_by_date
WHERE (((sales_by_date.billed_dt)>=[StartDateRange]
And (sales_by_date.billed_dt)<=[EndDateRange]))
GROUP BY Format(sales_by_date.billed_dt,"yyyymmdd")
You tried to execute a query that does not include the specified expression
Format(sales_by_date.billed_dt,"yyyymmdd") as part of an aggregate function.

Here is my SQL statment; SELECT Format(sales_by_date.billed_dt,"yyyymmdd")
AS MonYear, Sum(sales_by_date.cost_amt) AS SumOfcost_amt, MonYear
FROM sales_by_date
WHERE (((sales_by_date.billed_dt)>=[StartDateRange] And
(sales_by_date.billed_dt)<=[EndDateRange]))
GROUP BY MonYear;

JL said:
Hi mccloud,

This should wrok.

Select Format(HistTable.OrderDate, "mmmmyyyy") As MonYear,
Sum(HistTable.OrderSales)
From HistTable
Where HistTable.OrderDate >= [StartDateRange] and HistTable.OrderDate <=
[EndDateRange]
Group By MonYear;

Hope this will help.

:

I'm working with a linked table that has order line history information. I
need to specify a date range for example 20040109 - 20041231 and have results
sorted and totaled by Month. Example... Sept2004, Oct2004, Nov2004, Dec2004.
The range can be up to 12 months. Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
Probably messed up on the parentheses or left out a space. Also for safety, I
would declare the parameters as dates. It is possible that Access is not
correctly interpreting the parameters.

I've rewritten this with a minimum of parens. Access will add them back in when
you save and close.

Also I noticed that you were doing this for months. The SQL I supplied would
have totalled by days.

Parameters [StartDateRange] DateTime, [EndDateRange] DateTime;
SELECT Format(sales_by_date.billed_dt,"yyyymm") AS MonYear,
Sum(sales_by_date.cost_amt) AS SumOfcost_amt
FROM sales_by_date
WHERE sales_by_date.billed_dt>=[StartDateRange]
And sales_by_date.billed_dt <=[EndDateRange]
GROUP BY Format(sales_by_date.billed_dt,"yyyymm")
Ok that got me a little futher but now I'm getting "This expression is typed
incorrectly, or it is too complex to be evaluated. For example, a numeric
expression may contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to variables."

Help!

John Spencer (MVP) said:
Try the following.

SELECT Format(sales_by_date.billed_dt,"yyyymmdd") AS MonYear,
Sum(sales_by_date.cost_amt) AS SumOfcost_amt
FROM sales_by_date
WHERE (((sales_by_date.billed_dt)>=[StartDateRange]
And (sales_by_date.billed_dt)<=[EndDateRange]))
GROUP BY Format(sales_by_date.billed_dt,"yyyymmdd")
You tried to execute a query that does not include the specified expression
Format(sales_by_date.billed_dt,"yyyymmdd") as part of an aggregate function.

Here is my SQL statment; SELECT Format(sales_by_date.billed_dt,"yyyymmdd")
AS MonYear, Sum(sales_by_date.cost_amt) AS SumOfcost_amt, MonYear
FROM sales_by_date
WHERE (((sales_by_date.billed_dt)>=[StartDateRange] And
(sales_by_date.billed_dt)<=[EndDateRange]))
GROUP BY MonYear;

:

Hi mccloud,

This should wrok.

Select Format(HistTable.OrderDate, "mmmmyyyy") As MonYear,
Sum(HistTable.OrderSales)
From HistTable
Where HistTable.OrderDate >= [StartDateRange] and HistTable.OrderDate <=
[EndDateRange]
Group By MonYear;

Hope this will help.

:

I'm working with a linked table that has order line history information. I
need to specify a date range for example 20040109 - 20041231 and have results
sorted and totaled by Month. Example... Sept2004, Oct2004, Nov2004, Dec2004.
The range can be up to 12 months. Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top