Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: Expression builder syntax, if not null then...
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Jul 2010
    Posts
    398
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Expression builder syntax, if not null then...

    Quote Originally Posted by Maggie Barr View Post
    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.

  2. #12
    Board Regular
    Join Date
    Jan 2014
    Location
    Maine
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Expression builder syntax, if not null then...

    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.

  3. #13
    Board Regular
    Join Date
    Jan 2014
    Location
    Maine
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Expression builder syntax, if not null then...

    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

  4. #14
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,347
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Expression builder syntax, if not null then...

    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 by xenou; May 14th, 2019 at 12:03 PM. Reason: Fixed the greater than and less thans

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #15
    Board Regular
    Join Date
    Jul 2010
    Posts
    398
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Expression builder syntax, if not null then...

    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 by stumac; May 14th, 2019 at 12:03 PM.

  6. #16
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,347
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Expression builder syntax, if not null then...

    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 cd
    PHP Code:
    a>and c<or a<and c>

    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 by xenou; May 14th, 2019 at 12:14 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  7. #17
    Board Regular
    Join Date
    Jan 2014
    Location
    Maine
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Expression builder syntax, if not null then...

    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

  8. #18
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,347
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Expression builder syntax, if not null then...

    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.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  9. #19
    Board Regular
    Join Date
    Jan 2014
    Location
    Maine
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Expression builder syntax, if not null then...

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •