How do I temporarily limit values in a query field

B

BobC

For testing purposes, I want to restrict the query to a range of
numerical values e.g. <4999.
I get error messages when I try to type <4999 in the criteria field.
The field contains an expression does have an alias if that makes a
difference? e.g. Incurred: IIf([COV]="GENL" ...,...,...)
 
B

BobC

That's what I get for trying trying to abbreviate things ...

Field: Incurred: IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+NZ([PDO],0),IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0)))

Table: (blank)
Total: Expression
Sort: (blank)
Show: (checked)
Criteria: >500

I get and 'Enter Parameter Value' dialog box asking for a value for COV

The field normally returns a numberic value.

This type of error does not occur if I put something like >500 in other
fields that do not have an alias?

Hope this is a better explanation.
Thanks,
Bob



works fine until I try to put limits on it value in the qryClaimDetails
query.


Marshall said:
BobC said:
For testing purposes, I want to restrict the query to a range of
numerical values e.g. <4999.
I get error messages when I try to type <4999 in the criteria field.
The field contains an expression does have an alias if that makes a
difference? e.g. Incurred: IIf([COV]="GENL" ...,...,...)
Click to expand...


It would help if you would post the error message you
received. I'll take a guess that it was a type mismatch
error. If that's what you got, then the IIf expresion,
which you also failed to Copy/Paste into your post, is
resulting in a text value instead of a number.
Click to expand...
 
M

Marshall Barton

BobC said:
For testing purposes, I want to restrict the query to a range of
numerical values e.g. <4999.
I get error messages when I try to type <4999 in the criteria field.
The field contains an expression does have an alias if that makes a
difference? e.g. Incurred: IIf([COV]="GENL" ...,...,...)
Click to expand...


It would help if you would post the error message you
received. I'll take a guess that it was a type mismatch
error. If that's what you got, then the IIf expresion,
which you also failed to Copy/Paste into your post, is
resulting in a text value instead of a number.
 
J

John Spencer

If Cov is a calculated field, you will either need to repeat the calculation
each time you reference COV or you will need to use the current query
(without incurred) as the source for another query. When you do that Cov
will be available to make your calculations.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

BobC said:
That's what I get for trying trying to abbreviate things ...

Field: Incurred: IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+NZ([PDO],0),IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0)))
Table: (blank)
Total: Expression
Sort: (blank)
Show: (checked)
Criteria: >500

I get and 'Enter Parameter Value' dialog box asking for a value for COV

The field normally returns a numberic value.

This type of error does not occur if I put something like >500 in other
fields that do not have an alias?

Hope this is a better explanation.
Thanks,
Bob



works fine until I try to put limits on it value in the qryClaimDetails
query.


Marshall said:
BobC said:
For testing purposes, I want to restrict the query to a range of
numerical values e.g. <4999.
I get error messages when I try to type <4999 in the criteria field.
The field contains an expression does have an alias if that makes a
difference? e.g. Incurred: IIf([COV]="GENL" ...,...,...)
Click to expand...


It would help if you would post the error message you
received. I'll take a guess that it was a type mismatch
error. If that's what you got, then the IIf expresion,
which you also failed to Copy/Paste into your post, is
resulting in a text value instead of a number.
Click to expand...
Click to expand...
 
B

BobC

I do not fully understand what you are saying?

COV is another field in the query ... as is [Curries] and [PAIDLOSS] ...
which also pop up as a 'Enter Parameter Value' dialog box if I just
stuff an answer in for COV just to see what happens next.

Are you saying that I cannot or not easily, temporarily limit the query
based on the Incurred field as it is?

Just to make sure I am not getting off track ...
During a testing phase, I was trying to limit the number of records to
just the larger values of Field: Incurred: IIf([COV]="GENL" Or ...

I am willing to go another route; I just figured I was doing something
wrong/stupid in the way I was putting something like >500 in a query field?

Thanks,
Bob
 
B

BobC

In an attempt to learn new things and to utilize the IN operator ... I
am getting an error message: "The expression you entered has a invalid
..(Dot)or ! operator or invalid parenthesis."

The SQL is below:
Incurred: IIf([COV] IN("GENL"."AUTOLIAB"."D&O"."EPL")
,Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0),IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0))

On the original issue ... Don't know if this will help ... This is the
last few lines of the 'old' sql with an attempt to limit the records to
values of <500:

qryClaimDetails_C.MHAPTFDED, qryClaimDetails_C.MHAPDPLDED,
qryClaimDetails_C.MHAPDED
HAVING (((IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0),IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0))))<500))
ORDER BY qryClaimDetails_C.DACC DESC , qryClaimDetails_C.SUBIND;








Marshall said:
BobC said:
That's what I get for trying trying to abbreviate things ...

Field: Incurred: IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+NZ([PDO],0),
IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0)))

Table: (blank)
Total: Expression
Sort: (blank)
Show: (checked)
Criteria: >500

I get and 'Enter Parameter Value' dialog box asking for a value for COV

The field normally returns a numberic value.

This type of error does not occur if I put something like >500 in other
fields that do not have an alias?

works fine until I try to put limits on it value in the qryClaimDetails
query.
Click to expand...


Assuming COV really is a field in the query's table, I don't
see anything in there that would cause that error. Maybe
you type it wrong somewhere in the query (e.g. with an extra
space?)

It/s also possible that you messed up the GROUP BY clause,
but without knowing why you are using a Totals type query,
or what the SELECT, GROUP BY and WHERE clauses look like, I
can't tell what's going on there.

It would make that expression a little easier to read if you
used the IN operator:

IIf(COV IN("GENL"."AUTOLIAB"."D&O"."EPL"),
Nz(CURRES,0)+Nz(PDLOSS,0)+NZ(PDO,0)+Nz(PDLegal,0),
IIf(COV="AUTOPHY",
Nz(CURRES,0)+Nz(PDLOSS,0)+Nz(PDO,0),
Nz(CURRES,0)+Nz(PDLOSS,0)))

Trying to express a query's design view in a text message is
rather messy at best. Far better to Copy/Paste the query's
SQL view.
Click to expand...
 
M

Marshall Barton

BobC said:
That's what I get for trying trying to abbreviate things ...

Field: Incurred: IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+NZ([PDO],0),
IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0)))

Table: (blank)
Total: Expression
Sort: (blank)
Show: (checked)
Criteria: >500

I get and 'Enter Parameter Value' dialog box asking for a value for COV

The field normally returns a numberic value.

This type of error does not occur if I put something like >500 in other
fields that do not have an alias?

works fine until I try to put limits on it value in the qryClaimDetails
query.
Click to expand...


Assuming COV really is a field in the query's table, I don't
see anything in there that would cause that error. Maybe
you type it wrong somewhere in the query (e.g. with an extra
space?)

It/s also possible that you messed up the GROUP BY clause,
but without knowing why you are using a Totals type query,
or what the SELECT, GROUP BY and WHERE clauses look like, I
can't tell what's going on there.

It would make that expression a little easier to read if you
used the IN operator:

IIf(COV IN("GENL"."AUTOLIAB"."D&O"."EPL"),
Nz(CURRES,0)+Nz(PDLOSS,0)+NZ(PDO,0)+Nz(PDLegal,0),
IIf(COV="AUTOPHY",
Nz(CURRES,0)+Nz(PDLOSS,0)+Nz(PDO,0),
Nz(CURRES,0)+Nz(PDLOSS,0)))

Trying to express a query's design view in a text message is
rather messy at best. Far better to Copy/Paste the query's
SQL view.
 
J

John Spencer

Can you post the SQL of the query?

If you are getting parameter prompts for the fields, that means Access does
not understand what those fields are. Normally they are a misspelling or
they don't exist in the tables that the query is using.

I think I misunderstood something you were saying, so ignore my earlier
response.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

BobC said:
I do not fully understand what you are saying?

COV is another field in the query ... as is [Curries] and [PAIDLOSS] ...
which also pop up as a 'Enter Parameter Value' dialog box if I just stuff
an answer in for COV just to see what happens next.

Are you saying that I cannot or not easily, temporarily limit the query
based on the Incurred field as it is?

