If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Simply confused - Query stopped working
I have a db that is on it's 3rd year of usage and development. A while back
I created a complex report built upon a set of queries. It has always worked without issue. I recently did some work to the db, but in no way touch this report, queries or underlying tables, and now I it won't work. I traced the issue to the queries. In a first queries I have a field that is a calculated field (simple + - /). The first query runs fine. Now I have a 2nd query built upon the first in which I reference this above mentioned calculated field. For some reason, the 2nd query spits out that the expression is too complex... If I remove the field, it works. The field is also used in another field in an iif () expression and that works fine, but simple refering to it alone, generates this error. How can it be too complexe since it gets calculated without issue in the first Query and since I can use it in a iif() expression? What is going on here? Anyone have any ideas?! Thank you, QB |
#2
|
|||
|
|||
Simply confused - Query stopped working
Your first line of defense/fall back is to restore these queries from your
most recent backup ... you DO have backups, right? If not, a second option would be to scrap both queries and rebuild them, and see if the new version works (Access sometimes suffers subtle corruption ... it can be faster to throw out the old and start over). Another approach would be to (first, make a backup!) use Compact & Repair to see if this finds/fixes the issue. Is your db compiled? Open a module, use Debug, and compile it if it isn't. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "QB" wrote in message ... I have a db that is on it's 3rd year of usage and development. A while back I created a complex report built upon a set of queries. It has always worked without issue. I recently did some work to the db, but in no way touch this report, queries or underlying tables, and now I it won't work. I traced the issue to the queries. In a first queries I have a field that is a calculated field (simple + - /). The first query runs fine. Now I have a 2nd query built upon the first in which I reference this above mentioned calculated field. For some reason, the 2nd query spits out that the expression is too complex... If I remove the field, it works. The field is also used in another field in an iif () expression and that works fine, but simple refering to it alone, generates this error. How can it be too complexe since it gets calculated without issue in the first Query and since I can use it in a iif() expression? What is going on here? Anyone have any ideas?! Thank you, QB |
#3
|
|||
|
|||
Simply confused - Query stopped working
What is going on here?
Hard to tell without see the calculated field construction and how it is used in the follow on query. Post the SQL of both and sample data. -- Build a little, test a little. "QB" wrote: I have a db that is on it's 3rd year of usage and development. A while back I created a complex report built upon a set of queries. It has always worked without issue. I recently did some work to the db, but in no way touch this report, queries or underlying tables, and now I it won't work. I traced the issue to the queries. In a first queries I have a field that is a calculated field (simple + - /). The first query runs fine. Now I have a 2nd query built upon the first in which I reference this above mentioned calculated field. For some reason, the 2nd query spits out that the expression is too complex... If I remove the field, it works. The field is also used in another field in an iif () expression and that works fine, but simple refering to it alone, generates this error. How can it be too complexe since it gets calculated without issue in the first Query and since I can use it in a iif() expression? What is going on here? Anyone have any ideas?! Thank you, QB |
#4
|
|||
|
|||
Simply confused - Query stopped working
Jeff,
I learnt the hard way about backups a long time ago!!! I wasn't too hot to the idea of going back to my last backup as I was just ready to release a new version, so I'd be loosing a lot of work, but I finally broke down. BUT! Now I seem to have a bigger problem on my hands. I went to my backup, same error? I went back 15 versions, and same error!!? I know for a fact that it worked! So what the... I've done the compact and repair on both the back and front-ends, I even did a decompile and recompile, compact .... Nothing. I can post the SQL if you wish, but I am more convinced than ever that it has nothing to do with the problem now. Any more ideas? QB "Jeff Boyce" wrote: Your first line of defense/fall back is to restore these queries from your most recent backup ... you DO have backups, right? If not, a second option would be to scrap both queries and rebuild them, and see if the new version works (Access sometimes suffers subtle corruption ... it can be faster to throw out the old and start over). Another approach would be to (first, make a backup!) use Compact & Repair to see if this finds/fixes the issue. Is your db compiled? Open a module, use Debug, and compile it if it isn't. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "QB" wrote in message ... I have a db that is on it's 3rd year of usage and development. A while back I created a complex report built upon a set of queries. It has always worked without issue. I recently did some work to the db, but in no way touch this report, queries or underlying tables, and now I it won't work. I traced the issue to the queries. In a first queries I have a field that is a calculated field (simple + - /). The first query runs fine. Now I have a 2nd query built upon the first in which I reference this above mentioned calculated field. For some reason, the 2nd query spits out that the expression is too complex... If I remove the field, it works. The field is also used in another field in an iif () expression and that works fine, but simple refering to it alone, generates this error. How can it be too complexe since it gets calculated without issue in the first Query and since I can use it in a iif() expression? What is going on here? Anyone have any ideas?! Thank you, QB . |
#5
|
|||
|
|||
Simply confused - Query stopped working
You've done all the right things, from my point of view.
Is there any chance your PC has ... "enjoyed" ... a recent update/patch? (and by the way, next time you don't need to restore an earlier version of the entire app. It would be sufficient to import a copy of the query from an earlier version, then rename accordingly...) Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "QB" wrote in message ... Jeff, I learnt the hard way about backups a long time ago!!! I wasn't too hot to the idea of going back to my last backup as I was just ready to release a new version, so I'd be loosing a lot of work, but I finally broke down. BUT! Now I seem to have a bigger problem on my hands. I went to my backup, same error? I went back 15 versions, and same error!!? I know for a fact that it worked! So what the... I've done the compact and repair on both the back and front-ends, I even did a decompile and recompile, compact .... Nothing. I can post the SQL if you wish, but I am more convinced than ever that it has nothing to do with the problem now. Any more ideas? QB "Jeff Boyce" wrote: Your first line of defense/fall back is to restore these queries from your most recent backup ... you DO have backups, right? If not, a second option would be to scrap both queries and rebuild them, and see if the new version works (Access sometimes suffers subtle corruption ... it can be faster to throw out the old and start over). Another approach would be to (first, make a backup!) use Compact & Repair to see if this finds/fixes the issue. Is your db compiled? Open a module, use Debug, and compile it if it isn't. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "QB" wrote in message ... I have a db that is on it's 3rd year of usage and development. A while back I created a complex report built upon a set of queries. It has always worked without issue. I recently did some work to the db, but in no way touch this report, queries or underlying tables, and now I it won't work. I traced the issue to the queries. In a first queries I have a field that is a calculated field (simple + - /). The first query runs fine. Now I have a 2nd query built upon the first in which I reference this above mentioned calculated field. For some reason, the 2nd query spits out that the expression is too complex... If I remove the field, it works. The field is also used in another field in an iif () expression and that works fine, but simple refering to it alone, generates this error. How can it be too complexe since it gets calculated without issue in the first Query and since I can use it in a iif() expression? What is going on here? Anyone have any ideas?! Thank you, QB . |
#6
|
|||
|
|||
Simply confused - Query stopped working
As a guess, your first query is producing an error on some field. And then
you are attempting in the second query to filter on that field. Perhaps somewhere in the first query you are getting a divide by zero error? John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County KARL DEWEY wrote: What is going on here? Hard to tell without see the calculated field construction and how it is used in the follow on query. Post the SQL of both and sample data. |
#7
|
|||
|
|||
Simply confused - Query stopped working
I've isolated the problem, but don't understand. Perhaps you have an
explanation. Here is my sql for the 2 queries (and yes I know there are issues with naming conventions, table structure... - I took over another developer mess - One problem at a time) SELECT [900-PARAMETRE].[900-01_NUMÉRO] AS [Numéro de projet], [900-PARAMETRE].[900-02_NOM], IIf([900-PARAMETRE].[Type A]=True,"A",IIf([900-PARAMETRE].[Type B]=True,"B","C")) AS Type, [900-PARAMETRE].[910 STATU] AS Statut, [900-PARAMETRE].[900-05_DEBUT DU PROJET], [900-PARAMETRE].[900-07_LIVRAISON REELLE], [900-PARAMETRE].dtStatut, [900-PARAMETRE].[900-03_CHARGÉ DE PROJET] AS [Chargé de projet], ((([Prix de vente]+[Facture des chgs])-([Total ETC]+[Total AC]))/([Prix de vente]+[Facture des chgs]))-((([Prix de vente]+[Facture des chgs])-([Coutant]+[Coutant des chgs]))/([Prix de vente]+[Facture des chgs])) AS Marge FROM [900-PARAMETRE] INNER JOIN qry_Dashboard ON [900-PARAMETRE].[900-01_NUMÉRO] = qry_Dashboard.[Numéro de Projet] WHERE ((([900-PARAMETRE].[900-01_NUMÉRO]) Is Not Null)) ORDER BY [900-PARAMETRE].[900-01_NUMÉRO]; SELECT qry_rpt_RespectMarge01.[Numéro de projet], qry_rpt_RespectMarge01.[900-02_NOM], qry_rpt_RespectMarge01.Type, Year([900-05_DEBUT DU PROJET]) AS Livrable, qry_rpt_RespectMarge01.[900-07_LIVRAISON REELLE], qry_rpt_RespectMarge01.dtStatut, qry_rpt_RespectMarge01.[Chargé de projet], qry_rpt_RespectMarge01.Marge, CouleurMarge([Marge]) AS [Couleur Marge] FROM qry_rpt_RespectMarge01 WHERE (((Year([900-05_DEBUT DU PROJET]))=[Forms]![frm_rpt_RespectMarge]![cbo_Livrable]) AND ((qry_rpt_RespectMarge01.[Chargé de projet]) Like IIf([Forms]![frm_rpt_RespectMarge]![cbo_Charge]="Tous","*",[Forms]![frm_rpt_RespectMarge]![cbo_Charge])) AND ((qry_rpt_RespectMarge01.Marge) Is Not Null) AND ((qry_rpt_RespectMarge01.Statut) In ("Fermé"))); After some troubeshooting I now know that the issue is with the ((qry_rpt_RespectMarge01.Marge) Is Not Null) part of the query. If I remove it, it runs fine. If I put "", it runs fine. It is specifically because of the Is Not Null. Why would this pose a problem? Thank you for your help. QB "Jeff Boyce" wrote: Your first line of defense/fall back is to restore these queries from your most recent backup ... you DO have backups, right? If not, a second option would be to scrap both queries and rebuild them, and see if the new version works (Access sometimes suffers subtle corruption ... it can be faster to throw out the old and start over). Another approach would be to (first, make a backup!) use Compact & Repair to see if this finds/fixes the issue. Is your db compiled? Open a module, use Debug, and compile it if it isn't. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "QB" wrote in message ... I have a db that is on it's 3rd year of usage and development. A while back I created a complex report built upon a set of queries. It has always worked without issue. I recently did some work to the db, but in no way touch this report, queries or underlying tables, and now I it won't work. I traced the issue to the queries. In a first queries I have a field that is a calculated field (simple + - /). The first query runs fine. Now I have a 2nd query built upon the first in which I reference this above mentioned calculated field. For some reason, the 2nd query spits out that the expression is too complex... If I remove the field, it works. The field is also used in another field in an iif () expression and that works fine, but simple refering to it alone, generates this error. How can it be too complexe since it gets calculated without issue in the first Query and since I can use it in a iif() expression? What is going on here? Anyone have any ideas?! Thank you, QB . |
#8
|
|||
|
|||
Simply confused - Query stopped working
That would be the next place I'd check ... has anybody entered any data into
the database since it used to work? Access knows the difference among a null (nothing there), a zero-length string (""), and one/more spaces. People, on the other hand, can't readily tell the difference by looking at the field. If your query doesn't accommodate all of these, I can imagine Access getting confused... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "QB" wrote in message ... I've isolated the problem, but don't understand. Perhaps you have an explanation. Here is my sql for the 2 queries (and yes I know there are issues with naming conventions, table structure... - I took over another developer mess - One problem at a time) SELECT [900-PARAMETRE].[900-01_NUMÉRO] AS [Numéro de projet], [900-PARAMETRE].[900-02_NOM], IIf([900-PARAMETRE].[Type A]=True,"A",IIf([900-PARAMETRE].[Type B]=True,"B","C")) AS Type, [900-PARAMETRE].[910 STATU] AS Statut, [900-PARAMETRE].[900-05_DEBUT DU PROJET], [900-PARAMETRE].[900-07_LIVRAISON REELLE], [900-PARAMETRE].dtStatut, [900-PARAMETRE].[900-03_CHARGÉ DE PROJET] AS [Chargé de projet], ((([Prix de vente]+[Facture des chgs])-([Total ETC]+[Total AC]))/([Prix de vente]+[Facture des chgs]))-((([Prix de vente]+[Facture des chgs])-([Coutant]+[Coutant des chgs]))/([Prix de vente]+[Facture des chgs])) AS Marge FROM [900-PARAMETRE] INNER JOIN qry_Dashboard ON [900-PARAMETRE].[900-01_NUMÉRO] = qry_Dashboard.[Numéro de Projet] WHERE ((([900-PARAMETRE].[900-01_NUMÉRO]) Is Not Null)) ORDER BY [900-PARAMETRE].[900-01_NUMÉRO]; SELECT qry_rpt_RespectMarge01.[Numéro de projet], qry_rpt_RespectMarge01.[900-02_NOM], qry_rpt_RespectMarge01.Type, Year([900-05_DEBUT DU PROJET]) AS Livrable, qry_rpt_RespectMarge01.[900-07_LIVRAISON REELLE], qry_rpt_RespectMarge01.dtStatut, qry_rpt_RespectMarge01.[Chargé de projet], qry_rpt_RespectMarge01.Marge, CouleurMarge([Marge]) AS [Couleur Marge] FROM qry_rpt_RespectMarge01 WHERE (((Year([900-05_DEBUT DU PROJET]))=[Forms]![frm_rpt_RespectMarge]![cbo_Livrable]) AND ((qry_rpt_RespectMarge01.[Chargé de projet]) Like IIf([Forms]![frm_rpt_RespectMarge]![cbo_Charge]="Tous","*",[Forms]![frm_rpt_RespectMarge]![cbo_Charge])) AND ((qry_rpt_RespectMarge01.Marge) Is Not Null) AND ((qry_rpt_RespectMarge01.Statut) In ("Fermé"))); After some troubeshooting I now know that the issue is with the ((qry_rpt_RespectMarge01.Marge) Is Not Null) part of the query. If I remove it, it runs fine. If I put "", it runs fine. It is specifically because of the Is Not Null. Why would this pose a problem? Thank you for your help. QB "Jeff Boyce" wrote: Your first line of defense/fall back is to restore these queries from your most recent backup ... you DO have backups, right? If not, a second option would be to scrap both queries and rebuild them, and see if the new version works (Access sometimes suffers subtle corruption ... it can be faster to throw out the old and start over). Another approach would be to (first, make a backup!) use Compact & Repair to see if this finds/fixes the issue. Is your db compiled? Open a module, use Debug, and compile it if it isn't. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "QB" wrote in message ... I have a db that is on it's 3rd year of usage and development. A while back I created a complex report built upon a set of queries. It has always worked without issue. I recently did some work to the db, but in no way touch this report, queries or underlying tables, and now I it won't work. I traced the issue to the queries. In a first queries I have a field that is a calculated field (simple + - /). The first query runs fine. Now I have a 2nd query built upon the first in which I reference this above mentioned calculated field. For some reason, the 2nd query spits out that the expression is too complex... If I remove the field, it works. The field is also used in another field in an iif () expression and that works fine, but simple refering to it alone, generates this error. How can it be too complexe since it gets calculated without issue in the first Query and since I can use it in a iif() expression? What is going on here? Anyone have any ideas?! Thank you, QB . |
Thread Tools | |
Display Modes | |
|
|