Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

IIf Expression

Discussion in 'Information Technology' started by Levans digital, Jul 28, 2006.

Tags:
  1. Levans digital

    Levans digital
    Expand Collapse
    Guest

    Logically I have 4 fields in a query.

    ReceievedDate
    DeliveredBackDate
    ShippedDate
    StorageDays (Calculated Field)

    When the 3 above Date fields have values I have the stoge days to be
    calculated (DeliveredDate - ReceievedDate)

    Below is my expression:
    StorageDays: IIf(Not IsNull([ReceivedDate]) And ([DeliveredBackDate]) And
    ([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]))

    The problem is that my result field is blank, why?

    Thanks
     
  2. Loading...

    Similar Threads Forum Date
    India IIFA 2014: ‘Bhaag Milkha Bhaag’ wins 9 technical awards! Breaking News Mar 5, 2014
    Cultural Appropriation And Expressions Of Sikhi Convert's Corner Dec 28, 2015
    Canada On Sikh separatism, Harper in India defends freedom of expression Breaking News Nov 8, 2012
    Protecting Students' Rights to Religious Expression Interfaith Dialogues May 30, 2010
    Expressions of Faith Mixing Two Worlds Sikh Youth Sep 1, 2009

  3. xRoachx

    xRoachx
    Expand Collapse
    Guest

    Hi Levans -- Your statement is only checking if the 1st date is not null and
    you don't have a statement to handle the false part of the IIf. Try:

    IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
    Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]),FALSE
    STATEMENT HERE)

    "Levans digital" wrote:

    > Logically I have 4 fields in a query.
    >
    > ReceievedDate
    > DeliveredBackDate
    > ShippedDate
    > StorageDays (Calculated Field)
    >
    > When the 3 above Date fields have values I have the stoge days to be
    > calculated (DeliveredDate - ReceievedDate)
    >
    > Below is my expression:
    > StorageDays: IIf(Not IsNull([ReceivedDate]) And ([DeliveredBackDate]) And
    > ([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]))
    >
    > The problem is that my result field is blank, why?
    >
    > Thanks
     
  4. Levans digital

    Levans digital
    Expand Collapse
    Guest

    Hey thanks,
    But I am kinda new here. If the statment is false I want the field to have
    no value what must I enter to denote that?

    "xRoachx" wrote:

    > Hi Levans -- Your statement is only checking if the 1st date is not null and
    > you don't have a statement to handle the false part of the IIf. Try:
    >
    > IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
    > Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]),FALSE
    > STATEMENT HERE)
    >
    > "Levans digital" wrote:
    >
    > > Logically I have 4 fields in a query.
    > >
    > > ReceievedDate
    > > DeliveredBackDate
    > > ShippedDate
    > > StorageDays (Calculated Field)
    > >
    > > When the 3 above Date fields have values I have the stoge days to be
    > > calculated (DeliveredDate - ReceievedDate)
    > >
    > > Below is my expression:
    > > StorageDays: IIf(Not IsNull([ReceivedDate]) And ([DeliveredBackDate]) And
    > > ([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]))
    > >
    > > The problem is that my result field is blank, why?
    > >
    > > Thanks
     
  5. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
    Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]), Null)

    OR

    IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
    Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]),"")


    "Levans digital" wrote:

    > Hey thanks,
    > But I am kinda new here. If the statment is false I want the field to have
    > no value what must I enter to denote that?
    >
    > "xRoachx" wrote:
    >
    > > Hi Levans -- Your statement is only checking if the 1st date is not null and
    > > you don't have a statement to handle the false part of the IIf. Try:
    > >
    > > IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
    > > Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]),FALSE
    > > STATEMENT HERE)
    > >
    > > "Levans digital" wrote:
    > >
    > > > Logically I have 4 fields in a query.
    > > >
    > > > ReceievedDate
    > > > DeliveredBackDate
    > > > ShippedDate
    > > > StorageDays (Calculated Field)
    > > >
    > > > When the 3 above Date fields have values I have the stoge days to be
    > > > calculated (DeliveredDate - ReceievedDate)
    > > >
    > > > Below is my expression:
    > > > StorageDays: IIf(Not IsNull([ReceivedDate]) And ([DeliveredBackDate]) And
    > > > ([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]))
    > > >
    > > > The problem is that my result field is blank, why?
    > > >
    > > > Thanks
     
  6. Levans digital

    Levans digital
    Expand Collapse
    Guest

    Karl Thanks,

    That solves that problem. Refering back to my original post. I started out
    with 3 date fields. Now I added another date field. However after that
    expression become true and data is added into the fourth date field
    [ReturnedDate) the "False" part of the expression does not occur, instead the
    true part still occurs, why?

    "KARL DEWEY" wrote:

    > IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
    > Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]), Null)
    >
    > OR
    >
    > IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
    > Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]),"")
    >
    >
    > "Levans digital" wrote:
    >
    > > Hey thanks,
    > > But I am kinda new here. If the statment is false I want the field to have
    > > no value what must I enter to denote that?
    > >
    > > "xRoachx" wrote:
    > >
    > > > Hi Levans -- Your statement is only checking if the 1st date is not null and
    > > > you don't have a statement to handle the false part of the IIf. Try:
    > > >
    > > > IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
    > > > Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]),FALSE
    > > > STATEMENT HERE)
    > > >
    > > > "Levans digital" wrote:
    > > >
    > > > > Logically I have 4 fields in a query.
    > > > >
    > > > > ReceievedDate
    > > > > DeliveredBackDate
    > > > > ShippedDate
    > > > > StorageDays (Calculated Field)
    > > > >
    > > > > When the 3 above Date fields have values I have the stoge days to be
    > > > > calculated (DeliveredDate - ReceievedDate)
    > > > >
    > > > > Below is my expression:
    > > > > StorageDays: IIf(Not IsNull([ReceivedDate]) And ([DeliveredBackDate]) And
    > > > > ([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]))
    > > > >
    > > > > The problem is that my result field is blank, why?
    > > > >
    > > > > Thanks
     
  7. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Please re-state your problem giving example of input data, query SQL,
    results, and results you want.

    "Levans digital" wrote:

    > Karl Thanks,
    >
    > That solves that problem. Refering back to my original post. I started out
    > with 3 date fields. Now I added another date field. However after that
    > expression become true and data is added into the fourth date field
    > [ReturnedDate) the "False" part of the expression does not occur, instead the
    > true part still occurs, why?
    >
    > "KARL DEWEY" wrote:
    >
    > > IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
    > > Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]), Null)
    > >
    > > OR
    > >
    > > IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
    > > Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]),"")
    > >
    > >
    > > "Levans digital" wrote:
    > >
    > > > Hey thanks,
    > > > But I am kinda new here. If the statment is false I want the field to have
    > > > no value what must I enter to denote that?
    > > >
    > > > "xRoachx" wrote:
    > > >
    > > > > Hi Levans -- Your statement is only checking if the 1st date is not null and
    > > > > you don't have a statement to handle the false part of the IIf. Try:
    > > > >
    > > > > IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
    > > > > Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]),FALSE
    > > > > STATEMENT HERE)
    > > > >
    > > > > "Levans digital" wrote:
    > > > >
    > > > > > Logically I have 4 fields in a query.
    > > > > >
    > > > > > ReceievedDate
    > > > > > DeliveredBackDate
    > > > > > ShippedDate
    > > > > > StorageDays (Calculated Field)
    > > > > >
    > > > > > When the 3 above Date fields have values I have the stoge days to be
    > > > > > calculated (DeliveredDate - ReceievedDate)
    > > > > >
    > > > > > Below is my expression:
    > > > > > StorageDays: IIf(Not IsNull([ReceivedDate]) And ([DeliveredBackDate]) And
    > > > > > ([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]))
    > > > > >
    > > > > > The problem is that my result field is blank, why?
    > > > > >
    > > > > > Thanks
     
  8. Levans digital

    Levans digital
    Expand Collapse
    Guest

    KARL I FIXED MY PROBLEM WITH THE FOLLOWING LENTHY EXPRESSION AND IT WORKS.
    Storagedays: IIf(Not IsNull([ReceivedDate]) And IsNull([StuffedDate]) And
    IsNull([DeliveredDate]) And IsNull([ReturnedDate]) And Not
    IsNull([DeliveredBackDate]) And IsNull([ReturnedBackDate]) And Not
    IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredBackDate]),"")

    IS THERE ANYWAY TO SHORTEN THIS?

    THE ABOVE EXPRESSION MEETS ONE VARIABLE OF A TOTAL OF 7 EACH OTHER VARIABLE
    WILL BE ABOUT THAT LENTH. IS IT POSSIBLE TO COMBINE ALL OF THEM IN ONE
    EXPRESSION

    EXAMPLE: SOME "ISNULL" WILL BE "NOT ISNULL" AND THE OTHER WAY AROUND.

    WHAT U THINK OR MUST IT BE DONE INCODE?

    THANKS

    "KARL DEWEY" wrote:

    > Please re-state your problem giving example of input data, query SQL,
    > results, and results you want.
    >
    > "Levans digital" wrote:
    >
    > > Karl Thanks,
    > >
    > > That solves that problem. Refering back to my original post. I started out
    > > with 3 date fields. Now I added another date field. However after that
    > > expression become true and data is added into the fourth date field
    > > [ReturnedDate) the "False" part of the expression does not occur, instead the
    > > true part still occurs, why?
    > >
    > > "KARL DEWEY" wrote:
    > >
    > > > IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
    > > > Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]), Null)
    > > >
    > > > OR
    > > >
    > > > IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
    > > > Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]),"")
    > > >
    > > >
    > > > "Levans digital" wrote:
    > > >
    > > > > Hey thanks,
    > > > > But I am kinda new here. If the statment is false I want the field to have
    > > > > no value what must I enter to denote that?
    > > > >
    > > > > "xRoachx" wrote:
    > > > >
    > > > > > Hi Levans -- Your statement is only checking if the 1st date is not null and
    > > > > > you don't have a statement to handle the false part of the IIf. Try:
    > > > > >
    > > > > > IIf(Not IsNull([ReceivedDate]) And Not IsNull([DeliveredBackDate]) And
    > > > > > Not IsNull([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]),FALSE
    > > > > > STATEMENT HERE)
    > > > > >
    > > > > > "Levans digital" wrote:
    > > > > >
    > > > > > > Logically I have 4 fields in a query.
    > > > > > >
    > > > > > > ReceievedDate
    > > > > > > DeliveredBackDate
    > > > > > > ShippedDate
    > > > > > > StorageDays (Calculated Field)
    > > > > > >
    > > > > > > When the 3 above Date fields have values I have the stoge days to be
    > > > > > > calculated (DeliveredDate - ReceievedDate)
    > > > > > >
    > > > > > > Below is my expression:
    > > > > > > StorageDays: IIf(Not IsNull([ReceivedDate]) And ([DeliveredBackDate]) And
    > > > > > > ([ShippedDate]),DateDiff("d",[ReceivedDate],[DeliveredDate]))
    > > > > > >
    > > > > > > The problem is that my result field is blank, why?
    > > > > > >
    > > > > > > Thanks
     

Share This Page