CODEWITHSUNDEEP
ms-access
bd528
bd528

Reputation: 886

Unexpected result from IIF and ISNULL

When using the code below on the record I am evaluating, NA is returned, which is what I want as [actual_ssd] (in this case) is null

Switch(
[Comm_Type]="Maya",CVDate(DateAdd("m",6,[actual_ssd])),
[Comm_Type]="Sapphire",IIf([Tariff_length]=12,CVDate(DateAdd("m",12,[actual_ssd])),CVDate(DateAdd("m",24,[actual_ssd]))),
[Comm_Type]="Cobalt",IIF(ISNULL([actual_ssd]),"NA","Test")
)

However, I want [actual_ssd] returned if [actual_ssd] is not null, but when I change my code to :-

Switch(
[Comm_Type]="Maya",CVDate(DateAdd("m",6,[actual_ssd])),
[Comm_Type]="Sapphire",IIf([Tariff_length]=12,CVDate(DateAdd("m",12,[actual_ssd])),CVDate(DateAdd("m",24,[actual_ssd]))),
[Comm_Type]="Cobalt",IIF(ISNULL([actual_ssd]),"NA",[actual_ssd])
)

...Access returns #Error in the query results.

Can anyone confirm why this is happening (where the [Comm_Type] is "Cobalt") ?

Upvotes: 0

Views: 54

Answers (2)

Gustav
Gustav

Reputation: 55816

You can't mix dates and strings, and indeed no need for CVDate:

Switch(
[Comm_Type]="Maya",DateAdd("m",6,[actual_ssd]),
[Comm_Type]="Sapphire",DateAdd("m",IIf([Tariff_length]=12,12,24),[actual_ssd]),
[Comm_Type]="Cobalt",IIf([actual_ssd] Is Null,"NA", Format([actual_ssd],"yyyy-mm-dd"))
)

Upvotes: 1

Minty
Minty

Reputation: 1626

The correct format in a query is 

IIf([actual_ssd] Is Null,"NA",[actual_ssd])

IsNull() Only works in VBA

Upvotes: 0

Related Questions