Just to make sure I am not getting off track ...
During a testing phase, I was trying to limit the number of records to
just the larger values of Field: Incurred: IIf([COV]="GENL" Or ...

I am willing to go another route; I just figured I was doing something
wrong/stupid in the way I was putting something like >500 in a query
field?

Thanks,
Bob

John said:
If Cov is a calculated field, you will either need to repeat the
calculation each time you reference COV or you will need to use the
current query (without incurred) as the source for another query. When
you do that Cov will be available to make your calculations.
Click to expand...
Click to expand...
 
B

BobC

This is a huge query ... hope this goes!

SELECT qryClaimDetails_C.[CLAIM#], qryClaimDetails_C.DACC,
qryClaimDetails_C.POLYR, qryClaimDetails_C.COVERAGE AS COV,
qryClaimDetails_C.SUBIND, qryClaimDetails_C.HA,
qryClaimDetails_C.HANAME, qryClaimDetails_C.DESCR,
qryClaimDetails_C.STATUS, qryClaimDetails_C.DRPT,
qryClaimDetails_C.CLAIMANT, qryClaimDetails_A.SumOfPDLEGAL AS PDLEGAL,
qryClaimDetails_A.SumOfPDLOSS AS PDLOSS,
qryClaimDetails_A.SumOfPDOTHEREXP AS PDO, qryClaimDetails_C.CURRES,
qryClaimDetails_C.DCURRES, qryClaimDetails_C.RESCHG, IIf([COV]="GENL" Or
[COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0),IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0)))
AS Incurred, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>0,Nz([MEMDED],0),0) AS MEMDED_PROP,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=Nz([TFDED],0),Nz([TFDED],0),0) AS MEMDED_TF,
IIf(Nz([Incurred],0)>0 And Nz([COV],0)="AUTOPHY",Nz([AUTOPHYDED],0),0)
AS MEMDED_AUTOPHY,
Nz([MEMDED_PROP],0)+Nz([MEMDED_TF],0)+Nz([MEMDED_AUTOPHY],0) AS
MEMDED_TOTAL, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)<Nz([MHAPDED],0),Nz([Incurred],0),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)<>"TF",Nz([MHAPDED],0),0)) AS MHAPDED_PROP,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)<Nz([MHAPTFDED],0),Nz([Incurred],0),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)="TF",Nz([MHAPTFDED],0),0)) AS MHAPDED_TF,
Nz([MHAPDED_PROP],0)+Nz([MHAPDED_TF],0) AS MHAPDED_TOTAL,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=(Nz([TFDED],0)+Nz([MHAPDED],0)) And
Nz([Incurred],0)<=((Nz([TFDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([Incurred],0)-(Nz([TFDED],0)-Nz([MHAPDED],0)),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=((Nz([TFDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([PROPRET],0),0))
AS PLSA_TF, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=(Nz([MEMDED],0)+Nz([MHAPDED],0)) And
Nz([Incurred],0)<=((Nz([MEMDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([Incurred],0)-(Nz([MEMDED],0)+Nz([MHAPDED],0)),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=((Nz([MEMDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([PROPRET],0),0))
AS PLSA_PROP, Nz([PLSA_TF],0)+Nz([PLSA_PROP],0) AS PLSA_TOTAL,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=(Nz([PROPRET],0)+Nz([TFDED],0)+Nz([MEMDED],0)),Nz([Incurred],0)-(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([TFDED],0)))
AS EXPL_TF, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([MEMDED],0)),Nz([Incurred],0)-(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([MEMDED],0)))
AS EXPL_PROP, 0 AS MEM1_GL, IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or
[COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>=Nz([LIABRET],0),Nz([LIABRET],0),0) AS MHAP_GL,
IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>Nz([LIABRET],0) And
Nz([Incurred],0)<=2000000,Nz([Incurred],0)-Nz([LIABRET],0),IIf(([COV]="GENL"
Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>=2000000,2000000-Nz([LIABRET],0),0)) AS EXLL_GL,
IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>(2000000+Nz([LIABRET],0)),Nz([Incurred],0)-2000000,0)
AS MEM2_GL, IIf([COV]="EPL" And
Nz([Incurred],0)>=Nz([EPLDED],0),Nz([EPLDED],0),0) AS MEM1_EPL,
IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>Nz([EPLDED],0) And
Nz([Incurred],0)<=Nz([LIABRET],0),Nz([Incurred],0)-Nz([EPLDED],0),IIf(Nz([COV],0)="EPL"
And Nz([Incurred],0)>=Nz([LIABRET],0),Nz([LIABRET],0)-Nz([EPLDED],0),0))
AS MHAP_EPL, IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>Nz([LIABRET],0)
And
Nz([Incurred],0)<=2000000,Nz([Incurred],0)-Nz([LIABRET],0),IIf(Nz([COV],0)="EPL"
And Nz([Incurred],0)>=2000000,2000000-Nz([LIABRET],0),0)) AS EXLL_EPL,
IIf([COV]="EPL" And Nz([Incurred],0)>2000000,Nz([Incurred],0)-2000000,0)
AS MEM2_EPL,
IIf([COV]="PROP",Nz([PDLEGAL],0)+Nz([PDLOSS],0)+Nz([PDO],0)+Nz([CURRES],0)+Nz([MEMDED_PROP],0),0)
AS PropGndUp,
IIf([COV]="AUTOPHY",Nz([PDLEGAL],0)+Nz([PDLOSS],0)+Nz([PDO],0)+Nz([CURRES],0)+Nz([MEMDED_AUTOPHY],0),0)
AS AutoPhyGndUp, IIf([COV]<>"PROP" And [COV]<>"AUTOPHY",[Incurred],0) AS
OtherGndUp, [PropGNDUp]+[AutoPhyGndUp]+[OtherGndUp] AS GNDUP,
qryClaimDetails_C.RECNO, qryClaimDetails_C.MEMDED,
qryClaimDetails_C.PROPRET, qryClaimDetails_C.PROPAGG,
qryClaimDetails_C.AUTOPHYDED, qryClaimDetails_C.TFDED,
qryClaimDetails_C.LIABRET, qryClaimDetails_C.LIABAGG,
qryClaimDetails_C.LIABLIM, qryClaimDetails_C.EPLDED,
qryClaimDetails_C.MHAPTFDED, qryClaimDetails_C.MHAPDPLDED
FROM qryClaimDetails_A RIGHT JOIN qryClaimDetails_C ON
qryClaimDetails_A.[CLAIM#] = qryClaimDetails_C.[CLAIM#]
GROUP BY qryClaimDetails_C.[CLAIM#], qryClaimDetails_C.DACC,
qryClaimDetails_C.POLYR, qryClaimDetails_C.COVERAGE,
qryClaimDetails_C.SUBIND, qryClaimDetails_C.HA,
qryClaimDetails_C.HANAME, qryClaimDetails_C.DESCR,
qryClaimDetails_C.STATUS, qryClaimDetails_C.DRPT,
qryClaimDetails_C.CLAIMANT, qryClaimDetails_A.SumOfPDLEGAL,
qryClaimDetails_A.SumOfPDLOSS, qryClaimDetails_A.SumOfPDOTHEREXP,
qryClaimDetails_C.CURRES, qryClaimDetails_C.DCURRES,
qryClaimDetails_C.RESCHG, qryClaimDetails_C.RECNO,
qryClaimDetails_C.MEMDED, qryClaimDetails_C.PROPRET,
qryClaimDetails_C.PROPAGG, qryClaimDetails_C.AUTOPHYDED,
qryClaimDetails_C.TFDED, qryClaimDetails_C.LIABRET,
qryClaimDetails_C.LIABAGG, qryClaimDetails_C.LIABLIM,
qryClaimDetails_C.EPLDED, qryClaimDetails_C.MHAPTFDED,
qryClaimDetails_C.MHAPDPLDED, qryClaimDetails_C.MHAPDED
HAVING (((IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0),IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0))))>500))
ORDER BY qryClaimDetails_C.DACC DESC , qryClaimDetails_C.SUBIND;
 
J

John Spencer

And exactly as I said, you have aliased qryClaimDetails_C.COVERAGE as
COV and then tried to use that alias elsewhere in the query. Access
will sometimes allow you to do that in the SELECT clause, but will not
allow that alias to be used in a WHERE (or HAVING) clause or in an ORDER
BY clause.

Your best bet is to replace COV with Coverage as long as you don't have
a field named coverage in queryClaimDetails_A. OR Alias Coverage as COV
in the query qryClaimDetails_C.

You've done the same with Incurred which is dependent on Coverage.

The easiest way to work this out may be to generate another intermediate
query with Incurred calculated in the intermediate query and then use
that as the source to do all those convoluted IIF expressions.

By the way I don't see any reason for you to use a Group by clause. You
are not using any of the aggregate functions in this query.


SELECT DISTINCT
qryClaimDetails_C.[CLAIM#]
, qryClaimDetails_C.DACC
, qryClaimDetails_C.POLYR
, qryClaimDetails_C.COVERAGE AS COV
, qryClaimDetails_C.SUBIND
, qryClaimDetails_C.HA
, qryClaimDetails_C.HANAME
, qryClaimDetails_C.DESCR
, qryClaimDetails_C.STATUS
, qryClaimDetails_C.DRPT
, qryClaimDetails_C.CLAIMANT
, qryClaimDetails_A.SumOfPDLEGAL AS PDLEGAL
, qryClaimDetails_A.SumOfPDLOSS AS PDLOSS
, qryClaimDetails_A.SumOfPDOTHEREXP AS PDO
, qryClaimDetails_C.CURRES
, qryClaimDetails_C.DCURRES
, qryClaimDetails_C.RESCHG

, IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0)
,IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0)))
AS Incurred

============== REMOVE THIS ENTIRE SECTION ============================
============== Add it back in a query based on this query ==========
======================================================================
, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>0,Nz([MEMDED],0),0) AS MEMDED_PROP

, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=Nz([TFDED],0),Nz([TFDED],0),0) AS MEMDED_TF

, IIf(Nz([Incurred],0)>0 And Nz([COV],0)="AUTOPHY",Nz([AUTOPHYDED],0),0)
AS MEMDED_AUTOPHY

, Nz([MEMDED_PROP],0)+Nz([MEMDED_TF],0)+Nz([MEMDED_AUTOPHY],0) AS
MEMDED_TOTAL, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)<Nz([MHAPDED],0),Nz([Incurred],0),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)<>"TF",Nz([MHAPDED],0),0)) AS MHAPDED_PROP

, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)<Nz([MHAPTFDED],0),Nz([Incurred],0),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)="TF",Nz([MHAPTFDED],0),0)) AS MHAPDED_TF

, Nz([MHAPDED_PROP],0)+Nz([MHAPDED_TF],0) AS MHAPDED_TOTAL

, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=(Nz([TFDED],0)+Nz([MHAPDED],0)) And
Nz([Incurred],0)<=((Nz([TFDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0))
,Nz([Incurred],0)-(Nz([TFDED],0)-Nz([MHAPDED],0)),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=((Nz([TFDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([PROPRET],0),0))
AS PLSA_TF, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=(Nz([MEMDED],0)+Nz([MHAPDED],0)) And
Nz([Incurred],0)<=((Nz([MEMDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0))
,Nz([Incurred],0)-(Nz([MEMDED],0)+Nz([MHAPDED],0)),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=((Nz([MEMDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([PROPRET],0),0))
AS PLSA_PROP, Nz([PLSA_TF],0)+Nz([PLSA_PROP],0) AS PLSA_TOTAL

, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=(Nz([PROPRET],0)+Nz([TFDED],0)+Nz([MEMDED],0))
,Nz([Incurred],0)-(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([TFDED],0))) AS
EXPL_TF, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([MEMDED],0))
,Nz([Incurred],0)-(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([MEMDED],0))) AS
EXPL_PROP

, 0 AS MEM1_GL

, IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L")
And Nz([Incurred],0)>=Nz([LIABRET],0),Nz([LIABRET],0),0) AS MHAP_GL

, IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L")
And Nz([Incurred],0)>Nz([LIABRET],0) And
Nz([Incurred],0)<=2000000,Nz([Incurred],0)-Nz([LIABRET],0),IIf(([COV]="GENL"
Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>=2000000,2000000-Nz([LIABRET],0),0)) AS EXLL_GL,
IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>(2000000+Nz([LIABRET],0)),Nz([Incurred],0)-2000000,0)
AS MEM2_GL

, IIf([COV]="EPL" And Nz([Incurred],0)>=Nz([EPLDED],0),Nz([EPLDED],0),0)
AS MEM1_EPL, IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>Nz([EPLDED],0)
And Nz([Incurred],0)<=Nz([LIABRET],0),Nz([Incurred],0)
-Nz([EPLDED],0),IIf(Nz([COV],0)="EPL" And
Nz([Incurred],0)>=Nz([LIABRET],0),Nz([LIABRET],0)-Nz([EPLDED],0),0)) AS
MHAP_EPL

, IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>Nz([LIABRET],0) And
Nz([Incurred],0)<=2000000,Nz([Incurred],0)-Nz([LIABRET],0)
,IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>=2000000,2000000-
Nz([LIABRET],0),0)) AS EXLL_EPL, IIf([COV]="EPL" And
Nz([Incurred],0)>2000000,Nz([Incurred],0)-2000000,0) AS MEM2_EPL

, IIf([COV]="PROP",Nz([PDLEGAL],0)+Nz([PDLOSS],0)+Nz([PDO],0)
+Nz([CURRES],0)+Nz([MEMDED_PROP],0),0) AS PropGndUp

, IIf([COV]="AUTOPHY",Nz([PDLEGAL],0)+Nz([PDLOSS],0)+Nz([PDO],0)
+Nz([CURRES],0)+Nz([MEMDED_AUTOPHY],0),0) AS AutoPhyGndUp

, IIf([COV]<>"PROP" And [COV]<>"AUTOPHY",[Incurred],0) AS OtherGndUp,
[PropGNDUp]+[AutoPhyGndUp]+[OtherGndUp] AS GNDUP
============== REMOVE THIS ENTIRE SECTION ============================


, qryClaimDetails_C.RECNO
, qryClaimDetails_C.MEMDED
, qryClaimDetails_C.PROPRET
, qryClaimDetails_C.PROPAGG
, qryClaimDetails_C.AUTOPHYDED
, qryClaimDetails_C.TFDED
, qryClaimDetails_C.LIABRET
, qryClaimDetails_C.LIABAGG
, qryClaimDetails_C.LIABLIM
, qryClaimDetails_C.EPLDED
, qryClaimDetails_C.MHAPTFDED
, qryClaimDetails_C.MHAPDPLDED

FROM qryClaimDetails_A
RIGHT JOIN qryClaimDetails_C
ON qryClaimDetails_A.[CLAIM#] = qryClaimDetails_C.[CLAIM#]

================ DROP THIS ENTIRE BIT ====================
GROUP BY qryClaimDetails_C.[CLAIM#]
, qryClaimDetails_C.DACC
, qryClaimDetails_C.POLYR
, qryClaimDetails_C.COVERAGE
, qryClaimDetails_C.SUBIND
, qryClaimDetails_C.HA
, qryClaimDetails_C.HANAME
, qryClaimDetails_C.DESCR
, qryClaimDetails_C.STATUS
, qryClaimDetails_C.DRPT
, qryClaimDetails_C.CLAIMANT
, qryClaimDetails_A.SumOfPDLEGAL
, qryClaimDetails_A.SumOfPDLOSS
, qryClaimDetails_A.SumOfPDOTHEREXP
, qryClaimDetails_C.CURRES
, qryClaimDetails_C.DCURRES
, qryClaimDetails_C.RESCHG
, qryClaimDetails_C.RECNO
, qryClaimDetails_C.MEMDED
, qryClaimDetails_C.PROPRET
, qryClaimDetails_C.PROPAGG
, qryClaimDetails_C.AUTOPHYDED
, qryClaimDetails_C.TFDED
, qryClaimDetails_C.LIABRET
, qryClaimDetails_C.LIABAGG
, qryClaimDetails_C.LIABLIM
, qryClaimDetails_C.EPLDED
, qryClaimDetails_C.MHAPTFDED
, qryClaimDetails_C.MHAPDPLDED
, qryClaimDetails_C.MHAPDED
================ DROP THIS ENTIRE BIT ====================

=================Change the Having clause to a WHERE clause
===========================================================
WHERE (((IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0)
,IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0))))>500))
ORDER BY qryClaimDetails_C.DACC DESC
, qryClaimDetails_C.SUBIND;


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

This is a huge query ... hope this goes!

