Query based on a Date Range

[mccloud]

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?

 

[JL]

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]

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?

 

[John Spencer (MVP)]

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?

 

[JL]

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.

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?

 

[mccloud]

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!

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?

 

[John Spencer (MVP)]

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!

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?