Short Iif Statement: Close But No Cigar

[Guest]

I have an Iif statement that as far as I can tell says what I need it to say,
but running the query proves otherwise. This is it:

Status: IIf([ShopCartSmallDesc].[Status]="pre" And
([IM2_InventoryItemWhseDetl].[QtyOnHand]-[IM2_InventoryItemWhseDetl].[QtyOnSalesOrder]-[IM2_InventoryItemWhseDetl].[QtyOnBackOrder])>0,"new",IIf([ShopCartSmallDesc].[Date
Imported]>DateAdd("m",-2,Date()),"new",[ShopCartSmallDesc].[Status]))

I think this says (and need it to say):

If [status] is "pre" AND [qtyonhand] is > 0, update [status] to "new", if
not, check to see if [dateimported] is less than two months old, if it is,
update [status] to new, if not, leave it as whatever it was.

But, this must not be correct though, because I'm getting a bunch or records
[status] updated to "new" even though they weren't "pre" and they aren't less
than two months old.

Can anyone see what I'm doing wrong here?
Thanks for any assistance.

 

[Douglas J Steele]

Are you sure you always have values for all three of
[IM2_InventoryItemWhseDetl].[QtyOnHand],
[IM2_InventoryItemWhseDetl].[QtyOnSalesOrder] and
[IM2_InventoryItemWhseDetl].[QtyOnBackOrder]? If any of those three values
is Null, expression won't evaluate correctly. Try:

(Nz([IM2_InventoryItemWhseDetl].[QtyOnHand],0)-Nz([IM2_InventoryItemWhseDetl
].[QtyOnSalesOrder], 0)-Nz([IM2_InventoryItemWhseDetl].[QtyOnBackOrder],
0))>0

Also, is there a possibility that [ShopCartSmallDesc].[Status] might be
Null? If so, try using

Nz(([ShopCartSmallDesc].[Status], "pre")="pre"

 

[Guest]

Nz(([ShopCartSmallDesc].[Status], "pre")="pre"

Not sure if I understand this funcion. Does this mean, if the field is null,
return a value of "pre"?

Because, if it's initially, null, I want it to remain null (unless
[dateimported] is less than two months old).

Are you sure you always have values for all three of
[IM2_InventoryItemWhseDetl].[QtyOnHand],
[IM2_InventoryItemWhseDetl].[QtyOnSalesOrder] and
[IM2_InventoryItemWhseDetl].[QtyOnBackOrder]? If any of those three values
is Null, expression won't evaluate correctly. Try:

(Nz([IM2_InventoryItemWhseDetl].[QtyOnHand],0)-Nz([IM2_InventoryItemWhseDetl
].[QtyOnSalesOrder], 0)-Nz([IM2_InventoryItemWhseDetl].[QtyOnBackOrder],
0))>0

Also, is there a possibility that [ShopCartSmallDesc].[Status] might be
Null? If so, try using

Nz(([ShopCartSmallDesc].[Status], "pre")="pre"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have an Iif statement that as far as I can tell says what I need it to say,
but running the query proves otherwise. This is it:

Status: IIf([ShopCartSmallDesc].[Status]="pre" And
([IM2_InventoryItemWhseDetl].[QtyOnHand]-[IM2_InventoryItemWhseDetl].[QtyOnS
alesOrder]-[IM2_InventoryItemWhseDetl].[QtyOnBackOrder])>0,"new",IIf([ShopCa
rtSmallDesc].[Date
Imported]>DateAdd("m",-2,Date()),"new",[ShopCartSmallDesc].[Status]))

I think this says (and need it to say):

If [status] is "pre" AND [qtyonhand] is > 0, update [status] to "new", if
not, check to see if [dateimported] is less than two months old, if it is,
update [status] to new, if not, leave it as whatever it was.

But, this must not be correct though, because I'm getting a bunch or records
[status] updated to "new" even though they weren't "pre" and they aren't less
than two months old.

Can anyone see what I'm doing wrong here?
Thanks for any assistance.

 

[Douglas J Steele]

Yes, that's what it means.

If you want Null fields to be left Null, you're going to have to explicitly
check for Null values using the IsNull function.

If [ShopCartSmallDesc].[Status] IS null, then
[ShopCartSmallDesc].[Status]="pre" is meaningless.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Nz(([ShopCartSmallDesc].[Status], "pre")="pre"

Not sure if I understand this funcion. Does this mean, if the field is null,
return a value of "pre"?

Because, if it's initially, null, I want it to remain null (unless
[dateimported] is less than two months old).

Are you sure you always have values for all three of
[IM2_InventoryItemWhseDetl].[QtyOnHand],
[IM2_InventoryItemWhseDetl].[QtyOnSalesOrder] and
[IM2_InventoryItemWhseDetl].[QtyOnBackOrder]? If any of those three values
is Null, expression won't evaluate correctly. Try:

(Nz([IM2_InventoryItemWhseDetl].[QtyOnHand],0)-Nz([IM2_InventoryItemWhseDetl
].[QtyOnSalesOrder], 0)-Nz([IM2_InventoryItemWhseDetl].[QtyOnBackOrder],
0))>0

Also, is there a possibility that [ShopCartSmallDesc].[Status] might be
Null? If so, try using

Nz(([ShopCartSmallDesc].[Status], "pre")="pre"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have an Iif statement that as far as I can tell says what I need it

to
say,

but running the query proves otherwise. This is it:

Status: IIf([ShopCartSmallDesc].[Status]="pre" And

([IM2_InventoryItemWhseDetl].[QtyOnHand]-[IM2_InventoryItemWhseDetl].[QtyOnS
alesOrder]-[IM2_InventoryItemWhseDetl].[QtyOnBackOrder])>0,"new",IIf([ShopCa
rtSmallDesc].[Date

Imported]>DateAdd("m",-2,Date()),"new",[ShopCartSmallDesc].[Status]))

I think this says (and need it to say):

If [status] is "pre" AND [qtyonhand] is > 0, update [status] to "new", if
not, check to see if [dateimported] is less than two months old, if it is,
update [status] to new, if not, leave it as whatever it was.

But, this must not be correct though, because I'm getting a bunch or records
[status] updated to "new" even though they weren't "pre" and they

aren't
less

than two months old.

Can anyone see what I'm doing wrong here?
Thanks for any assistance.