Re: How do I temporarily limit values in a query field

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 Barton wrote:
BobC wrote:

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.


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.

.

Relevant Pages

  • Re: Jet SQL and Virtual tables/subqueries qiestions
    ... Perahps we've begin to uncover some bugs in the Access query ... If you PASTE SQL test in the following format into the SQL window ... around a field or table name within the VirtTbl1 subquery, ... I would think that producing a correct error message for that sort of query ...
    (microsoft.public.access.queries)
  • Re: Delete Records From One Table that are Duplicated in Another
    ... Your error message indicates Access / JET think there are 2 Tables involved ... Post your Table Structure and the SQL String of your Query. ... I linked them on the Loan# ...
    (microsoft.public.access.queries)
  • Re: 3061 Error when doing everything that works in another module.
    ... Error message states that 1 parameter is expected. ... > I assume that the SQL statement is a properly delimited text string in your ... is this a reference to using the design view ... > of a query to create the SQL statement and to run the query? ...
    (microsoft.public.access.modulesdaovba)
  • Re: 3061 Error when doing everything that works in another module.
    ... built the SQL string in the query designer. ... > Error message states that 1 parameter is expected. ... >> of a query to create the SQL statement and to run the query? ...
    (microsoft.public.access.modulesdaovba)
  • Re: Making dynamic table sortable
    ... The flip side of which results is websites filled with SQL injections ... Which is likely to be invalid SQL. ... error conditions. ... rather than the query ...
    (comp.lang.php)