Page 1 of 2 12 LastLast
Results 1 to 10 of 19

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

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

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

    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

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

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

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

    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

  3. #3
    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,
    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 by Maggie Barr; May 14th, 2019 at 11:08 AM.

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

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

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

    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. #5
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,338
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

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

    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.[END_Date_VALUE],"OUTSIDE","GOOD RECORD") AS Expr1
    FROM Table2 AS T;

    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

  6. #6
    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,
    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

  7. #7
    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,
    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

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

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

    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.

    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. #9
    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...

    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.


  10. #10
    Board Regular
    Join Date
    Jul 2010
    Posts
    396
    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
    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,Enter nested IIF here,Enter Else here)
    The way access works if it encounters a null value with + the final total will be null: 1 + Null = Null.
    Last edited by stumac; May 14th, 2019 at 11:38 AM.

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
  •