2

I have the following part of my query within excel that is not working.

iif(master.[Canada] is null or master.[USA] is null ,'USER','' ) as [Stackoverflow]

Am I doing the nulls correctly?

The logic should

1) If there is No Canada or No Usa data, put "USER" in Stackoverflow column.

2) If either Canada OR USA has data then Stackoverflow should be empty.

Currently what Im getting:

+-----------+--------------+---------------+
|  Canada   |     USA      | Stackoverflow |
+-----------+--------------+---------------+
|           |              |               |
|           |              |               |
| 912796NZ8 |              |               |
|           |              |               |
|           | US912796NZ81 |               |
|           |              |               |
| 912796NZ8 | US912796NZ81 |               |
| 912796NZ8 | US912796NZ81 |               |
| 912796qd4 | US912796QD43 |               |
| 298785HB5 | US298785HB50 |               |
+-----------+--------------+---------------+

What I am expecting:

+-----------+--------------+---------------+
|  Canada   |     USA      | Stackoverflow |
+-----------+--------------+---------------+
|           |              | USER          |
|           |              | USER          |
| 912796NZ8 |              |               |
|           |              | USER          |
|           | US912796NZ81 |               |
|           |              | USER          |
| 912796NZ8 | US912796NZ81 |               |
| 912796NZ8 | US912796NZ81 |               |
| 912796qd4 | US912796QD43 |               |
| 298785HB5 | US298785HB50 |               |
+-----------+--------------+---------------+

After changing query to iif(TRIM(master.[Camada]) = '' OR TRIM(master.[USA]) = '','USER', '') as [Stackoverflow]

It does a good job except now I still have some canada and USA data that gives me USER. 

+-----------+-----+---------------+
|  Canada   | USA | Stackoverflow |
+-----------+-----+---------------+
| 62941ZPA6 |     | USER          |
| 62943Z4R0 |     | USER          |
| 62945ZLQ1 |     | USER          |
| 62950ZZE5 |     | USER          |
| 75585RLK9 |     | USER          |
| 00433JAA3 |     | USER          |
| 13509PEV1 |     | USER          |
| 13509PEZ2 |     | USER          |
| 62931ZLX2 |     | USER          |
| 62941Z8M9 |     | USER          |
| 62941ZYK4 |     | USER          |
| 62942ZV42 |     | USER          |
| 62943Z6T4 |     | USER          |
| 62946Z6Y0 |     | USER          |
| 62947ZWC8 |     | USER          |
| 62948ZTJ6 |     | USER          |
| 62949ZE51 |     | USER          |
| 75585RLK9 |     | USER          |
| 75585RMB8 |     | USER          |
| 75585RMW2 |     | USER          |
+-----------+-----+---------------+

Should not have USER for these 20 records.

Any help would be appreciated, thanks.

excelguy
  • 1,574
  • 6
  • 33
  • 67

2 Answers2

1

I think, Jet uses the IsNull() function instead of the IS NULL operator:

iif(IsNull(master.[Canada]) or IsNull(master.[USA]),'USER','' ) as [Stackoverflow]
cha
  • 10,301
  • 1
  • 18
  • 26
1

The Jet/ACE SQL dialect does support IS NULL. However, as your current results suggest, empty strings ('') are not the same as the NULL entity. This is especially true in Excel (a non-database application where empty cells may not default to NULL). In fact, you are actually assigning empty strings in the falsepart of your IIF() call where records without 'USER' value in [Stackoverflow] will be empty string and not NULL.

Consider extending your IIF expressions to account for zero-length strings and assigning NULL to non-matches:

IIF((master.[Canada] IS NULL AND master.[USA] IS NULL) OR
    (master.[Canada] = '' AND master.[USA] IS NULL) OR
    (master.[Canada] IS NULL AND master.[USA] = '') OR
    (master.[Canada] = '' AND master.[USA] = ''), 'USER', NULL) As [Stackoverflow]

Even account for invisible whitespace by using TRIM():

IIF((master.[Canada] IS NULL AND master.[USA] IS NULL) OR
    (TRIM(master.[Canada]) = '' AND master.[USA] IS NULL) OR
    (master.[Canada] IS NULL AND TRIM(master.[USA]) = '') OR
    (TRIM(master.[Canada]) = '' AND TRIM(master.[USA]) = ''), 'USER', NULL) As [Stackoverflow]
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • Thanks Parfait, but right now I am getting the logic where both Canada ANDUSA contain '', but I also want to exclude 'USER' when one of them is ''. Which is weird because it seems like it worked sometimes, and not all the time. I'll try adding trim. – excelguy Oct 26 '18 at 18:20
  • Hmmm... using `OR` as done above should check both columns if either is empty string. Please explain initial data, current result, desired result. Yes, try `TRIM` as suggested due to unseen whitespaces. – Parfait Oct 26 '18 at 18:22
  • a Strange observation, having the `IS NULL` part actually doesnt help to much (4000 canada records where user is), but when I removed this I have only 20 canada records where user is. – excelguy Oct 26 '18 at 18:37
  • Once again, all these conditions use `OR` operator so if records fall in *any* of them, `'USER'` is tagged. Removing unneeded conditions should not change result unless there were cases that followed its logic. – Parfait Oct 26 '18 at 18:40
  • updated the question, its acting like both canada and usa is null then put user, when it should be either null then user. – excelguy Oct 26 '18 at 18:41
  • Ah! Got it. I read your condition wrong: *If either Canada OR USA has data then Stackoverflow should be empty.* which really means *If Canada AND USA do not have data then Stackoverflow should show 'USER'.* So use `AND` instead with parentheses pairs joined by `OR`. See edit and notice the additional conditions. – Parfait Oct 26 '18 at 18:45
  • Thanks! your the best! – excelguy Oct 26 '18 at 18:56