Welcome to SPN

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

Sign Up Now!

iff statement

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

Tags:
  1. Chey

    Chey
    Expand Collapse
    Guest

    I have a table with the following informtion
    Headers
    FY, CC, LC, Start Date, End Date,
    each year this will be poplulated
    I need a code that will assign the proper information with the proper date.
    Example these are Fiscal numbers

    If date leave is between start date, and start end then that row of
    information. However How do I get it to call from the right row.

    Right now my table looks like this
    FY CC LC Start Date End Date
    06 123 456 7/1/2005 6/30/2006
    07 789 321 7/1/2006 6/30/2007

    so if there trip was in 7/25/05 then it shows 06 123 456
    I hope this makes sense.
    Thanks
    Chey
     
  2. Loading...


  3. schasteen

    schasteen
    Expand Collapse
    Guest

    Try
    =DLookUp("FY","YourTable","#" & [YourField] & "# Between [Start Date] and
    [End Date]") & " " & DLookUp("CC","YourTable","#" & [YourField] & "# Between
    [Start Date] and [End Date]") & " " & DLookUp("LC","YourTable","#" &
    [YourField] & "# Between [Start Date] and [End Date]")

    "Chey" wrote:

    > I have a table with the following informtion
    > Headers
    > FY, CC, LC, Start Date, End Date,
    > each year this will be poplulated
    > I need a code that will assign the proper information with the proper date.
    > Example these are Fiscal numbers
    >
    > If date leave is between start date, and start end then that row of
    > information. However How do I get it to call from the right row.
    >
    > Right now my table looks like this
    > FY CC LC Start Date End Date
    > 06 123 456 7/1/2005 6/30/2006
    > 07 789 321 7/1/2006 6/30/2007
    >
    > so if there trip was in 7/25/05 then it shows 06 123 456
    > I hope this makes sense.
    > Thanks
    > Chey
     
  4. Chey

    Chey
    Expand Collapse
    Guest

    My Table is Named FY but I am not sure what [YourField] is.

    "schasteen" wrote:

    > Try
    > =DLookUp("FY","YourTable","#" & [YourField] & "# Between [Start Date] and
    > [End Date]") & " " & DLookUp("CC","YourTable","#" & [YourField] & "# Between
    > [Start Date] and [End Date]") & " " & DLookUp("LC","YourTable","#" &
    > [YourField] & "# Between [Start Date] and [End Date]")
    >
    > "Chey" wrote:
    >
    > > I have a table with the following informtion
    > > Headers
    > > FY, CC, LC, Start Date, End Date,
    > > each year this will be poplulated
    > > I need a code that will assign the proper information with the proper date.
    > > Example these are Fiscal numbers
    > >
    > > If date leave is between start date, and start end then that row of
    > > information. However How do I get it to call from the right row.
    > >
    > > Right now my table looks like this
    > > FY CC LC Start Date End Date
    > > 06 123 456 7/1/2005 6/30/2006
    > > 07 789 321 7/1/2006 6/30/2007
    > >
    > > so if there trip was in 7/25/05 then it shows 06 123 456
    > > I hope this makes sense.
    > > Thanks
    > > Chey
     
  5. schasteen

    schasteen
    Expand Collapse
    Guest

    The name of the field or control where the 7/25/05 date is.

    "Chey" wrote:

    > My Table is Named FY but I am not sure what [YourField] is.
    >
    > "schasteen" wrote:
    >
    > > Try
    > > =DLookUp("FY","YourTable","#" & [YourField] & "# Between [Start Date] and
    > > [End Date]") & " " & DLookUp("CC","YourTable","#" & [YourField] & "# Between
    > > [Start Date] and [End Date]") & " " & DLookUp("LC","YourTable","#" &
    > > [YourField] & "# Between [Start Date] and [End Date]")
    > >
    > > "Chey" wrote:
    > >
    > > > I have a table with the following informtion
    > > > Headers
    > > > FY, CC, LC, Start Date, End Date,
    > > > each year this will be poplulated
    > > > I need a code that will assign the proper information with the proper date.
    > > > Example these are Fiscal numbers
    > > >
    > > > If date leave is between start date, and start end then that row of
    > > > information. However How do I get it to call from the right row.
    > > >
    > > > Right now my table looks like this
    > > > FY CC LC Start Date End Date
    > > > 06 123 456 7/1/2005 6/30/2006
    > > > 07 789 321 7/1/2006 6/30/2007
    > > >
    > > > so if there trip was in 7/25/05 then it shows 06 123 456
    > > > I hope this makes sense.
    > > > Thanks
    > > > Chey
     
  6. Chey

    Chey
    Expand Collapse
    Guest

    this is what I put and now it gives me an error message.
    =DLookUp("FY","FY","#" & [Date Leave] & "# Between [FY Start Date] and [End
    Date]") & " " & DLookUp("CC","FY","#" & [Date Leave] & "# Between [FY Start
    Date] and [FY End Date]") & " " & LookUp("LC","FY","#" & [Date Leave] & "#
    Between [FY Start Date] and [FY End Date]")

    "schasteen" wrote:

    > Try
    > =DLookUp("FY","YourTable","#" & [YourField] & "# Between [Start Date] and
    > [End Date]") & " " & DLookUp("CC","YourTable","#" & [YourField] & "# Between
    > [Start Date] and [End Date]") & " " & DLookUp("LC","YourTable","#" &
    > [YourField] & "# Between [Start Date] and [End Date]")
    >
    > "Chey" wrote:
    >
    > > I have a table with the following informtion
    > > Headers
    > > FY, CC, LC, Start Date, End Date,
    > > each year this will be poplulated
    > > I need a code that will assign the proper information with the proper date.
    > > Example these are Fiscal numbers
    > >
    > > If date leave is between start date, and start end then that row of
    > > information. However How do I get it to call from the right row.
    > >
    > > Right now my table looks like this
    > > FY CC LC Start Date End Date
    > > 06 123 456 7/1/2005 6/30/2006
    > > 07 789 321 7/1/2006 6/30/2007
    > >
    > > so if there trip was in 7/25/05 then it shows 06 123 456
    > > I hope this makes sense.
    > > Thanks
    > > Chey
     
  7. schasteen

    schasteen
    Expand Collapse
    Guest

    Don't know if a typo from transfering, but the first end date does not have
    FY End Date and the others do.

    =DLookUp("FY","FY","#" & [Date Leave] & "# Between [FY Start Date] and [FY
    End
    Date]") & " " & DLookUp("CC","FY","#" & [Date Leave] & "# Between [FY Start
    Date] and [FY End Date]") & " " & LookUp("LC","FY","#" & [Date Leave] & "#
    Between [FY Start Date] and [FY End Date]")

    "Chey" wrote:

    > this is what I put and now it gives me an error message.
    > =DLookUp("FY","FY","#" & [Date Leave] & "# Between [FY Start Date] and [End
    > Date]") & " " & DLookUp("CC","FY","#" & [Date Leave] & "# Between [FY Start
    > Date] and [FY End Date]") & " " & LookUp("LC","FY","#" & [Date Leave] & "#
    > Between [FY Start Date] and [FY End Date]")
    >
    > "schasteen" wrote:
    >
    > > Try
    > > =DLookUp("FY","YourTable","#" & [YourField] & "# Between [Start Date] and
    > > [End Date]") & " " & DLookUp("CC","YourTable","#" & [YourField] & "# Between
    > > [Start Date] and [End Date]") & " " & DLookUp("LC","YourTable","#" &
    > > [YourField] & "# Between [Start Date] and [End Date]")
    > >
    > > "Chey" wrote:
    > >
    > > > I have a table with the following informtion
    > > > Headers
    > > > FY, CC, LC, Start Date, End Date,
    > > > each year this will be poplulated
    > > > I need a code that will assign the proper information with the proper date.
    > > > Example these are Fiscal numbers
    > > >
    > > > If date leave is between start date, and start end then that row of
    > > > information. However How do I get it to call from the right row.
    > > >
    > > > Right now my table looks like this
    > > > FY CC LC Start Date End Date
    > > > 06 123 456 7/1/2005 6/30/2006
    > > > 07 789 321 7/1/2006 6/30/2007
    > > >
    > > > so if there trip was in 7/25/05 then it shows 06 123 456
    > > > I hope this makes sense.
    > > > Thanks
    > > > Chey
     
  8. Chey

    Chey
    Expand Collapse
    Guest

    now it tells me that I have the wrong amount of arguments.

    "schasteen" wrote:

    > Don't know if a typo from transfering, but the first end date does not have
    > FY End Date and the others do.
    >
    > =DLookUp("FY","FY","#" & [Date Leave] & "# Between [FY Start Date] and [FY
    > End
    > Date]") & " " & DLookUp("CC","FY","#" & [Date Leave] & "# Between [FY Start
    > Date] and [FY End Date]") & " " & LookUp("LC","FY","#" & [Date Leave] & "#
    > Between [FY Start Date] and [FY End Date]")
    >
    > "Chey" wrote:
    >
    > > this is what I put and now it gives me an error message.
    > > =DLookUp("FY","FY","#" & [Date Leave] & "# Between [FY Start Date] and [End
    > > Date]") & " " & DLookUp("CC","FY","#" & [Date Leave] & "# Between [FY Start
    > > Date] and [FY End Date]") & " " & LookUp("LC","FY","#" & [Date Leave] & "#
    > > Between [FY Start Date] and [FY End Date]")
    > >
    > > "schasteen" wrote:
    > >
    > > > Try
    > > > =DLookUp("FY","YourTable","#" & [YourField] & "# Between [Start Date] and
    > > > [End Date]") & " " & DLookUp("CC","YourTable","#" & [YourField] & "# Between
    > > > [Start Date] and [End Date]") & " " & DLookUp("LC","YourTable","#" &
    > > > [YourField] & "# Between [Start Date] and [End Date]")
    > > >
    > > > "Chey" wrote:
    > > >
    > > > > I have a table with the following informtion
    > > > > Headers
    > > > > FY, CC, LC, Start Date, End Date,
    > > > > each year this will be poplulated
    > > > > I need a code that will assign the proper information with the proper date.
    > > > > Example these are Fiscal numbers
    > > > >
    > > > > If date leave is between start date, and start end then that row of
    > > > > information. However How do I get it to call from the right row.
    > > > >
    > > > > Right now my table looks like this
    > > > > FY CC LC Start Date End Date
    > > > > 06 123 456 7/1/2005 6/30/2006
    > > > > 07 789 321 7/1/2006 6/30/2007
    > > > >
    > > > > so if there trip was in 7/25/05 then it shows 06 123 456
    > > > > I hope this makes sense.
    > > > > Thanks
    > > > > Chey
     
  9. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 13 Jul 2006 14:07:02 -0700, Chey
    <Chey@discussions.microsoft.com> wrote:

    >now it tells me that I have the wrong amount of arguments.


    There are some extra octothorpes in there and a lot of misplaced
    quotes: you don't need # around the name of the field being searched,
    just around the date literals. Try

    =DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    #" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
    Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
    & LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    #" & [FY End Date] & "#")

    For reasonable start and end dates, the criteria for the DLookups
    (once they've been evaluated) might look like

    [Date Leave] BETWEEN #7/1/2006# AND #7/31/2006#


    John W. Vinson[MVP]
     
  10. Chey

    Chey
    Expand Collapse
    Guest

    Hello,
    It still tells me that I have the wrong number of aurguments.
    I copied and pasted what you gave me.
    =DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    #" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
    Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
    & LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    #" & [FY End Date] & "#")

    I pasted it in an unbound field. Is this right?

    "John Vinson" wrote:

    > On Thu, 13 Jul 2006 14:07:02 -0700, Chey
    > <Chey@discussions.microsoft.com> wrote:
    >
    > >now it tells me that I have the wrong amount of arguments.

    >
    > There are some extra octothorpes in there and a lot of misplaced
    > quotes: you don't need # around the name of the field being searched,
    > just around the date literals. Try
    >
    > =DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    > #" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
    > Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
    > & LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    > #" & [FY End Date] & "#")
    >
    > For reasonable start and end dates, the criteria for the DLookups
    > (once they've been evaluated) might look like
    >
    > [Date Leave] BETWEEN #7/1/2006# AND #7/31/2006#
    >
    >
    > John W. Vinson[MVP]
    >
    >
    >
     
  11. Chey

    Chey
    Expand Collapse
    Guest

    Now I am thinking of something else.
    I have 3 columns. FY CC and LC
    In the first field I need it to return FY If it is between FY Start Date and
    FY End date.
    Then the sencond field it needs ot rertun LC "
    "
    then the thrid field needs to return CC "
    "
    so I tried this
    =DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    #" & [FY End Date] & "#")
    But if I am reading it right where does it tell it to show the proper FY it
    could be 06 or 07 depending on the date. When I do it like this it returns
    ERROR
    Thanks

    "John Vinson" wrote:

    > On Thu, 13 Jul 2006 14:07:02 -0700, Chey
    > <Chey@discussions.microsoft.com> wrote:
    >
    > >now it tells me that I have the wrong amount of arguments.

    >
    > There are some extra octothorpes in there and a lot of misplaced
    > quotes: you don't need # around the name of the field being searched,
    > just around the date literals. Try
    >
    > =DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    > #" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
    > Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
    > & LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    > #" & [FY End Date] & "#")
    >
    > For reasonable start and end dates, the criteria for the DLookups
    > (once they've been evaluated) might look like
    >
    > [Date Leave] BETWEEN #7/1/2006# AND #7/31/2006#
    >
    >
    > John W. Vinson[MVP]
    >
    >
    >
     
  12. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    such a complex statement is better of in VBA code, me thinks
    (hopeless to debug) & reliant on US Date Format (to top it of)

    Pieter

    "Chey" <Chey@discussions.microsoft.com> wrote in message
    news:F96EA3DC-6E88-418F-BA45-392C34844F3E@microsoft.com...
    > Hello,
    > It still tells me that I have the wrong number of aurguments.
    > I copied and pasted what you gave me.
    > =DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    > #" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
    > Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
    > & LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    > #" & [FY End Date] & "#")
    >
    > I pasted it in an unbound field. Is this right?
    >
    > "John Vinson" wrote:
    >
    >> On Thu, 13 Jul 2006 14:07:02 -0700, Chey
    >> <Chey@discussions.microsoft.com> wrote:
    >>
    >> >now it tells me that I have the wrong amount of arguments.

    >>
    >> There are some extra octothorpes in there and a lot of misplaced
    >> quotes: you don't need # around the name of the field being searched,
    >> just around the date literals. Try
    >>
    >> =DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    >> #" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
    >> Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
    >> & LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    >> #" & [FY End Date] & "#")
    >>
    >> For reasonable start and end dates, the criteria for the DLookups
    >> (once they've been evaluated) might look like
    >>
    >> [Date Leave] BETWEEN #7/1/2006# AND #7/31/2006#
    >>
    >>
    >> John W. Vinson[MVP]
    >>
    >>
    >>
     
  13. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    such a complex statement is better of in VBA code, me thinks
    (hopeless to debug) & reliant on US Date Format (to top it of)

    Pieter

    "Chey" <Chey@discussions.microsoft.com> wrote in message
    news:F96EA3DC-6E88-418F-BA45-392C34844F3E@microsoft.com...
    > Hello,
    > It still tells me that I have the wrong number of aurguments.
    > I copied and pasted what you gave me.
    > =DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    > #" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
    > Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
    > & LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    > #" & [FY End Date] & "#")
    >
    > I pasted it in an unbound field. Is this right?
    >
    > "John Vinson" wrote:
    >
    >> On Thu, 13 Jul 2006 14:07:02 -0700, Chey
    >> <Chey@discussions.microsoft.com> wrote:
    >>
    >> >now it tells me that I have the wrong amount of arguments.

    >>
    >> There are some extra octothorpes in there and a lot of misplaced
    >> quotes: you don't need # around the name of the field being searched,
    >> just around the date literals. Try
    >>
    >> =DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    >> #" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
    >> Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
    >> & LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    >> #" & [FY End Date] & "#")
    >>
    >> For reasonable start and end dates, the criteria for the DLookups
    >> (once they've been evaluated) might look like
    >>
    >> [Date Leave] BETWEEN #7/1/2006# AND #7/31/2006#
    >>
    >>
    >> John W. Vinson[MVP]
    >>
    >>
    >>




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4367 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  14. Chey

    Chey
    Expand Collapse
    Guest

    would you be able to walk me through something so I can get this to visually
    look right. I have something else that is similar that I could use it on.
    Thanks

    "Pieter Wijnen" wrote:

    > such a complex statement is better of in VBA code, me thinks
    > (hopeless to debug) & reliant on US Date Format (to top it of)
    >
    > Pieter
    >
    > "Chey" <Chey@discussions.microsoft.com> wrote in message
    > news:F96EA3DC-6E88-418F-BA45-392C34844F3E@microsoft.com...
    > > Hello,
    > > It still tells me that I have the wrong number of aurguments.
    > > I copied and pasted what you gave me.
    > > =DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    > > #" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
    > > Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
    > > & LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    > > #" & [FY End Date] & "#")
    > >
    > > I pasted it in an unbound field. Is this right?
    > >
    > > "John Vinson" wrote:
    > >
    > >> On Thu, 13 Jul 2006 14:07:02 -0700, Chey
    > >> <Chey@discussions.microsoft.com> wrote:
    > >>
    > >> >now it tells me that I have the wrong amount of arguments.
    > >>
    > >> There are some extra octothorpes in there and a lot of misplaced
    > >> quotes: you don't need # around the name of the field being searched,
    > >> just around the date literals. Try
    > >>
    > >> =DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    > >> #" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
    > >> Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
    > >> & LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    > >> #" & [FY End Date] & "#")
    > >>
    > >> For reasonable start and end dates, the criteria for the DLookups
    > >> (once they've been evaluated) might look like
    > >>
    > >> [Date Leave] BETWEEN #7/1/2006# AND #7/31/2006#
    > >>
    > >>
    > >> John W. Vinson[MVP]
    > >>
    > >>
    > >>

    >
    >
    >
    > --
    > ----------------------------------------
    > I am using the free version of SPAMfighter for private users.
    > It has removed 4367 spam emails to date.
    > Paying users do not have this message in their emails.
    > Get the free SPAMfighter here: http://www.spamfighter.com/len
    >
    >
    >
     
  15. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 26 Jul 2006 16:23:01 -0700, Chey
    <Chey@discussions.microsoft.com> wrote:

    >Hello,
    >It still tells me that I have the wrong number of aurguments.
    >I copied and pasted what you gave me.
    >=DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    >#" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
    >Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
    >& LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
    >#" & [FY End Date] & "#")


    OK, let's break this down:

    =DLookUp("FY","FY",
    "[Date Leave] Between #" & [FY Start Date]
    & "# and #" & [FY End Date] & "#")
    & " "
    & DLookUp("CC","FY",
    "[Date Leave] Between # " & [FY Start Date]
    & "# and #" & [FY End Date] & "#")
    & " " & LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] &

    ^^^^^^

    There you are. Apparently a typo - should be DLookUp. I don't know if
    there is a Lookup function in your database, but perhaps there is and
    it requires a different number of arguments.

    "# and #" & [FY End Date] & "#")

    John W. Vinson[MVP]
     

Share This Page