SELECT qryClaimDetails_C.[CLAIM#], qryClaimDetails_C.DACC,
qryClaimDetails_C.POLYR, qryClaimDetails_C.COVERAGE AS COV,
qryClaimDetails_C.SUBIND, qryClaimDetails_C.HA,
qryClaimDetails_C.HANAME, qryClaimDetails_C.DESCR,
qryClaimDetails_C.STATUS, qryClaimDetails_C.DRPT,
qryClaimDetails_C.CLAIMANT, qryClaimDetails_A.SumOfPDLEGAL AS PDLEGAL,
qryClaimDetails_A.SumOfPDLOSS AS PDLOSS,
qryClaimDetails_A.SumOfPDOTHEREXP AS PDO, qryClaimDetails_C.CURRES,
qryClaimDetails_C.DCURRES, qryClaimDetails_C.RESCHG, IIf([COV]="GENL" Or
[COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0),IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0)))
AS Incurred, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>0,Nz([MEMDED],0),0) AS MEMDED_PROP,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=Nz([TFDED],0),Nz([TFDED],0),0) AS MEMDED_TF,
IIf(Nz([Incurred],0)>0 And Nz([COV],0)="AUTOPHY",Nz([AUTOPHYDED],0),0)
AS MEMDED_AUTOPHY,
Nz([MEMDED_PROP],0)+Nz([MEMDED_TF],0)+Nz([MEMDED_AUTOPHY],0) AS
MEMDED_TOTAL, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)<Nz([MHAPDED],0),Nz([Incurred],0),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)<>"TF",Nz([MHAPDED],0),0)) AS MHAPDED_PROP,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)<Nz([MHAPTFDED],0),Nz([Incurred],0),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)="TF",Nz([MHAPTFDED],0),0)) AS MHAPDED_TF,
Nz([MHAPDED_PROP],0)+Nz([MHAPDED_TF],0) AS MHAPDED_TOTAL,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=(Nz([TFDED],0)+Nz([MHAPDED],0)) And
Nz([Incurred],0)<=((Nz([TFDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([Incurred],0)-(Nz([TFDED],0)-Nz([MHAPDED],0)),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=((Nz([TFDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([PROPRET],0),0))
AS PLSA_TF, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=(Nz([MEMDED],0)+Nz([MHAPDED],0)) And
Nz([Incurred],0)<=((Nz([MEMDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([Incurred],0)-(Nz([MEMDED],0)+Nz([MHAPDED],0)),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=((Nz([MEMDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([PROPRET],0),0))
AS PLSA_PROP, Nz([PLSA_TF],0)+Nz([PLSA_PROP],0) AS PLSA_TOTAL,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=(Nz([PROPRET],0)+Nz([TFDED],0)+Nz([MEMDED],0)),Nz([Incurred],0)-(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([TFDED],0)))
AS EXPL_TF, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([MEMDED],0)),Nz([Incurred],0)-(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([MEMDED],0)))
AS EXPL_PROP, 0 AS MEM1_GL, IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or
[COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>=Nz([LIABRET],0),Nz([LIABRET],0),0) AS MHAP_GL,
IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>Nz([LIABRET],0) And
Nz([Incurred],0)<=2000000,Nz([Incurred],0)-Nz([LIABRET],0),IIf(([COV]="GENL"
Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>=2000000,2000000-Nz([LIABRET],0),0)) AS EXLL_GL,
IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>(2000000+Nz([LIABRET],0)),Nz([Incurred],0)-2000000,0)
AS MEM2_GL, IIf([COV]="EPL" And
Nz([Incurred],0)>=Nz([EPLDED],0),Nz([EPLDED],0),0) AS MEM1_EPL,
IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>Nz([EPLDED],0) And
Nz([Incurred],0)<=Nz([LIABRET],0),Nz([Incurred],0)-Nz([EPLDED],0),IIf(Nz([COV],0)="EPL"
And Nz([Incurred],0)>=Nz([LIABRET],0),Nz([LIABRET],0)-Nz([EPLDED],0),0))
AS MHAP_EPL, IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>Nz([LIABRET],0)
And
Nz([Incurred],0)<=2000000,Nz([Incurred],0)-Nz([LIABRET],0),IIf(Nz([COV],0)="EPL"
And Nz([Incurred],0)>=2000000,2000000-Nz([LIABRET],0),0)) AS EXLL_EPL,
IIf([COV]="EPL" And Nz([Incurred],0)>2000000,Nz([Incurred],0)-2000000,0)
AS MEM2_EPL,
IIf([COV]="PROP",Nz([PDLEGAL],0)+Nz([PDLOSS],0)+Nz([PDO],0)+Nz([CURRES],0)+Nz([MEMDED_PROP],0),0)
AS PropGndUp,
IIf([COV]="AUTOPHY",Nz([PDLEGAL],0)+Nz([PDLOSS],0)+Nz([PDO],0)+Nz([CURRES],0)+Nz([MEMDED_AUTOPHY],0),0)
AS AutoPhyGndUp, IIf([COV]<>"PROP" And [COV]<>"AUTOPHY",[Incurred],0) AS
OtherGndUp, [PropGNDUp]+[AutoPhyGndUp]+[OtherGndUp] AS GNDUP,
qryClaimDetails_C.RECNO, qryClaimDetails_C.MEMDED,
qryClaimDetails_C.PROPRET, qryClaimDetails_C.PROPAGG,
qryClaimDetails_C.AUTOPHYDED, qryClaimDetails_C.TFDED,
qryClaimDetails_C.LIABRET, qryClaimDetails_C.LIABAGG,
qryClaimDetails_C.LIABLIM, qryClaimDetails_C.EPLDED,
qryClaimDetails_C.MHAPTFDED, qryClaimDetails_C.MHAPDPLDED
FROM qryClaimDetails_A RIGHT JOIN qryClaimDetails_C ON
qryClaimDetails_A.[CLAIM#] = qryClaimDetails_C.[CLAIM#]
GROUP BY qryClaimDetails_C.[CLAIM#], qryClaimDetails_C.DACC,
qryClaimDetails_C.POLYR, qryClaimDetails_C.COVERAGE,
qryClaimDetails_C.SUBIND, qryClaimDetails_C.HA,
qryClaimDetails_C.HANAME, qryClaimDetails_C.DESCR,
qryClaimDetails_C.STATUS, qryClaimDetails_C.DRPT,
qryClaimDetails_C.CLAIMANT, qryClaimDetails_A.SumOfPDLEGAL,
qryClaimDetails_A.SumOfPDLOSS, qryClaimDetails_A.SumOfPDOTHEREXP,
qryClaimDetails_C.CURRES, qryClaimDetails_C.DCURRES,
qryClaimDetails_C.RESCHG, qryClaimDetails_C.RECNO,
qryClaimDetails_C.MEMDED, qryClaimDetails_C.PROPRET,
qryClaimDetails_C.PROPAGG, qryClaimDetails_C.AUTOPHYDED,
qryClaimDetails_C.TFDED, qryClaimDetails_C.LIABRET,
qryClaimDetails_C.LIABAGG, qryClaimDetails_C.LIABLIM,
qryClaimDetails_C.EPLDED, qryClaimDetails_C.MHAPTFDED,
qryClaimDetails_C.MHAPDPLDED, qryClaimDetails_C.MHAPDED
HAVING (((IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0),IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0))))>500))

ORDER BY qryClaimDetails_C.DACC DESC , qryClaimDetails_C.SUBIND;


John said:
Can you post the SQL of the query?

If you are getting parameter prompts for the fields, that means Access
does not understand what those fields are. Normally they are a
misspelling or they don't exist in the tables that the query is using.

I think I misunderstood something you were saying, so ignore my
earlier response.
Click to expand...
Click to expand...
 
B

BobC

I am still in awe!
I will need to spend some time with this and likely get back to you.

Please note that a lot of what you suggested I drop in needed for
reports who source is this query. I was using this query as a final
gathering point for about 15 reports.

Thanks Much!
Bob


John said:
And exactly as I said, you have aliased qryClaimDetails_C.COVERAGE as
COV and then tried to use that alias elsewhere in the query. Access
will sometimes allow you to do that in the SELECT clause, but will not
allow that alias to be used in a WHERE (or HAVING) clause or in an ORDER
BY clause.

Your best bet is to replace COV with Coverage as long as you don't have
a field named coverage in queryClaimDetails_A. OR Alias Coverage as COV
in the query qryClaimDetails_C.

You've done the same with Incurred which is dependent on Coverage.

The easiest way to work this out may be to generate another intermediate
query with Incurred calculated in the intermediate query and then use
that as the source to do all those convoluted IIF expressions.

By the way I don't see any reason for you to use a Group by clause. You
are not using any of the aggregate functions in this query.


SELECT DISTINCT
qryClaimDetails_C.[CLAIM#]
, qryClaimDetails_C.DACC
, qryClaimDetails_C.POLYR
, qryClaimDetails_C.COVERAGE AS COV
, qryClaimDetails_C.SUBIND
, qryClaimDetails_C.HA
, qryClaimDetails_C.HANAME
, qryClaimDetails_C.DESCR
, qryClaimDetails_C.STATUS
, qryClaimDetails_C.DRPT
, qryClaimDetails_C.CLAIMANT
, qryClaimDetails_A.SumOfPDLEGAL AS PDLEGAL
, qryClaimDetails_A.SumOfPDLOSS AS PDLOSS
, qryClaimDetails_A.SumOfPDOTHEREXP AS PDO
, qryClaimDetails_C.CURRES
, qryClaimDetails_C.DCURRES
, qryClaimDetails_C.RESCHG

, IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0)
,IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0)))
AS Incurred

============== REMOVE THIS ENTIRE SECTION ============================
============== Add it back in a query based on this query ==========
======================================================================
, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>0,Nz([MEMDED],0),0) AS MEMDED_PROP

, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=Nz([TFDED],0),Nz([TFDED],0),0) AS MEMDED_TF

, IIf(Nz([Incurred],0)>0 And Nz([COV],0)="AUTOPHY",Nz([AUTOPHYDED],0),0)
AS MEMDED_AUTOPHY

, Nz([MEMDED_PROP],0)+Nz([MEMDED_TF],0)+Nz([MEMDED_AUTOPHY],0) AS
MEMDED_TOTAL, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)<Nz([MHAPDED],0),Nz([Incurred],0),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)<>"TF",Nz([MHAPDED],0),0)) AS MHAPDED_PROP

, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)<Nz([MHAPTFDED],0),Nz([Incurred],0),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)="TF",Nz([MHAPTFDED],0),0)) AS MHAPDED_TF

, Nz([MHAPDED_PROP],0)+Nz([MHAPDED_TF],0) AS MHAPDED_TOTAL

, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=(Nz([TFDED],0)+Nz([MHAPDED],0)) And
Nz([Incurred],0)<=((Nz([TFDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0))
,Nz([Incurred],0)-(Nz([TFDED],0)-Nz([MHAPDED],0)),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=((Nz([TFDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([PROPRET],0),0))
AS PLSA_TF, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=(Nz([MEMDED],0)+Nz([MHAPDED],0)) And
Nz([Incurred],0)<=((Nz([MEMDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0))
,Nz([Incurred],0)-(Nz([MEMDED],0)+Nz([MHAPDED],0)),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=((Nz([MEMDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([PROPRET],0),0))
AS PLSA_PROP, Nz([PLSA_TF],0)+Nz([PLSA_PROP],0) AS PLSA_TOTAL

, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=(Nz([PROPRET],0)+Nz([TFDED],0)+Nz([MEMDED],0))
,Nz([Incurred],0)-(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([TFDED],0))) AS
EXPL_TF, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([MEMDED],0))
,Nz([Incurred],0)-(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([MEMDED],0))) AS
EXPL_PROP

, 0 AS MEM1_GL

, IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L")
And Nz([Incurred],0)>=Nz([LIABRET],0),Nz([LIABRET],0),0) AS MHAP_GL

, IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L")
And Nz([Incurred],0)>Nz([LIABRET],0) And
Nz([Incurred],0)<=2000000,Nz([Incurred],0)-Nz([LIABRET],0),IIf(([COV]="GENL"
Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>=2000000,2000000-Nz([LIABRET],0),0)) AS EXLL_GL,
IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>(2000000+Nz([LIABRET],0)),Nz([Incurred],0)-2000000,0)
AS MEM2_GL

, IIf([COV]="EPL" And Nz([Incurred],0)>=Nz([EPLDED],0),Nz([EPLDED],0),0)
AS MEM1_EPL, IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>Nz([EPLDED],0)
And Nz([Incurred],0)<=Nz([LIABRET],0),Nz([Incurred],0)
-Nz([EPLDED],0),IIf(Nz([COV],0)="EPL" And
Nz([Incurred],0)>=Nz([LIABRET],0),Nz([LIABRET],0)-Nz([EPLDED],0),0)) AS
MHAP_EPL

, IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>Nz([LIABRET],0) And
Nz([Incurred],0)<=2000000,Nz([Incurred],0)-Nz([LIABRET],0)
,IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>=2000000,2000000-
Nz([LIABRET],0),0)) AS EXLL_EPL, IIf([COV]="EPL" And
Nz([Incurred],0)>2000000,Nz([Incurred],0)-2000000,0) AS MEM2_EPL

, IIf([COV]="PROP",Nz([PDLEGAL],0)+Nz([PDLOSS],0)+Nz([PDO],0)
+Nz([CURRES],0)+Nz([MEMDED_PROP],0),0) AS PropGndUp

, IIf([COV]="AUTOPHY",Nz([PDLEGAL],0)+Nz([PDLOSS],0)+Nz([PDO],0)
+Nz([CURRES],0)+Nz([MEMDED_AUTOPHY],0),0) AS AutoPhyGndUp

