nested if statement in query

Question

(please excuse that I didn't use aliases). I would like my query output to say "none" if 3rd qrtr sales equals 4th quarter sales, but my output is just saying "up" when they equal each other. Please help me correct this code

trend: IIf([qryQuarterlyCustomersales3rdQuarter_final]
.[3rd qrtr sls]=[qryQuarterlyCustomersales4thQuarter_final]
.[4th qrtr sls],"none",
IIf([qryQuarterlyCustomersales3rdQuarter_final]
.[3rd qrtr sls]>[qryQuarterlyCustomersales4thQuarter_final]
.[4th qrtr sls],"down","up"))

Answer 1

What data type is [3rd qrtr sls] and [4th qrtr sls]? can you post some sample data?

what does this yield?

IIF([qryQuarterlyCustomersales4thQuarter_final]
.[4th qrtr sls] > [qryQuarterlyCustomersales3rdQuarter_final]
.[3rd qrtr sls], "up", IIF([qryQuarterlyCustomersales4thQuarter_final].[4th qrtr sls] <[qryQuarterlyCustomersales3rdQuarter_final]
.[3rd qrtr sls], "down", "none"))

if this still yields "up", then you know the values stored in those two columns aren't equal

Answer 2

Are you sure the two values are exactly the same?
As written there appears to be a slight mistake in the inner iif statement, but that doesn't explain the results you're currently getting.

Pseudocode helps me understand how these expressions work:

if q3 = q4
    "none"
else*
    if q3 > q4
        "up"
    else*
        "down"

*note that else is implied; not legal syntax here

Making as few changes as possible, this should work as you expect it to:

trend: IIf([qryQuarterlyCustomersales3rdQuarter_final]
.[3rd qrtr sls] = [qryQuarterlyCustomersales4thQuarter_final]
.[4th qrtr sls],"none",
IIf([qryQuarterlyCustomersales3rdQuarter_final]
.[3rd qrtr sls] > [qryQuarterlyCustomersales4thQuarter_final]
.[4th qrtr sls],"up","down")) # <- these final two args should be switched

Documentation:

Syntax

IIf(expr, truepart, falsepart)

The IIf function syntax has these arguments:

ARGUMENT DESCRIPTION expr Required. Expression you want to evaluate. truepart Required. Value or expression returned if expr is True. falsepart Required. Value or expression returned if expr is False.