Access crashes when using a query field in report

  • Thread starter Thread starter lunde
  • Start date Start date
L

lunde

In a query I have this subquery

PreviousOrders: (SELECT COUNT(date_purchased) FROM orders AS orders1
WHERE orders1.date_purchased < orders.date_purchased AND
orders1.customers_id = orders.customers_id)

This subquery count the number of previous orders for the particular
customers_id, and when I execute the query it works fine.

But when I try to include the field PreviousOrders in a report,
MS-Access crashes. No explanation - just a complete crash down.

There are no NULL values in orders.date_purchased or
orders.customers_id. Any explanations on what is going wrong?

Thanks!
 
Post the main query as well.

And if that query is based on another query, trace that back up as well.

Subqueries are notorious. Presumably you've already tried a compact/repair
in case it's just a bad index.

What version of Access is this? Locate msaccess.exe on your drive (typically
in c:\Program Files\Microsoft Office\Office), right-click and choose
Properties. The Version tab gives full info.

Also, what version of JET? Locate msjet40.dll (typically in
c:\windows\system32), and let us know the version.
 
Thanks Allen. Please see my answers below. After your reply I made a
small adjustment to my query, which has resulted in that MS-Access does
no longer crash but reports this error in stead: "Multi-level GROUP BY
clause is not allowed in a sub-query". Still, the query works as
expected, but when putting the field "PreviousOrders" into the report
the above error appears.

Post the main query as well.SELECT orders.orders_id, orders.suppl_onum, orders.suppl_note,
orders.date_purchased, orders.delivery_name, orders.delivery_company,
orders.delivery_street_address, orders.delivery_suburb,
orders.delivery_city, orders.delivery_postcode, orders.delivery_country,
orders.eu_vat, lang_texts.ship_to, lang_texts.items, lang_texts.item_no,
orders_products.orders_products_id,
IIf(orders!delivery_company='','',orders!delivery_company & Chr(13) &
Chr(10)) & orders!delivery_name & Chr(13) & Chr(10) &
orders!delivery_street_address & Chr(13) & Chr(10) &
IIf(orders!delivery_suburb='','',orders!delivery_suburb & Chr(13) &
Chr(10)) & orders!delivery_postcode & " " & orders!delivery_city &
Chr(13) & Chr(10) & orders!delivery_country AS Levadresse,
orders.customers_telephone, orders.customers_email_address,
products_extra.products_model, products_extra.contain_product,
[number]*[products_quantity] AS pack_quantity, products_extra.Stockcode,
orders.payment_method, lang_texts.return_address,
IIf(orders.payment_method="Talong","Giroslip",IIf(orders.payment_method
Like "*Zahlschein*","Zahlschein","")) AS Giroslip,
[pack_quantity]*[unit_weight] AS pack_weight, orders.carrier,
IIf([carrier]="RM","Royal Mail Airmail","DHL") AS form_carrier,
IIf([pack_weight]<40,0,[pack_quantity]) AS pack_bottles,
IIf([brochure]<>'',"Leaflet: " & [brochure],'') AS brochuretext,
products_extra.nomenclature, lang_texts.nomenclature_text,
lang_texts.invoice_attached, (SELECT COUNT(date_purchased) FROM orders
AS orders1 WHERE orders1.date_purchased < orders.date_purchased AND
orders1.customers_id = orders.customers_id) AS PreviousOrders
FROM orders_suppl_onum INNER JOIN (products_extra INNER JOIN ((countries
INNER JOIN (orders INNER JOIN orders_products ON orders.orders_id =
orders_products.orders_id) ON countries.countries_name =
orders.delivery_country) INNER JOIN lang_texts ON countries.lang_code =
lang_texts.language_id) ON products_extra.products_model =
orders_products.products_model) ON orders_suppl_onum.suppl_onum =
orders.suppl_onum
ORDER BY orders.orders_id, products_extra.contain_product;
And if that query is based on another query, trace that back up as well.
None

Subqueries are notorious. Presumably you've already tried a
compact/repair in case it's just a bad index.
Click to expand...
Did so on the access database, and also the MySQL database to whom
MS-Access is linked. In fact all data for this particular query is
coming from a MySQL database. All links have been refreshed, and the
MySQL database itself has been repaired/optimized. All this has not helped.
What version of Access is this? Locate msaccess.exe on your drive
(typically in c:\Program Files\Microsoft Office\Office), right-click and
choose Properties. The Version tab gives full info. 10.0.6771.0

Also, what version of JET? Locate msjet40.dll (typically in
c:\windows\system32), and let us know the version.
Click to expand...
4.0.8618.0
 
Okay, good: the crashes have stopped.

The multi-level group-by is a very common problems with reports based on a
query with a subquery. Here's a brief explanation and some alternatives:
http://allenbrowne.com/subquery-02.html#MultiLevelGroupBy

Your Office XP and JET service packs are up to date.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

lunde said:
Thanks Allen. Please see my answers below. After your reply I made a small
adjustment to my query, which has resulted in that MS-Access does no
longer crash but reports this error in stead: "Multi-level GROUP BY clause
is not allowed in a sub-query". Still, the query works as expected, but
when putting the field "PreviousOrders" into the report the above error
appears.

