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

 

[stumac]

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.

This should work, however, normally you would enter it as [QueryName].[FieldName]. If you query contains only one table or query or the field names withing them are not the same then you don't require the qualifier, making it much easier to read.

 

[Maggie Barr]

I actually did account for leap years, it is 1-366.
What I am trying now is:
OBS_DATE_ACCEPTANCE: IIf([eBird_ALL_DATA Query2 Base Data Output.START_Date_VALUE]+[eBird_ALL_DATA Query2 Base Data Output.END_Date_VALUE]=0,"",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"))

I don't want to add in the observation date, as it wouldn't work to not include unacceptable species for the state. I am still having issues, but I think getting closer.

 

[Maggie Barr]

stumac,
Thanks for your reply. I had to write in the query reference as there is another query within the query with the same field name. I can, and will, update it to the better form.
Thanks,
Maggie

 

[xenou]

Okay here's some food for thought again.

Using this table (basically the same, but with numbers now):

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

Some queries:

Query1

select 
	t.ID, 
	IIf(t.[DAY-MONTH_VALUE] is null or t.[START_Date_VALUE] is null or t.[END_Date_VALUE] is null, null, 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 t
order by t.ID

Query2

select 
	t.ID, 
	Null as Expr1
from Table2 t
where
	t.[DAY-MONTH_VALUE] is null or t.[START_Date_VALUE] is null or t.[END_Date_VALUE] is null
union all
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 t
where
	not (t.[DAY-MONTH_VALUE] is null or t.[START_Date_VALUE] is null or t.[END_Date_VALUE] is null)
order by t.ID

Query3

select 
	t.ID, 
	not (t.[DAY-MONTH_VALUE] >= t.[START_Date_VALUE] and t.[DAY-MONTH_VALUE] <= t.[END_Date_VALUE]) as Expr1
from Table2 t
order by t.ID

Results of Query1 and Query2:
--------------------
| ID | Expr1       |
--------------------
|  1 | GOOD RECORD |
|  2 | GOOD RECORD |
|  3 | GOOD RECORD |
|  4 | GOOD RECORD |
|  5 | GOOD RECORD |
|  6 | OUTSIDE     |
|  7 | OUTSIDE     |
|  8 |             |
|  9 |             |
| 10 |             |
--------------------


Results of Query3:
--------------
| ID | Expr1 |
--------------
|  1 |     0 |
|  2 |     0 |
|  3 |     0 |
|  4 |     0 |
|  5 |     0 |
|  6 |    -1 |
|  7 |    -1 |
|  8 |       |
|  9 |       |
| 10 |       |
--------------

So the first query is probably what you are after here (nested IIFs). The second query doesn't use IIF but separates the data into two parts (the part with one or more nulls in the three relevant fields, and the part without one or more nulls). The third query is more succinct and takes advantage of the nulls rather than trying to work around them (so to speak). But we return only True or False. Note that in MSAccess -1 equals True, and 0 equals False.


Edit:
Note that I could throw in a fourth and even more concise version as Query4:

select 
	t.ID, 
	not (t.[DAY-MONTH_VALUE] between t.[START_Date_VALUE] and t.[END_Date_VALUE]) as Expr1
from Table2 t
order by t.ID

This one shows the use of between. Note that with the use of true, false, I guess it would be more natural to reverse your results, so that True = "good record" and False = "bad record". The field name could and should (whatever the choice) provide the semantic key to the value: so it would be named something like IS_INSIDE or IS_NOT_INSIDE

 

[stumac]

What about using Between:

IIf([START_Date_VALUE]+[END_Date_VALUE] Is Null,"",IIf([eBird_ALL_DATA Query2 Base Data Output].[Day-Month_Value] Between [eBird_ALL_DATA Query2 Base Data Output].[Start_Date_Value] And [eBird_ALL_DATA Query2 Base Data Output].[End_Date_Value],"GOOD RECORD","OUTSIDE"))

Had included +1/-1 - dont think it is required

 

[xenou]

This question is actually a good for one for all the little details involved logical and/or, True and False in Access, Null handling, and IIF expressions, as well as between and (for that matter, union queries).

My queries above were all mangled by the < or > signs in the code. So as a note to all, if your code contains > and < signs then use the PHP tags to post it so they aren't interpreted by the browser as part of the HTML markup. Otherwise you have to type in the actual html character entities or go to some other lengths.

I don't know the precise conditions under which this occurs by the way. I think whether or not spaces are in the code makes a difference.


Example (ahaha got it to work (or not work, anyway):

a>b and c<d or a<b and c>d

a>b and c<d or a<b and c>d

Edit - and yes, it seems another solution is you can also add spaces around all your greater than and less than signs:

a > b and c < d or a < b and c > d

 

[Maggie Barr]

xenou,
Sorry for any confusion I may have caused. Below is the code for exactly what I need, and it works (I tested it), and I only have to click on the cell in the column I inserted within my working query and open the expression builder and paste:
OBS_DATE_ACCEPTANCE: IIf([eBird_ALL_DATA Query2 Base Data Output].[START_Date_VALUE] Is Null,"",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"))

It was just a matter of my not knowing the language well enough, and trying too many potential things at once.
Thank you for trying to help, I really appreciate it.
Maggie

 

[xenou]

That's fine, but it probably won't work for cases where Start_Date_value or End_Date_Value is null (then it would return "Good Record" when it should probably also be blank.

 

[Maggie Barr]

xenou,
It actually does work for the cases where the Start_Date_Value is null, it leaves the output cell blank, which is exactly what I want it to do. My first formula made no reference to if the Start_Date_Value was blank/null, so it would put "GOOD RECORD" in the cell. I ran the new code on two new columns I created, one dealing with the Start_Date_Values and one dealing with Buffer_Start_Date_Values, where I apply a second level of acceptance (My boss wants it this way), and the output for those with blank start dates is a blank cell. It does work fine. I appreciate your concern and pointing out potential errors, but it does work.
Thanks,
Maggie