Can't run Query, can't build formula

P

Phil Smith

I have a query which I built on the grid. It used to work. I added a
field, and it does not work anymore. It hangs access. Here is the
thing. NO part of this is based on queries, it is all based on (odbc)
tables. When I right-Click BUILD on a field with the following, access
hangs: This is NOT the field I added BTW.

Expr1: Sum(IIf([invoice_h]![prebook_id] In
(163,164),[invoice_h]![product_total],[invoice_h]![invoice_total]))

I have repaired the database. I have copied the SQL into another
database. The whole SQL is as follows. (Kinda ugly.)


Any Ideas?



TRANSFORM Sum(IIf([invoice_h]![prebook_id] In
(163,164),[invoice_h]![product_total],[invoice_h]![invoice_total])) AS Expr1
SELECT territory.name AS Territory, customer.name AS Customer,
Sum(IIf([division]![name]="apparel" And
[invoice_h]![invoice_date]>=#8/1/2008# And
[invoice_h]![invoice_date]<#8/1/2009#,[invoice_d]![price]*[invoice_d]![ship_qty],0))
AS ApparelSales
FROM division INNER JOIN ((invoice_d INNER JOIN ((territory INNER JOIN
customer ON territory.territory_id = customer.territory_id) INNER JOIN
(invoice_h INNER JOIN prebook ON invoice_h.prebook_id =
prebook.prebook_id) ON customer.customer_id = invoice_h.customer_id) ON
invoice_d.invoice_id = invoice_h.invoice_id) INNER JOIN item ON
(prebook.prebook_id = item.prebook_id) AND (invoice_d.item_id =
item.item_id)) ON division.division_id = item.division_id
WHERE (((IIf([invoice_h]![prebook_id] In
(153,155,163,164),[invoice_d]![price]*[invoice_d]![order_qty],0))>=#9/1/2009#
And (IIf([invoice_h]![prebook_id] In
(153,155,163,164),[invoice_d]![price]*[invoice_d]![order_qty],0))<#12/2/2009#)
AND ((invoice_h.prebook_id)=163 Or (invoice_h.prebook_id)=164)) OR
(((IIf([invoice_h]![prebook_id] In
(153,155,163,164),[invoice_d]![price]*[invoice_d]![order_qty],0))>=#1/1/2009#
And (IIf([invoice_h]![prebook_id] In
(153,155,163,164),[invoice_d]![price]*[invoice_d]![order_qty],0))<#4/1/2009#)
AND ((invoice_h.prebook_id)=155 Or (invoice_h.prebook_id)=153)) OR
(((invoice_h.invoice_date)>=#8/1/2008# And
(invoice_h.invoice_date)<#8/1/2009#) AND ((division.name)="Apparel"))
GROUP BY territory.name, customer.name, invoice_h.invoice_date,
division.name
ORDER BY territory.name, customer.name, IIf([invoice_h]![prebook_id] In
(163,164),"Fall 09","Spring 09")
PIVOT IIf([invoice_h]![prebook_id] In (163,164),"Fall 09","Spring 09");
 
K

KARL DEWEY

It used to work. I added a field
What field was added?

I see things in your query that baffles me --
WHERE (((IIf([invoice_h]![prebook_id] In (153,155,163,164),
[invoice_d]![price]*[invoice_d]![order_qty],0))>=#9/1/2009#
And (IIf([invoice_h]![prebook_id] In (153,155,163,164),
[invoice_d]![price]*[invoice_d]![order_qty],0))<#12/2/2009#) ....
What are you comparing #9/1/2009# and #12/2/2009# to?

--
Build a little, test a little.


Phil Smith said:
I have a query which I built on the grid. It used to work. I added a
field, and it does not work anymore. It hangs access. Here is the
thing. NO part of this is based on queries, it is all based on (odbc)
tables. When I right-Click BUILD on a field with the following, access
hangs: This is NOT the field I added BTW.

Expr1: Sum(IIf([invoice_h]![prebook_id] In
(163,164),[invoice_h]![product_total],[invoice_h]![invoice_total]))

I have repaired the database. I have copied the SQL into another
database. The whole SQL is as follows. (Kinda ugly.)


Any Ideas?



TRANSFORM Sum(IIf([invoice_h]![prebook_id] In
(163,164),[invoice_h]![product_total],[invoice_h]![invoice_total])) AS Expr1
SELECT territory.name AS Territory, customer.name AS Customer,
Sum(IIf([division]![name]="apparel" And
[invoice_h]![invoice_date]>=#8/1/2008# And
[invoice_h]![invoice_date]<#8/1/2009#,[invoice_d]![price]*[invoice_d]![ship_qty],0))
AS ApparelSales
FROM division INNER JOIN ((invoice_d INNER JOIN ((territory INNER JOIN
customer ON territory.territory_id = customer.territory_id) INNER JOIN
(invoice_h INNER JOIN prebook ON invoice_h.prebook_id =
prebook.prebook_id) ON customer.customer_id = invoice_h.customer_id) ON
invoice_d.invoice_id = invoice_h.invoice_id) INNER JOIN item ON
(prebook.prebook_id = item.prebook_id) AND (invoice_d.item_id =
item.item_id)) ON division.division_id = item.division_id
WHERE (((IIf([invoice_h]![prebook_id] In
(153,155,163,164),[invoice_d]![price]*[invoice_d]![order_qty],0))>=#9/1/2009#
And (IIf([invoice_h]![prebook_id] In
(153,155,163,164),[invoice_d]![price]*[invoice_d]![order_qty],0))<#12/2/2009#)
AND ((invoice_h.prebook_id)=163 Or (invoice_h.prebook_id)=164)) OR
(((IIf([invoice_h]![prebook_id] In
(153,155,163,164),[invoice_d]![price]*[invoice_d]![order_qty],0))>=#1/1/2009#
And (IIf([invoice_h]![prebook_id] In
(153,155,163,164),[invoice_d]![price]*[invoice_d]![order_qty],0))<#4/1/2009#)
AND ((invoice_h.prebook_id)=155 Or (invoice_h.prebook_id)=153)) OR
(((invoice_h.invoice_date)>=#8/1/2008# And
(invoice_h.invoice_date)<#8/1/2009#) AND ((division.name)="Apparel"))
GROUP BY territory.name, customer.name, invoice_h.invoice_date,
division.name
ORDER BY territory.name, customer.name, IIf([invoice_h]![prebook_id] In
(163,164),"Fall 09","Spring 09")
PIVOT IIf([invoice_h]![prebook_id] In (163,164),"Fall 09","Spring 09");
Click to expand...
 
P

Phil Smith

I had to redo this as a multipiece query, and managed to get it to work.
Thanx.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Another query too complex problem 3
Exported query Runs forever, creates huge spreadsheet with no data. 2
Enter Parameter Value .WHY? 2
4+0 = 2 in Union query. Data lost. 13
engine does not recognize <name> as a valid field name or expression.(Error 3070) 3
Query works, query based on it fails. 2

Top