Simply confused - Query stopped working

[QB]

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

 

[Jeff Boyce]

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.

 

[KARL DEWEY]

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.

 

[QB]

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]

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.

 

[John Spencer]

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

 

[QB]

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]

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.

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

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.




.