Newbie, trouble with IIFstatement in SQL query

  • Thread starter Thread starter verci
  • Start date Start date
V

verci

Hi, I use this query as my reports underleying query but I do not know what
the correct sintax is for the IIF statement, can you show me how to do this
correctly???, I'm trying tu sum all the values of the field Importe if the
field Factura.Retencion is True.


SELECT FacturaDetalle.FacturaID, Factura.NumFactura, Factura.FechFactura,
Factura.Nombre, Factura.Retencion, FacturaDetalle.Importe,
Factura.Cancelada,

GranRet: IIF( Factura.Retencion = true , Sum(([Importe]*4)/100),"")

FROM Factura INNER JOIN FacturaDetalle ON Factura.FacturaID =
FacturaDetalle.FacturaID
WHERE (((Factura.FechFactura) Between [Fecha Inicial:] And [Fecha Final:]));
 
Your logic structure is fine. You need to qualify the fields you are
addressing. Also, it appears you are summing numbers, but returning a zero
length string if Factura.Retencion is false. Are you sure that is okay, or
should it return a zero?
Note the use of ! instead of .
The easiest way to remember which to use is . denotes a property of method
of an object. ! is a qualifer for an object you created.

The way the formula is written, Importe will be multipled by 4 and divided
by 100 for eac row in the table, then summed.

GranRet: IIF( [Factura]![Retencion] = true ,
Sum(([Factura]![Importe]*4)/100),"")
 
You can't use Sum() without changing your query to a Totals Query. Also, you
should not have the "" in your IIf() since it returns a zero-length-string
where I expect you want to return 0.

It is difficult to determine if you actually need a total in your query or
just report or group totals in your report.
 
Do the IIF test inside the SUM.
Since you are summing, you need to GROUP on all the other fields that you
are not applying an aggregate operation to.
The field alias GranRet needs to be speciefied with the keyword AS and must
be after the calculated field

SELECT FacturaDetalle.FacturaID, Factura.NumFactura, Factura.FechFactura,
Factura.Nombre, Factura.Retencion, FacturaDetalle.Importe,
Factura.Cancelada,
Sum(IIF(Factura.Retencion = true, ([Importe]*4)/100,Null)) as GranRet
FROM Factura INNER JOIN FacturaDetalle
ON Factura.FacturaID = FacturaDetalle.FacturaID
WHERE (((Factura.FechFactura) Between [Fecha Inicial:] And [Fecha Final:]))
GROUP BY FacturaDetalle.FacturaID, Factura.NumFactura, Factura.FechFactura,
Factura.Nombre, Factura.Retencion, FacturaDetalle.Importe,
Factura.Cancelada
 
Back
Top