Expression builder syntax, if not null then...

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
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.
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
169
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

Board Regular
Joined
Jan 28, 2014
Messages
169
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,630
Office Version
2013
Platform
Windows
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
Code:
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
Code:
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
Code:
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:
Code:
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
 
Last edited:

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
What about using Between:

Code:
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
 
Last edited:

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,630
Office Version
2013
Platform
Windows
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):

Code:
a>b and c<d or a<b and c>d
Code:
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:
Code:
a > b and c < d or a < b and c > d
 
Last edited:

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
169
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,630
Office Version
2013
Platform
Windows
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

Board Regular
Joined
Jan 28, 2014
Messages
169
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
 

Watch MrExcel Video

Forum statistics

Threads
1,096,321
Messages
5,449,698
Members
405,575
Latest member
Masimo85

This Week's Hot Topics

Top