Re: Simply confused - Query stopped working

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <QB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FB53EFC9-FF15-4F7F-A5E9-63CFACC6CC2B@xxxxxxxxxxxxxxxx
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


.

.

Relevant Pages

  • Re: Simply confused - Query stopped working
    ... This author may have received products and services mentioned ... I wasn't too hot to the idea of going back to my last backup as I was just ... I created a complex report built upon a set of queries. ... The first query runs fine. ...
    (microsoft.public.access.queries)
  • RE: Dynamically referencing a recordset?
    ... through that recordset to set the query parameters in VBA. ... to be the one who had to modify 1440 queries if there is a change in the ... different table schema, then you need 3, one for each record type. ... This is a database that tracks the production on records in another ...
    (microsoft.public.access.modulesdaovba)
  • Re: My query is empty. How do I reestabish relationships to add d
    ... Following is the SQL and a little other information on the 2 queries ... Following the Query is some information on the Customners ... Backup, backup, backup! ... consider posting the SQL statement of the query that isn't ...
    (microsoft.public.access.queries)
  • Re: Was: what does "serialization" mean?
    ... > the specific queries up front. ... that the NEXT time the query is run the query is fast, ... data base, don't know squat" when in fact the Donald clone doesn't ... >> WHATEVER would probably be a separate query to summarize total sales. ...
    (comp.programming)
  • Re: Official Status of SQLServer 2005 ADP
    ... I have said that the support for SQL passthrough ... queries under MDB was bad and worst than the one offered by ADP while you ... > attempt to "pass through" every Access query against a linked ODBC ...
    (microsoft.public.access.adp.sqlserver)