Post the main query as well.SELECT orders.orders_id, orders.suppl_onum, orders.suppl_note,
orders.date_purchased, orders.delivery_name, orders.delivery_company,
orders.delivery_street_address, orders.delivery_suburb,
orders.delivery_city, orders.delivery_postcode, orders.delivery_country,
orders.eu_vat, lang_texts.ship_to, lang_texts.items, lang_texts.item_no,
orders_products.orders_products_id,
IIf(orders!delivery_company='','',orders!delivery_company & Chr(13) &
Chr(10)) & orders!delivery_name & Chr(13) & Chr(10) &
orders!delivery_street_address & Chr(13) & Chr(10) &
IIf(orders!delivery_suburb='','',orders!delivery_suburb & Chr(13) &
Chr(10)) & orders!delivery_postcode & " " & orders!delivery_city &
Chr(13) & Chr(10) & orders!delivery_country AS Levadresse,
orders.customers_telephone, orders.customers_email_address,
products_extra.products_model, products_extra.contain_product,
[number]*[products_quantity] AS pack_quantity, products_extra.Stockcode,
orders.payment_method, lang_texts.return_address,
IIf(orders.payment_method="Talong","Giroslip",IIf(orders.payment_method
Like "*Zahlschein*","Zahlschein","")) AS Giroslip,
[pack_quantity]*[unit_weight] AS pack_weight, orders.carrier,
IIf([carrier]="RM","Royal Mail Airmail","DHL") AS form_carrier,
IIf([pack_weight]<40,0,[pack_quantity]) AS pack_bottles,
IIf([brochure]<>'',"Leaflet: " & [brochure],'') AS brochuretext,
products_extra.nomenclature, lang_texts.nomenclature_text,
lang_texts.invoice_attached, (SELECT COUNT(date_purchased) FROM orders AS
orders1 WHERE orders1.date_purchased < orders.date_purchased AND
orders1.customers_id = orders.customers_id) AS PreviousOrders
FROM orders_suppl_onum INNER JOIN (products_extra INNER JOIN ((countries
INNER JOIN (orders INNER JOIN orders_products ON orders.orders_id =
orders_products.orders_id) ON countries.countries_name =
orders.delivery_country) INNER JOIN lang_texts ON countries.lang_code =
lang_texts.language_id) ON products_extra.products_model =
orders_products.products_model) ON orders_suppl_onum.suppl_onum =
orders.suppl_onum
ORDER BY orders.orders_id, products_extra.contain_product;
And if that query is based on another query, trace that back up as well.
None

Subqueries are notorious. Presumably you've already tried a
compact/repair in case it's just a bad index.
Click to expand...
Did so on the access database, and also the MySQL database to whom
MS-Access is linked. In fact all data for this particular query is coming
from a MySQL database. All links have been refreshed, and the MySQL
database itself has been repaired/optimized. All this has not helped.
What version of Access is this? Locate msaccess.exe on your drive
(typically in c:\Program Files\Microsoft Office\Office), right-click and
choose Properties. The Version tab gives full info. 10.0.6771.0

Also, what version of JET? Locate msjet40.dll (typically in
c:\windows\system32), and let us know the version.
Click to expand...
4.0.8618.0
Click to expand...
 
lunde said:
Thank you very much Allen - works perfect now :)
Click to expand...
One issue though: If I print out PreviousOrders it correctly displays
the number of orders this particular customer has previously placed. But
if I try to include this code in an unbound texts control source
=IIf([previousOrders]=0;[brochuretext];"")

Then it returns #error. Is this for the same reason as before mentioned?
 
To get that text box to work you would need:
- a control named previousOrders
- a control named brochuretext

In a report, just having a field in the RecordSource is not enough: you need
a control bound to that field, or the optimizer sometimes doesn't bother
fetching the data for the field.

Additionally the Name of this calculated control cannot be the same as the
name of any field. For example, it must not be named previousOrders or
brochuretext.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

lunde said:
lunde said:
Thank you very much Allen - works perfect now :)
Click to expand...
One issue though: If I print out PreviousOrders it correctly displays the
number of orders this particular customer has previously placed. But if I
try to include this code in an unbound texts control source
=IIf([previousOrders]=0;[brochuretext];"")

Then it returns #error. Is this for the same reason as before mentioned?
Click to expand...
 
Allen said:
To get that text box to work you would need:
- a control named previousOrders
- a control named brochuretext

In a report, just having a field in the RecordSource is not enough: you
need a control bound to that field, or the optimizer sometimes doesn't
bother fetching the data for the field.

Additionally the Name of this calculated control cannot be the same as
the name of any field. For example, it must not be named previousOrders
or brochuretext.
Click to expand...
Must admit that this exceed my processing capacity ;) What I want to
achieve is simply to output the value of the field brochuretext if it is
a new customer, otherwise blank. I'm not quite sure about the definition
of a control in Access.
 
A "control" means a text box (or a combo, or a check box, or ...)

Put text boxes on the report for those fields.
Hide them if you wish. (Visible property = No.)
 
Back
Top