, IIf([COV]<>"PROP" And [COV]<>"AUTOPHY",[Incurred],0) AS OtherGndUp,
[PropGNDUp]+[AutoPhyGndUp]+[OtherGndUp] AS GNDUP
============== REMOVE THIS ENTIRE SECTION ============================


, qryClaimDetails_C.RECNO
, qryClaimDetails_C.MEMDED
, qryClaimDetails_C.PROPRET
, qryClaimDetails_C.PROPAGG
, qryClaimDetails_C.AUTOPHYDED
, qryClaimDetails_C.TFDED
, qryClaimDetails_C.LIABRET
, qryClaimDetails_C.LIABAGG
, qryClaimDetails_C.LIABLIM
, qryClaimDetails_C.EPLDED
, qryClaimDetails_C.MHAPTFDED
, qryClaimDetails_C.MHAPDPLDED

FROM qryClaimDetails_A
RIGHT JOIN qryClaimDetails_C
ON qryClaimDetails_A.[CLAIM#] = qryClaimDetails_C.[CLAIM#]

================ DROP THIS ENTIRE BIT ====================
GROUP BY qryClaimDetails_C.[CLAIM#]
, qryClaimDetails_C.DACC
, qryClaimDetails_C.POLYR
, qryClaimDetails_C.COVERAGE
, qryClaimDetails_C.SUBIND
, qryClaimDetails_C.HA
, qryClaimDetails_C.HANAME
, qryClaimDetails_C.DESCR
, qryClaimDetails_C.STATUS
, qryClaimDetails_C.DRPT
, qryClaimDetails_C.CLAIMANT
, qryClaimDetails_A.SumOfPDLEGAL
, qryClaimDetails_A.SumOfPDLOSS
, qryClaimDetails_A.SumOfPDOTHEREXP
, qryClaimDetails_C.CURRES
, qryClaimDetails_C.DCURRES
, qryClaimDetails_C.RESCHG
, qryClaimDetails_C.RECNO
, qryClaimDetails_C.MEMDED
, qryClaimDetails_C.PROPRET
, qryClaimDetails_C.PROPAGG
, qryClaimDetails_C.AUTOPHYDED
, qryClaimDetails_C.TFDED
, qryClaimDetails_C.LIABRET
, qryClaimDetails_C.LIABAGG
, qryClaimDetails_C.LIABLIM
, qryClaimDetails_C.EPLDED
, qryClaimDetails_C.MHAPTFDED
, qryClaimDetails_C.MHAPDPLDED
, qryClaimDetails_C.MHAPDED
================ DROP THIS ENTIRE BIT ====================

=================Change the Having clause to a WHERE clause
===========================================================
WHERE (((IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0)
,IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0))))>500))

ORDER BY qryClaimDetails_C.DACC DESC
, qryClaimDetails_C.SUBIND;


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

This is a huge query ... hope this goes!

