Expression builder syntax, if not null then...

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
188
Hello and thank you in advance if you can help,
I have a column I created that works great:
IIf([eBird_ALL_DATA Query2 Base Data Output.DAY-MONTH_VALUE]<[eBird_ALL_DATA Query2 Base Data Output.START_Date_VALUE] OR [eBird_ALL_DATA Query2 Base Data Output.DAY-MONTH_VALUE]>[eBird_ALL_DATA Query2 Base Data Output.END_Date_VALUE],"OUTSIDE","GOOD RECORD")

However, I actually need it do only do this of a cell within the formula is not blank or null:
OBS_DATE_ACCEPTANCE: IIf([eBird_ALL_DATA Query2 Base Data Output.START_Date_VALUE]<>Null Or "" then ?????
I tried to modify it as above, and many other ways (Else etc.), but I can't seem to get any of them to work. It seems like it should be pretty easy to ask it to look at a cell, and if not blank or null, then do the original IIf statement. For the life of me, I just can't find out how.

Any help is appreciated,
Maggie
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Is the data type of Start_Date_Value and End_Date_Value a DATE data type? Or is it a string (dates stored as strings?)

What is the problem you are getting - is it an error message or is it just "wrong results"? In either case, what is an example of a problem record (if you know of one).

Overall, at first glance it does seem like it should work as is but there are some wrinkles with Nulls (and with IIFs, for that matter).
 
Upvote 0
xenou,
Thank for the response. The Start_Date_Value is a field that is a numeric value for a Day-Month combination for acceptable species in our state from a lookup table, so it is just loaded as a number value in the data, but for species that are not in our state, there will be no number. I only want to run the test to see when an acceptable species is outside an acceptable date range, which my previous formula does, but it is providing "GOOD RECORD" for species not in our state as it is comparing a value for the observation date against nothing, which would be incorrect, and this will get documented as a different flag within the analysis. I don't need it to check both Start_Date_Value and End_Date_Value because if one is blank, the other will be too. I just want to exclude it from the formula if Start_Date_Value is blank or null. It could be totally simple, like I don't have a comma where I should, or I do where I shouldn't.
I tried:
OBS_DATE_ACCEPTANCE: IIf([eBird_ALL_DATA Query2 Base Data Output.START_Date_VALUE]<>Null Or "" then "", Else IIf[eBird_ALL_DATA Query2 Base Data Output.DAY-MONTH_VALUE]<[eBird_ALL_DATA Query2 Base Data Output.START_Date_VALUE] Or [eBird_ALL_DATA Query2 Base Data Output.DAY-MONTH_VALUE]>[eBird_ALL_DATA Query2 Base Data Output.END_Date_VALUE],"OUTSIDE","GOOD RECORD"))

BUT, it just tells me I have a invalid syntax error and does nothing.
Thanks,
Maggie
 
Last edited:
Upvote 0
And now also question 2 - what value is null, the first, second, third, or possibly any or all of them?
 
Upvote 0
And now for some test results ...

Given this table:

------------------------------------------------------------
| ID | DAY-MONTH_VALUE | START_Date_VALUE | END_Date_VALUE |
------------------------------------------------------------
|  1 |       15-Jan-19 |        10-Jan-19 |      20-Jan-19 |
|  2 |       10-Jan-19 |        10-Jan-19 |      20-Jan-19 |
|  3 |       20-Jan-19 |        10-Jan-19 |      20-Jan-19 |
|  4 |       11-Jan-19 |        10-Jan-19 |      20-Jan-19 |
|  5 |       19-Jan-19 |        10-Jan-19 |      20-Jan-19 |
|  6 |       09-Jan-19 |        10-Jan-19 |      20-Jan-19 |
|  7 |       21-Jan-19 |        10-Jan-19 |      20-Jan-19 |
|  8 |                 |        10-Jan-19 |      20-Jan-19 |
|  9 |       15-Jan-19 |                  |      20-Jan-19 |
| 10 |       15-Jan-19 |        10-Jan-19 |                |
------------------------------------------------------------


Your query returns these results:
--------------------
| ID | Expr1       |
--------------------
|  1 | GOOD RECORD |
|  2 | GOOD RECORD |
|  3 | GOOD RECORD |
|  4 | GOOD RECORD |
|  5 | GOOD RECORD |
|  6 | OUTSIDE     |
|  7 | OUTSIDE     |
|  8 | GOOD RECORD |
|  9 | GOOD RECORD |
| 10 | GOOD RECORD |
--------------------


which is probably technically correct (in terms of doing what the query asks) but is also probably not what you wanted (I guess) so the query will need tweaking.

Query I used:
Code:
SELECT T.ID, IIf(t.[DAY-MONTH_VALUE]<t.[START_Date_VALUE] Or t.[DAY-MONTH_VALUE]>t.[END_Date_VALUE],"OUTSIDE","GOOD RECORD") AS Expr1
FROM Table2 AS T;
 
Upvote 0
xenou,
DAY-MONTH_VALUE, is an observation date, never blank or null
START_Date_VALUE, if it is blank so is END_Date_VALUE, no value present for non state species.
So, I would like the formula to look at
START_Date_VALUE and if blank or null, then leave blank, else, run the other iif formula.
Maggie
 
Upvote 0
xenou,
I am sorry if I didn't make it clear, the "values" for dates are just numbers, 1-365. there is no actual date comparisons. I set it up this way to that each Day-Month has a numeric value, like Jan first is 1, Jan second is 2 etc. That way the year is never an involved as I don't want it to be. AS well, it makes working with and comparing values that much easier.
Maggie
 
Upvote 0
Okay, just for starters this syntax is completely invalid:
Code:
[eBird_ALL_DATA Query2 Base Data Output.START_Date_VALUE]

What you mean is:
Code:
[eBird_ALL_DATA Query2 Base Data Output].[START_Date_VALUE]

The first is one long field name with a dot in the middle. The second is a table name and a field name separated by a dot.
 
Upvote 0
Sorry, but I don't see how the syntax is invalid as it worked in my first formula just fine.
IIf([eBird_ALL_DATA Query2 Base Data Output.DAY-MONTH_VALUE]<[eBird_ALL_DATA Query2 Base Data Output.START_Date_VALUE] OR [eBird_ALL_DATA Query2 Base Data Output.DAY-MONTH_VALUE]>[eBird_ALL_DATA Query2 Base Data Output.END_Date_VALUE],"OUTSIDE","GOOD RECORD")

[eBird_ALL_DATA Query2 Base Data Output.DAY-MONTH_VALUE] This is the query name (eBird_ALL_DATA Query2 Base Data Output) to reference the column name (DAY-MONTH_VALUE). I it worked as expected. I just want to modify the formula I sued to that if [eBird_ALL_DATA Query2 Base Data Output.START_Date_VALUE] is blank, leave the cell blank, otherwise run the original formula.

 
Upvote 0
I am sorry if I didn't make it clear, the "values" for dates are just numbers, 1-365.
- What about Leap years?

I think this sounds way more complex than using dates for comparisons.

To answer your original question:

Code:
IIf([DAY-MONTH_VALUE]+[START_Date_VALUE]+[END_Date_VALUE] Is Null,[I][COLOR=#ff0000]E[/COLOR][COLOR=#ff0000]nter nested IIF here[/COLOR][/I],[COLOR=#ff0000][I]Enter Else here[/I][/COLOR])

The way access works if it encounters a null value with + the final total will be null: 1 + Null = Null.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top