Run-time Error 2186

Thanks for your responses. Here are the answers to your questions:

Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd")
I can't say much about this. Have you test this alone on the separated query?
This works fine on the separated query. It is the method by which I'm
grouping [products_quantity] on a weekly basis.

GROUP BY Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd");
Should it be "GROUP BY Week;"?
I created the query in "Design" view within Access and this is the way the
SQL came out. When I went into the SQL to change it to "GROUP BY Week" I got
an error of the following natu
"You tried to execute a query that does not include the specified expression
'Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd")' as part
of an aggregate function.

I also tried recompiling,

"AccessVandal via AccessMonster.com" wrote:

Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd")
I can't say much about this. Have you test this alone on the separated query?

GROUP BY Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd");

Should it be "GROUP BY Week;"?

Is Week reserved in a subroutine or function?

Alberto wrote:
Crystal,

Thanks for all of your help.

Currently, qryNumberOfBuilds looks like this:
Week SumOfproducts_quantity
1/1/07 100
1/8/07 123
1/15/07 130

I need it to look like this:
1/1/07 1/8/07 1/15/07
SumOfproducts_quantity 100 123 130

Here is the underlying SQL.
SELECT Sum(orders_products.products_quantity) AS SumOfproducts_quantity,
Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd") AS Week
FROM orders_status INNER JOIN (((orders INNER JOIN
orders_products_attributes ON
orders.orders_id=orders_products_attributes.order s_id) INNER JOIN
orders_total ON orders.orders_id=orders_total.orders_id) INNER JOIN
orders_products ON
orders_products_attributes.orders_products_id=ord ers_products.orders_products_id) ON orders_status.orders_status_id=orders.orders_statu s
WHERE (((orders.invoice_date) Between FromDate() And ToDate()) AND
((orders_products_attributes.products_options_val ues) Like "*Build*") AND
((orders_products_attributes.products_options) Like "*Build*") AND
((orders_total.class)="ot_total") AND
((orders_status.orders_status_name)="Shipped"))
GROUP BY Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd");

--
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200901/1

It turns out that I had not set the data types of all dynamic criteria in the
Query-Parameters. I needed to do this for FromDate() and ToDate().

Once I did that, it seemed to fix the issue. Thanks for all of your help.

Alberto


"Alberto" wrote:

Thanks for your responses. Here are the answers to your questions:

Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd")
I can't say much about this. Have you test this alone on the separated query?
This works fine on the separated query. It is the method by which I'm
grouping [products_quantity] on a weekly basis.

GROUP BY Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd");
Should it be "GROUP BY Week;"?
I created the query in "Design" view within Access and this is the way the
SQL came out. When I went into the SQL to change it to "GROUP BY Week" I got
an error of the following natu
"You tried to execute a query that does not include the specified expression
'Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd")' as part
of an aggregate function.

I also tried recompiling,

"AccessVandal via AccessMonster.com" wrote:

Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd")
I can't say much about this. Have you test this alone on the separated query?

GROUP BY Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd");

Should it be "GROUP BY Week;"?

Is Week reserved in a subroutine or function?

Alberto wrote:
Crystal,

Thanks for all of your help.

Currently, qryNumberOfBuilds looks like this:
Week SumOfproducts_quantity
1/1/07 100
1/8/07 123
1/15/07 130

I need it to look like this:
1/1/07 1/8/07 1/15/07
SumOfproducts_quantity 100 123 130

Here is the underlying SQL.
SELECT Sum(orders_products.products_quantity) AS SumOfproducts_quantity,
Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd") AS Week
FROM orders_status INNER JOIN (((orders INNER JOIN
orders_products_attributes ON
orders.orders_id=orders_products_attributes.order s_id) INNER JOIN
orders_total ON orders.orders_id=orders_total.orders_id) INNER JOIN
orders_products ON
orders_products_attributes.orders_products_id=ord ers_products.orders_products_id) ON orders_status.orders_status_id=orders.orders_statu s
WHERE (((orders.invoice_date) Between FromDate() And ToDate()) AND
((orders_products_attributes.products_options_val ues) Like "*Build*") AND
((orders_products_attributes.products_options) Like "*Build*") AND
((orders_total.class)="ot_total") AND
((orders_status.orders_status_name)="Shipped"))
GROUP BY Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd");

--
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200901/1

glad you got it working, Alberto!

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day
*




Alberto wrote:
It turns out that I had not set the data types of all dynamic criteria in the
Query-Parameters. I needed to do this for FromDate() and ToDate().

Once I did that, it seemed to fix the issue. Thanks for all of your help.

Alberto

Good to hear it's solve.

You probaly miss copying the string "PARAMETERS yourentrystring" in the SQL
view.

Alberto wrote:
It turns out that I had not set the data types of all dynamic criteria in the
Query-Parameters. I needed to do this for FromDate() and ToDate().

Once I did that, it seemed to fix the issue. Thanks for all of your help.

Alberto

--
Please Rate the posting if helps you.

Message posted via http://www.accessmonster.com