SELECT qryClaimDetails_C.[CLAIM#], qryClaimDetails_C.DACC,
qryClaimDetails_C.POLYR, qryClaimDetails_C.COVERAGE AS COV,
qryClaimDetails_C.SUBIND, qryClaimDetails_C.HA,
qryClaimDetails_C.HANAME, qryClaimDetails_C.DESCR,
qryClaimDetails_C.STATUS, qryClaimDetails_C.DRPT,
qryClaimDetails_C.CLAIMANT, qryClaimDetails_A.SumOfPDLEGAL AS PDLEGAL,
qryClaimDetails_A.SumOfPDLOSS AS PDLOSS,
qryClaimDetails_A.SumOfPDOTHEREXP AS PDO, qryClaimDetails_C.CURRES,
qryClaimDetails_C.DCURRES, qryClaimDetails_C.RESCHG, IIf([COV]="GENL"
Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0),IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0)))
AS Incurred, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>0,Nz([MEMDED],0),0) AS MEMDED_PROP,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=Nz([TFDED],0),Nz([TFDED],0),0) AS MEMDED_TF,
IIf(Nz([Incurred],0)>0 And Nz([COV],0)="AUTOPHY",Nz([AUTOPHYDED],0),0)
AS MEMDED_AUTOPHY,
Nz([MEMDED_PROP],0)+Nz([MEMDED_TF],0)+Nz([MEMDED_AUTOPHY],0) AS
MEMDED_TOTAL, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)<Nz([MHAPDED],0),Nz([Incurred],0),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)<>"TF",Nz([MHAPDED],0),0)) AS MHAPDED_PROP,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)<Nz([MHAPTFDED],0),Nz([Incurred],0),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)="TF",Nz([MHAPTFDED],0),0)) AS MHAPDED_TF,
Nz([MHAPDED_PROP],0)+Nz([MHAPDED_TF],0) AS MHAPDED_TOTAL,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=(Nz([TFDED],0)+Nz([MHAPDED],0)) And
Nz([Incurred],0)<=((Nz([TFDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([Incurred],0)-(Nz([TFDED],0)-Nz([MHAPDED],0)),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=((Nz([TFDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([PROPRET],0),0))
AS PLSA_TF, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=(Nz([MEMDED],0)+Nz([MHAPDED],0)) And
Nz([Incurred],0)<=((Nz([MEMDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([Incurred],0)-(Nz([MEMDED],0)+Nz([MHAPDED],0)),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=((Nz([MEMDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([PROPRET],0),0))
AS PLSA_PROP, Nz([PLSA_TF],0)+Nz([PLSA_PROP],0) AS PLSA_TOTAL,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=(Nz([PROPRET],0)+Nz([TFDED],0)+Nz([MEMDED],0)),Nz([Incurred],0)-(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([TFDED],0)))
AS EXPL_TF, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([MEMDED],0)),Nz([Incurred],0)-(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([MEMDED],0)))
AS EXPL_PROP, 0 AS MEM1_GL, IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or
[COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>=Nz([LIABRET],0),Nz([LIABRET],0),0) AS MHAP_GL,
IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L")
And Nz([Incurred],0)>Nz([LIABRET],0) And
Nz([Incurred],0)<=2000000,Nz([Incurred],0)-Nz([LIABRET],0),IIf(([COV]="GENL"
Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>=2000000,2000000-Nz([LIABRET],0),0)) AS EXLL_GL,
IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L")
And
Nz([Incurred],0)>(2000000+Nz([LIABRET],0)),Nz([Incurred],0)-2000000,0)
AS MEM2_GL, IIf([COV]="EPL" And
Nz([Incurred],0)>=Nz([EPLDED],0),Nz([EPLDED],0),0) AS MEM1_EPL,
IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>Nz([EPLDED],0) And
Nz([Incurred],0)<=Nz([LIABRET],0),Nz([Incurred],0)-Nz([EPLDED],0),IIf(Nz([COV],0)="EPL"
And
Nz([Incurred],0)>=Nz([LIABRET],0),Nz([LIABRET],0)-Nz([EPLDED],0),0))
AS MHAP_EPL, IIf(Nz([COV],0)="EPL" And
Nz([Incurred],0)>Nz([LIABRET],0) And
Nz([Incurred],0)<=2000000,Nz([Incurred],0)-Nz([LIABRET],0),IIf(Nz([COV],0)="EPL"
And Nz([Incurred],0)>=2000000,2000000-Nz([LIABRET],0),0)) AS EXLL_EPL,
IIf([COV]="EPL" And
Nz([Incurred],0)>2000000,Nz([Incurred],0)-2000000,0) AS MEM2_EPL,
IIf([COV]="PROP",Nz([PDLEGAL],0)+Nz([PDLOSS],0)+Nz([PDO],0)+Nz([CURRES],0)+Nz([MEMDED_PROP],0),0)
AS PropGndUp,
IIf([COV]="AUTOPHY",Nz([PDLEGAL],0)+Nz([PDLOSS],0)+Nz([PDO],0)+Nz([CURRES],0)+Nz([MEMDED_AUTOPHY],0),0)
AS AutoPhyGndUp, IIf([COV]<>"PROP" And [COV]<>"AUTOPHY",[Incurred],0)
AS OtherGndUp, [PropGNDUp]+[AutoPhyGndUp]+[OtherGndUp] AS GNDUP,
qryClaimDetails_C.RECNO, qryClaimDetails_C.MEMDED,
qryClaimDetails_C.PROPRET, qryClaimDetails_C.PROPAGG,
qryClaimDetails_C.AUTOPHYDED, qryClaimDetails_C.TFDED,
qryClaimDetails_C.LIABRET, qryClaimDetails_C.LIABAGG,
qryClaimDetails_C.LIABLIM, qryClaimDetails_C.EPLDED,
qryClaimDetails_C.MHAPTFDED, qryClaimDetails_C.MHAPDPLDED
FROM qryClaimDetails_A RIGHT JOIN qryClaimDetails_C ON
qryClaimDetails_A.[CLAIM#] = qryClaimDetails_C.[CLAIM#]
GROUP BY qryClaimDetails_C.[CLAIM#], qryClaimDetails_C.DACC,
qryClaimDetails_C.POLYR, qryClaimDetails_C.COVERAGE,
qryClaimDetails_C.SUBIND, qryClaimDetails_C.HA,
qryClaimDetails_C.HANAME, qryClaimDetails_C.DESCR,
qryClaimDetails_C.STATUS, qryClaimDetails_C.DRPT,
qryClaimDetails_C.CLAIMANT, qryClaimDetails_A.SumOfPDLEGAL,
qryClaimDetails_A.SumOfPDLOSS, qryClaimDetails_A.SumOfPDOTHEREXP,
qryClaimDetails_C.CURRES, qryClaimDetails_C.DCURRES,
qryClaimDetails_C.RESCHG, qryClaimDetails_C.RECNO,
qryClaimDetails_C.MEMDED, qryClaimDetails_C.PROPRET,
qryClaimDetails_C.PROPAGG, qryClaimDetails_C.AUTOPHYDED,
qryClaimDetails_C.TFDED, qryClaimDetails_C.LIABRET,
qryClaimDetails_C.LIABAGG, qryClaimDetails_C.LIABLIM,
qryClaimDetails_C.EPLDED, qryClaimDetails_C.MHAPTFDED,
qryClaimDetails_C.MHAPDPLDED, qryClaimDetails_C.MHAPDED
HAVING (((IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0),IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0))))>500))

ORDER BY qryClaimDetails_C.DACC DESC , qryClaimDetails_C.SUBIND;


John said:
Can you post the SQL of the query?

If you are getting parameter prompts for the fields, that means
Access does not understand what those fields are. Normally they are
a misspelling or they don't exist in the tables that the query is using.

I think I misunderstood something you were saying, so ignore my
earlier response.
Click to expand...
Click to expand...
Click to expand...
 
B

BobC

Sir,
I have made some headway, but still have some issues.
As you should be able to see; I think I fixed both the COV and Incurred
issues (at least somewhat) by creating another intermediated query
(qryClaimDetail_D).
I still cannot put something like >500 in the Incurred field and it is
forcing me to use Group By (or I get an error).
I am including the SQL again ... I hope I am not being too much of a
burdon on you!?
Thanks for all of your help!!!!!!!!!!!!!!!!

SELECT qryClaimDetails_D.[CLAIM#], qryClaimDetails_D.COV,
qryClaimDetails_D.DACC, qryClaimDetails_D.POLYR,
qryClaimDetails_D.SUBIND, qryClaimDetails_D.HA,
qryClaimDetails_D.HANAME, qryClaimDetails_D.DESCR,
qryClaimDetails_D.STATUS, qryClaimDetails_D.DRPT,
qryClaimDetails_D.CLAIMANT, qryClaimDetails_D.CURRES,
qryClaimDetails_D.DCURRES, qryClaimDetails_D.RESCHG,
qryClaimDetails_D.SumOfPDLEGAL, qryClaimDetails_D.SumOfPDLOSS,
qryClaimDetails_D.SumOfPDOTHEREXP, IIf(Nz([COV],0)="PROP" And
Nz([SUBIND],0)<>"TF" And Nz([Incurred],0)>0,Nz([MEMDED],0),0) AS
MEMDED_PROP, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=Nz([TFDED],0),Nz([TFDED],0),0) AS MEMDED_TF,
IIf(Nz([Incurred],0)>0 And Nz([COV],0)="AUTOPHY",Nz([AUTOPHYDED],0),0)
AS MEMDED_AUTOPHY,
Nz([MEMDED_PROP],0)+Nz([MEMDED_TF],0)+Nz([MEMDED_AUTOPHY],0) AS
MEMDED_TOTAL, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)<Nz([MHAPDED],0),Nz([Incurred],0),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)<>"TF",Nz([MHAPDED],0),0)) AS MHAPDED_PROP,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)<Nz([MHAPTFDED],0),Nz([Incurred],0),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)="TF",Nz([MHAPTFDED],0),0)) AS MHAPDED_TF,
Nz([MHAPDED_PROP],0)+Nz([MHAPDED_TF],0) AS MHAPDED_TOTAL,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=(Nz([TFDED],0)+Nz([MHAPDED],0)) And
Nz([Incurred],0)<=((Nz([TFDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([Incurred],0)-(Nz([TFDED],0)-Nz([MHAPDED],0)),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=((Nz([TFDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([PROPRET],0),0))
AS PLSA_TF, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=(Nz([MEMDED],0)+Nz([MHAPDED],0)) And
Nz([Incurred],0)<=((Nz([MEMDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([Incurred],0)-(Nz([MEMDED],0)+Nz([MHAPDED],0)),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=((Nz([MEMDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([PROPRET],0),0))
AS PLSA_PROP, Nz([PLSA_TF],0)+Nz([PLSA_PROP],0) AS PLSA_TOTAL,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=(Nz([PROPRET],0)+Nz([TFDED],0)+Nz([MEMDED],0)),Nz([Incurred],0)-(Nz([PROPRET],0)+Nz([MHAPTFDED],0)+Nz([TFDED],0)))
AS EXPL_TF, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([MEMDED],0)),Nz([Incurred],0)-(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([MEMDED],0)))
AS EXPL_PROP, 0 AS MEM1_GL, IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or
[COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>=Nz([LIABRET],0),Nz([LIABRET],0),0) AS MHAP_GL,
IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>Nz([LIABRET],0) And
Nz([Incurred],0)<=2000000,Nz([Incurred],0)-Nz([LIABRET],0),IIf(([COV]="GENL"
Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>=2000000,2000000-Nz([LIABRET],0),0)) AS EXLL_GL,
IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>(2000000+Nz([LIABRET],0)),Nz([Incurred],0)-2000000,0)
AS MEM2_GL, IIf([COV]="EPL" And
Nz([Incurred],0)>=Nz([EPLDED],0),Nz([EPLDED],0),0) AS MEM1_EPL,
IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>Nz([EPLDED],0) And
Nz([Incurred],0)<=Nz([LIABRET],0),Nz([Incurred],0)-Nz([EPLDED],0),IIf(Nz([COV],0)="EPL"
And Nz([Incurred],0)>=Nz([LIABRET],0),Nz([LIABRET],0)-Nz([EPLDED],0),0))
AS MHAP_EPL, IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>Nz([LIABRET],0)
And
Nz([Incurred],0)<=2000000,Nz([Incurred],0)-Nz([LIABRET],0),IIf(Nz([COV],0)="EPL"
And Nz([Incurred],0)>=2000000,2000000-Nz([LIABRET],0),0)) AS EXLL_EPL,
IIf([COV]="EPL" And Nz([Incurred],0)>2000000,Nz([Incurred],0)-2000000,0)
AS MEM2_EPL,
IIf([COV]="PROP",Nz([SumOfPDLEGAL],0)+Nz([SumOfPDLOSS],0)+Nz([SumOfPDOTHEREXP],0)+Nz([CURRES],0)+Nz([MEMDED_PROP],0),0)
AS PropGndUp,
IIf([COV]="AUTOPHY",Nz([SumOfPDLEGAL],0)+Nz([SumOfPDLOSS],0)+Nz([SumOfPDOTHEREXP],0)+Nz([CURRES],0)+Nz([MEMDED_AUTOPHY],0),0)
AS AutoPhyGndUp, IIf([COV]<>"PROP" And [COV]<>"AUTOPHY",[Incurred],0) AS
OtherGndUp, [PropGNDUp]+[AutoPhyGndUp]+[OtherGndUp] AS GNDUP,
qryClaimDetails_D.RECNO, qryClaimDetails_D.MEMDED,
qryClaimDetails_D.MHAPDED, qryClaimDetails_D.PROPRET,
qryClaimDetails_D.PROPAGG, qryClaimDetails_D.AUTOPHYDED,
qryClaimDetails_D.TFDED, qryClaimDetails_D.LIABRET,
qryClaimDetails_D.LIABAGG, qryClaimDetails_D.LIABLIM,
qryClaimDetails_D.EPLDED, qryClaimDetails_D.MHAPTFDED,
qryClaimDetails_D.MHAPEPLDED
FROM qryClaimDetails_D
GROUP BY qryClaimDetails_D.[CLAIM#], qryClaimDetails_D.COV,
qryClaimDetails_D.DACC, qryClaimDetails_D.POLYR,
qryClaimDetails_D.SUBIND, qryClaimDetails_D.HA,
qryClaimDetails_D.HANAME, qryClaimDetails_D.DESCR,
qryClaimDetails_D.STATUS, qryClaimDetails_D.DRPT,
qryClaimDetails_D.CLAIMANT, qryClaimDetails_D.CURRES,
qryClaimDetails_D.DCURRES, qryClaimDetails_D.RESCHG,
qryClaimDetails_D.SumOfPDLEGAL, qryClaimDetails_D.SumOfPDLOSS,
qryClaimDetails_D.SumOfPDOTHEREXP, qryClaimDetails_D.Incurred,
qryClaimDetails_D.RECNO, qryClaimDetails_D.MEMDED,
qryClaimDetails_D.MHAPDED, qryClaimDetails_D.PROPRET,
qryClaimDetails_D.PROPAGG, qryClaimDetails_D.AUTOPHYDED,
qryClaimDetails_D.TFDED, qryClaimDetails_D.LIABRET,
qryClaimDetails_D.LIABAGG, qryClaimDetails_D.LIABLIM,
qryClaimDetails_D.EPLDED, qryClaimDetails_D.MHAPTFDED,
qryClaimDetails_D.MHAPEPLDED
ORDER BY qryClaimDetails_D.SUBIND;

Bob

John said:
And exactly as I said, you have aliased qryClaimDetails_C.COVERAGE as
COV and then tried to use that alias elsewhere in the query. Access
will sometimes allow you to do that in the SELECT clause, but will not
allow that alias to be used in a WHERE (or HAVING) clause or in an ORDER
BY clause.

Your best bet is to replace COV with Coverage as long as you don't have
a field named coverage in queryClaimDetails_A. OR Alias Coverage as COV
in the query qryClaimDetails_C.

You've done the same with Incurred which is dependent on Coverage.

The easiest way to work this out may be to generate another intermediate
query with Incurred calculated in the intermediate query and then use
that as the source to do all those convoluted IIF expressions.

By the way I don't see any reason for you to use a Group by clause. You
are not using any of the aggregate functions in this query.


SELECT DISTINCT
qryClaimDetails_C.[CLAIM#]
, qryClaimDetails_C.DACC
, qryClaimDetails_C.POLYR
, qryClaimDetails_C.COVERAGE AS COV
, qryClaimDetails_C.SUBIND
, qryClaimDetails_C.HA
, qryClaimDetails_C.HANAME
, qryClaimDetails_C.DESCR
, qryClaimDetails_C.STATUS
, qryClaimDetails_C.DRPT
, qryClaimDetails_C.CLAIMANT
, qryClaimDetails_A.SumOfPDLEGAL AS PDLEGAL
, qryClaimDetails_A.SumOfPDLOSS AS PDLOSS
, qryClaimDetails_A.SumOfPDOTHEREXP AS PDO
, qryClaimDetails_C.CURRES
, qryClaimDetails_C.DCURRES
, qryClaimDetails_C.RESCHG

, IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0)
,IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0)))
AS Incurred

============== REMOVE THIS ENTIRE SECTION ============================
============== Add it back in a query based on this query ==========
======================================================================
, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>0,Nz([MEMDED],0),0) AS MEMDED_PROP

, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=Nz([TFDED],0),Nz([TFDED],0),0) AS MEMDED_TF

, IIf(Nz([Incurred],0)>0 And Nz([COV],0)="AUTOPHY",Nz([AUTOPHYDED],0),0)
AS MEMDED_AUTOPHY

, Nz([MEMDED_PROP],0)+Nz([MEMDED_TF],0)+Nz([MEMDED_AUTOPHY],0) AS
MEMDED_TOTAL, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)<Nz([MHAPDED],0),Nz([Incurred],0),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)<>"TF",Nz([MHAPDED],0),0)) AS MHAPDED_PROP

, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)<Nz([MHAPTFDED],0),Nz([Incurred],0),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)="TF",Nz([MHAPTFDED],0),0)) AS MHAPDED_TF

, Nz([MHAPDED_PROP],0)+Nz([MHAPDED_TF],0) AS MHAPDED_TOTAL

, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=(Nz([TFDED],0)+Nz([MHAPDED],0)) And
Nz([Incurred],0)<=((Nz([TFDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0))
,Nz([Incurred],0)-(Nz([TFDED],0)-Nz([MHAPDED],0)),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=((Nz([TFDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([PROPRET],0),0))
AS PLSA_TF, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=(Nz([MEMDED],0)+Nz([MHAPDED],0)) And
Nz([Incurred],0)<=((Nz([MEMDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0))
,Nz([Incurred],0)-(Nz([MEMDED],0)+Nz([MHAPDED],0)),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=((Nz([MEMDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([PROPRET],0),0))
AS PLSA_PROP, Nz([PLSA_TF],0)+Nz([PLSA_PROP],0) AS PLSA_TOTAL

, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=(Nz([PROPRET],0)+Nz([TFDED],0)+Nz([MEMDED],0))
,Nz([Incurred],0)-(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([TFDED],0))) AS
EXPL_TF, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([MEMDED],0))
,Nz([Incurred],0)-(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([MEMDED],0))) AS
EXPL_PROP

, 0 AS MEM1_GL

, IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L")
And Nz([Incurred],0)>=Nz([LIABRET],0),Nz([LIABRET],0),0) AS MHAP_GL

, IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L")
And Nz([Incurred],0)>Nz([LIABRET],0) And
Nz([Incurred],0)<=2000000,Nz([Incurred],0)-Nz([LIABRET],0),IIf(([COV]="GENL"
Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>=2000000,2000000-Nz([LIABRET],0),0)) AS EXLL_GL,
IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>(2000000+Nz([LIABRET],0)),Nz([Incurred],0)-2000000,0)
AS MEM2_GL

, IIf([COV]="EPL" And Nz([Incurred],0)>=Nz([EPLDED],0),Nz([EPLDED],0),0)
AS MEM1_EPL, IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>Nz([EPLDED],0)
And Nz([Incurred],0)<=Nz([LIABRET],0),Nz([Incurred],0)
-Nz([EPLDED],0),IIf(Nz([COV],0)="EPL" And
Nz([Incurred],0)>=Nz([LIABRET],0),Nz([LIABRET],0)-Nz([EPLDED],0),0)) AS
MHAP_EPL

, IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>Nz([LIABRET],0) And
Nz([Incurred],0)<=2000000,Nz([Incurred],0)-Nz([LIABRET],0)
,IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>=2000000,2000000-
Nz([LIABRET],0),0)) AS EXLL_EPL, IIf([COV]="EPL" And
Nz([Incurred],0)>2000000,Nz([Incurred],0)-2000000,0) AS MEM2_EPL

, IIf([COV]="PROP",Nz([PDLEGAL],0)+Nz([PDLOSS],0)+Nz([PDO],0)
+Nz([CURRES],0)+Nz([MEMDED_PROP],0),0) AS PropGndUp

, IIf([COV]="AUTOPHY",Nz([PDLEGAL],0)+Nz([PDLOSS],0)+Nz([PDO],0)
+Nz([CURRES],0)+Nz([MEMDED_AUTOPHY],0),0) AS AutoPhyGndUp

, IIf([COV]<>"PROP" And [COV]<>"AUTOPHY",[Incurred],0) AS OtherGndUp,
[PropGNDUp]+[AutoPhyGndUp]+[OtherGndUp] AS GNDUP
============== REMOVE THIS ENTIRE SECTION ============================


, qryClaimDetails_C.RECNO
, qryClaimDetails_C.MEMDED
, qryClaimDetails_C.PROPRET
, qryClaimDetails_C.PROPAGG
, qryClaimDetails_C.AUTOPHYDED
, qryClaimDetails_C.TFDED
, qryClaimDetails_C.LIABRET
, qryClaimDetails_C.LIABAGG
, qryClaimDetails_C.LIABLIM
, qryClaimDetails_C.EPLDED
, qryClaimDetails_C.MHAPTFDED
, qryClaimDetails_C.MHAPDPLDED

FROM qryClaimDetails_A
RIGHT JOIN qryClaimDetails_C
ON qryClaimDetails_A.[CLAIM#] = qryClaimDetails_C.[CLAIM#]

================ DROP THIS ENTIRE BIT ====================
GROUP BY qryClaimDetails_C.[CLAIM#]
, qryClaimDetails_C.DACC
, qryClaimDetails_C.POLYR
, qryClaimDetails_C.COVERAGE
, qryClaimDetails_C.SUBIND
, qryClaimDetails_C.HA
, qryClaimDetails_C.HANAME
, qryClaimDetails_C.DESCR
, qryClaimDetails_C.STATUS
, qryClaimDetails_C.DRPT
, qryClaimDetails_C.CLAIMANT
, qryClaimDetails_A.SumOfPDLEGAL
, qryClaimDetails_A.SumOfPDLOSS
, qryClaimDetails_A.SumOfPDOTHEREXP
, qryClaimDetails_C.CURRES
, qryClaimDetails_C.DCURRES
, qryClaimDetails_C.RESCHG
, qryClaimDetails_C.RECNO
, qryClaimDetails_C.MEMDED
, qryClaimDetails_C.PROPRET
, qryClaimDetails_C.PROPAGG
, qryClaimDetails_C.AUTOPHYDED
, qryClaimDetails_C.TFDED
, qryClaimDetails_C.LIABRET
, qryClaimDetails_C.LIABAGG
, qryClaimDetails_C.LIABLIM
, qryClaimDetails_C.EPLDED
, qryClaimDetails_C.MHAPTFDED
, qryClaimDetails_C.MHAPDPLDED
, qryClaimDetails_C.MHAPDED
================ DROP THIS ENTIRE BIT ====================

=================Change the Having clause to a WHERE clause
===========================================================
WHERE (((IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0)
,IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0))))>500))

ORDER BY qryClaimDetails_C.DACC DESC
, qryClaimDetails_C.SUBIND;


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

This is a huge query ... hope this goes!

