Run-time Error 2186

[Alberto]

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 nature:
"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,

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?

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.orders_id) INNER JOIN
orders_total ON orders.orders_id=orders_total.orders_id) INNER JOIN
orders_products ON
orders_products_attributes.orders_products_id=orders_products.orders_products_id) ON orders_status.orders_status_id=orders.orders_status
WHERE (((orders.invoice_date) Between FromDate() And ToDate()) AND
((orders_products_attributes.products_options_values) 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");

 

[Alberto]

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

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 nature:
"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,

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?

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.orders_id) INNER JOIN
orders_total ON orders.orders_id=orders_total.orders_id) INNER JOIN
orders_products ON
orders_products_attributes.orders_products_id=orders_products.orders_products_id) ON orders_status.orders_status_id=orders.orders_status
WHERE (((orders.invoice_date) Between FromDate() And ToDate()) AND
((orders_products_attributes.products_options_values) 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");

 

[strive4peace]

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
*

 

[AccessVandal via AccessMonster.com]

Good to hear it's solve.

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