Solved IIf returning error

[trax1337]

I have what should be a simple query:

SELECT
    qry_receipt_bay_progress_2.[Cases Expected],
    qry_receipt_bay_progress_2.[Cases Receipted],
    qry_receipt_bay_progress_2.[Packs Expected],
    IIf(
        [VP] = 0,
        0,
        -Int(-([Cases Receipted] / ([VP] * [SP])))
    ) AS [Packs Receipted]
FROM
    qry_receipt_bay_progress_2;

The issue is this line:

IIf(
        [VP] = 0,

        0,

        -Int(-([Cases Receipted] / ([VP] * [SP])))

    ) AS [Packs Receipted]

If I remove the condition -Int(-([Cases Receipted] / ([VP] * [SP]))) returns the correct number when the VP is not 0 and #Num! when it is. So I want to add a condition to return 0 if VP is 0 but I just get Error if I use IIF(), Nz() also returns #Num!

 

[xavier.batlle]

I have what should be a simple query:

SELECT
    qry_receipt_bay_progress_2.[Cases Expected],
    qry_receipt_bay_progress_2.[Cases Receipted],
    qry_receipt_bay_progress_2.[Packs Expected],
    IIf(
        [VP] = 0,
        0,
        -Int(-([Cases Receipted] / ([VP] * [SP])))
    ) AS [Packs Receipted]
FROM
    qry_receipt_bay_progress_2;

The issue is this line:

IIf(
        [VP] = 0,

        0,

        -Int(-([Cases Receipted] / ([VP] * [SP])))

    ) AS [Packs Receipted]

If I remove the condition -Int(-([Cases Receipted] / ([VP] * [SP]))) returns the correct number when the VP is not 0 and #Num! when it is. So I want to add a condition to return 0 if VP is 0 but I just get Error if I use IIF(), Nz() also returns #Num!

The query works fine but depending on the data, if there are 0 or Null values in [VP] then the code will fail.
This code avoid the 0 and null values in [VP] and [SP]

SELECT
     qry_receipt_bay_progress_2.[Cases Expected],
     qry_receipt_bay_progress_2.[Cases Receipted],
     qry_receipt_bay_progress_2.[Packs Expected],
     IIf(Nz([VP],0)=0 or Nz([SP],0)=0,0,-Int(-([Cases Receipted]/([VP]*[SP])))) AS  [Packs Receipted value]
FROM qry_receipt_bay_progress_2;

 

[ebs17]

I am not entirely sure in which cases exactly, but sometimes the interpreter evaluates the Else branch even if it is not controlled according to the program logic.

If V=0, then this results in a division by 0 with a mandatory error.

If you keep your expression without taking NULL into account, for example

... -Int(-([Cases Receipted] / (IIf([VP]=0, 1, [VP]) * [SP]))

 

[xavier.batlle]

I am not entirely sure in which cases exactly, but sometimes the interpreter evaluates the Else branch even if it is not controlled according to the program logic.

If V=0, then this results in a division by 0 with a mandatory error.

If you keep your expression without taking NULL into account, for example

... -Int(-([Cases Receipted] / (IIf([VP]=0, 1, [VP]) * [SP]))

If I'm not wrong when you use the IIF() function in VBA the interpreter always evaluate both branches of the exrpression, BUT if you use the IIF() statement in a query the SQL interpreter only evaluates the according part of the expression.