SELECT qryClaimDetails_C.[CLAIM#], qryClaimDetails_C.DACC,
qryClaimDetails_C.POLYR, qryClaimDetails_C.COVERAGE AS COV,
qryClaimDetails_C.SUBIND, qryClaimDetails_C.HA,
qryClaimDetails_C.HANAME, qryClaimDetails_C.DESCR,
qryClaimDetails_C.STATUS, qryClaimDetails_C.DRPT,
qryClaimDetails_C.CLAIMANT, qryClaimDetails_A.SumOfPDLEGAL AS PDLEGAL,
qryClaimDetails_A.SumOfPDLOSS AS PDLOSS,
qryClaimDetails_A.SumOfPDOTHEREXP AS PDO, qryClaimDetails_C.CURRES,
qryClaimDetails_C.DCURRES, qryClaimDetails_C.RESCHG, IIf([COV]="GENL"
Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0),IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0)))
AS Incurred, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>0,Nz([MEMDED],0),0) AS MEMDED_PROP,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=Nz([TFDED],0),Nz([TFDED],0),0) AS MEMDED_TF,
IIf(Nz([Incurred],0)>0 And Nz([COV],0)="AUTOPHY",Nz([AUTOPHYDED],0),0)
AS MEMDED_AUTOPHY,
Nz([MEMDED_PROP],0)+Nz([MEMDED_TF],0)+Nz([MEMDED_AUTOPHY],0) AS
MEMDED_TOTAL, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)<Nz([MHAPDED],0),Nz([Incurred],0),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)<>"TF",Nz([MHAPDED],0),0)) AS MHAPDED_PROP,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)<Nz([MHAPTFDED],0),Nz([Incurred],0),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)="TF",Nz([MHAPTFDED],0),0)) AS MHAPDED_TF,
Nz([MHAPDED_PROP],0)+Nz([MHAPDED_TF],0) AS MHAPDED_TOTAL,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=(Nz([TFDED],0)+Nz([MHAPDED],0)) And
Nz([Incurred],0)<=((Nz([TFDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([Incurred],0)-(Nz([TFDED],0)-Nz([MHAPDED],0)),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=((Nz([TFDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([PROPRET],0),0))
AS PLSA_TF, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=(Nz([MEMDED],0)+Nz([MHAPDED],0)) And
Nz([Incurred],0)<=((Nz([MEMDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([Incurred],0)-(Nz([MEMDED],0)+Nz([MHAPDED],0)),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=((Nz([MEMDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([PROPRET],0),0))
AS PLSA_PROP, Nz([PLSA_TF],0)+Nz([PLSA_PROP],0) AS PLSA_TOTAL,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=(Nz([PROPRET],0)+Nz([TFDED],0)+Nz([MEMDED],0)),Nz([Incurred],0)-(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([TFDED],0)))
AS EXPL_TF, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([MEMDED],0)),Nz([Incurred],0)-(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([MEMDED],0)))
AS EXPL_PROP, 0 AS MEM1_GL, IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or
[COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>=Nz([LIABRET],0),Nz([LIABRET],0),0) AS MHAP_GL,
IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L")
And Nz([Incurred],0)>Nz([LIABRET],0) And
Nz([Incurred],0)<=2000000,Nz([Incurred],0)-Nz([LIABRET],0),IIf(([COV]="GENL"
Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>=2000000,2000000-Nz([LIABRET],0),0)) AS EXLL_GL,
IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L")
And
Nz([Incurred],0)>(2000000+Nz([LIABRET],0)),Nz([Incurred],0)-2000000,0)
AS MEM2_GL, IIf([COV]="EPL" And
Nz([Incurred],0)>=Nz([EPLDED],0),Nz([EPLDED],0),0) AS MEM1_EPL,
IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>Nz([EPLDED],0) And
Nz([Incurred],0)<=Nz([LIABRET],0),Nz([Incurred],0)-Nz([EPLDED],0),IIf(Nz([COV],0)="EPL"
And
Nz([Incurred],0)>=Nz([LIABRET],0),Nz([LIABRET],0)-Nz([EPLDED],0),0))
AS MHAP_EPL, IIf(Nz([COV],0)="EPL" And
Nz([Incurred],0)>Nz([LIABRET],0) And
Nz([Incurred],0)<=2000000,Nz([Incurred],0)-Nz([LIABRET],0),IIf(Nz([COV],0)="EPL"
And Nz([Incurred],0)>=2000000,2000000-Nz([LIABRET],0),0)) AS EXLL_EPL,
IIf([COV]="EPL" And
Nz([Incurred],0)>2000000,Nz([Incurred],0)-2000000,0) AS MEM2_EPL,
IIf([COV]="PROP",Nz([PDLEGAL],0)+Nz([PDLOSS],0)+Nz([PDO],0)+Nz([CURRES],0)+Nz([MEMDED_PROP],0),0)
AS PropGndUp,
IIf([COV]="AUTOPHY",Nz([PDLEGAL],0)+Nz([PDLOSS],0)+Nz([PDO],0)+Nz([CURRES],0)+Nz([MEMDED_AUTOPHY],0),0)
AS AutoPhyGndUp, IIf([COV]<>"PROP" And [COV]<>"AUTOPHY",[Incurred],0)
AS OtherGndUp, [PropGNDUp]+[AutoPhyGndUp]+[OtherGndUp] AS GNDUP,
qryClaimDetails_C.RECNO, qryClaimDetails_C.MEMDED,
qryClaimDetails_C.PROPRET, qryClaimDetails_C.PROPAGG,
qryClaimDetails_C.AUTOPHYDED, qryClaimDetails_C.TFDED,
qryClaimDetails_C.LIABRET, qryClaimDetails_C.LIABAGG,
qryClaimDetails_C.LIABLIM, qryClaimDetails_C.EPLDED,
qryClaimDetails_C.MHAPTFDED, qryClaimDetails_C.MHAPDPLDED
FROM qryClaimDetails_A RIGHT JOIN qryClaimDetails_C ON
qryClaimDetails_A.[CLAIM#] = qryClaimDetails_C.[CLAIM#]
GROUP BY qryClaimDetails_C.[CLAIM#], qryClaimDetails_C.DACC,
qryClaimDetails_C.POLYR, qryClaimDetails_C.COVERAGE,
qryClaimDetails_C.SUBIND, qryClaimDetails_C.HA,
qryClaimDetails_C.HANAME, qryClaimDetails_C.DESCR,
qryClaimDetails_C.STATUS, qryClaimDetails_C.DRPT,
qryClaimDetails_C.CLAIMANT, qryClaimDetails_A.SumOfPDLEGAL,
qryClaimDetails_A.SumOfPDLOSS, qryClaimDetails_A.SumOfPDOTHEREXP,
qryClaimDetails_C.CURRES, qryClaimDetails_C.DCURRES,
qryClaimDetails_C.RESCHG, qryClaimDetails_C.RECNO,
qryClaimDetails_C.MEMDED, qryClaimDetails_C.PROPRET,
qryClaimDetails_C.PROPAGG, qryClaimDetails_C.AUTOPHYDED,
qryClaimDetails_C.TFDED, qryClaimDetails_C.LIABRET,
qryClaimDetails_C.LIABAGG, qryClaimDetails_C.LIABLIM,
qryClaimDetails_C.EPLDED, qryClaimDetails_C.MHAPTFDED,
qryClaimDetails_C.MHAPDPLDED, qryClaimDetails_C.MHAPDED
HAVING (((IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0),IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0))))>500))

ORDER BY qryClaimDetails_C.DACC DESC , qryClaimDetails_C.SUBIND;


John said:
Can you post the SQL of the query?

If you are getting parameter prompts for the fields, that means
Access does not understand what those fields are. Normally they are
a misspelling or they don't exist in the tables that the query is using.

I think I misunderstood something you were saying, so ignore my
earlier response.
Click to expand...
Click to expand...
Click to expand...
 
B

BobC

That's FUNNNY!
I'll try the ,(Comma) operator after I get the alias issues working the
way they should!
Thanks for your help!
Bob

Marshall said:
BobC said:
In an attempt to learn new things and to utilize the IN operator ... I
am getting an error message: "The expression you entered has a invalid
.(Dot)or ! operator or invalid parenthesis."

The SQL is below:
Incurred: IIf([COV] IN("GENL"."AUTOLIAB"."D&O"."EPL")
Click to expand...

I need to squint harder. get eye surgery or much larger
monitors :-(

The list for IN should use commas, not periods.

IN("GENL","AUTOLIAB","D&O","EPL")

On the original issue ... Don't know if this will help ... This is the
last few lines of the 'old' sql with an attempt to limit the records to
values of <500:

qryClaimDetails_C.MHAPTFDED, qryClaimDetails_C.MHAPDPLDED,
qryClaimDetails_C.MHAPDED
HAVING (((IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0),IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0))))<500))
ORDER BY qryClaimDetails_C.DACC DESC , qryClaimDetails_C.SUBIND;
Click to expand...

As John said, you can not use an alias name anywhere outside
the Select clause, and sometimes not even there. Since it
seems that he has moved beyond that issue, I should stay out
of it before I confuse the issue.
Click to expand...
 
M

Marshall Barton

BobC said:
In an attempt to learn new things and to utilize the IN operator ... I
am getting an error message: "The expression you entered has a invalid
.(Dot)or ! operator or invalid parenthesis."

The SQL is below:
Incurred: IIf([COV] IN("GENL"."AUTOLIAB"."D&O"."EPL")
Click to expand...

I need to squint harder. get eye surgery or much larger
monitors :-(

The list for IN should use commas, not periods.

IN("GENL","AUTOLIAB","D&O","EPL")

On the original issue ... Don't know if this will help ... This is the
last few lines of the 'old' sql with an attempt to limit the records to
values of <500:

qryClaimDetails_C.MHAPTFDED, qryClaimDetails_C.MHAPDPLDED,
qryClaimDetails_C.MHAPDED
HAVING (((IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0),IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0))))<500))
ORDER BY qryClaimDetails_C.DACC DESC , qryClaimDetails_C.SUBIND;
Click to expand...

As John said, you can not use an alias name anywhere outside
the Select clause, and sometimes not even there. Since it
seems that he has moved beyond that issue, I should stay out
of it before I confuse the issue.
 
J

John Spencer

The only thing I can think of is that Incurred is somehow getting typed
as text and not as a numeric value. You could try surrounding the
calculation with CDbl to force it to get treated as a number

, CDbl(IIf([COV] In ("GENL", "AUTOLIAB", "D&O", "EPL"),
Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0) +Nz([PDO],0)
,IIf([COV]="AUTOPHY",Nz([CURRES],0) +Nz([PDLOSS],0) +Nz([PDO],0)
, Nz([CURRES],0) +Nz([PDLOSS],0)))) AS Incurred

I think it is possible that if all of the values you are attempting to
add are null (in any one record) that Access will type the NZ result as
a string and concatenate the zeroes into "0000" or "000" or "00". NZ
does not always play well in queries.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 

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

Overflow from Calculation - How do I do Calculation? 10
Limit the results from a calculated field in a query 7
compute, assign and total values in a query? 4
Query criteria problem 2
Use criteria in field for fieldname in query 2
How to limit Access listbox lookup query using another field 14
Parameter Value in Crosstab Query 4
IIF Statement to return multiple values for a field - JohnW 3

Top