Expression builder syntax, if not null then...

[Maggie Barr]

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

 

[xenou]

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).

 

[Maggie Barr]

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

 

[xenou]

And now also question 2 - what value is null, the first, second, third, or possibly any or all of them?

 

[xenou]

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:

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;

 

[Maggie Barr]

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

 

[Maggie Barr]

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

 

[xenou]

Okay, just for starters this syntax is completely invalid:

[eBird_ALL_DATA Query2 Base Data Output.START_Date_VALUE]

What you mean is:

[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.

 

[Maggie Barr]

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.

 

[stumac]

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:

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.