Welcome to SPN

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

Sign Up Now!

Combining two IIf statements

Discussion in 'Information Technology' started by trainbif, Nov 18, 2005.

  1. trainbif

    trainbif
    Expand Collapse
    Guest

    I am trying to use Access connected to a database. I have created a form
    thru Access and on it I have the normal fields i.e. last name, first name,
    account #, start date, termination date, etc.... and I also created 3 new
    fields that I am trying to use for billing purposes. one field is Total
    billing days, which is using the date dif and is working correctly. The
    other two fields is "Billing_start" and "billing_end" which I am trying use
    just for current month. I have entered the formula
    1. =IIf ([dt_started]<â€9/30/2005â€, [dt_started],â€10/01/2005â€) which looks
    for dates less that "9/30/2005" and makes it another date of "10/01/2005" for
    current month billing purposes. It works fine.
    2. =IIf ([dt_started]<â€9/30/2005â€, â€10/01/2005â€, [dt_started]) is the second
    formula I am using to look for dates within the current month of billing and
    make them the actual start dates (i.e. 12 Oct 2005).... It works fine.

    I have tried to combine the two formulas using "And" "Or" statement and not
    working together???? Any Ideas????
    Thanks
     
  2. Loading...

    Similar Threads Forum Date
    India IIFA 2014: ‘Bhaag Milkha Bhaag’ wins 9 technical awards! Breaking News Mar 5, 2014

  3. Ofer

    Ofer
    Expand Collapse
    Guest

    What do you mean by combining the two formula's , they have the same
    criteria, but each one of them return a different value, it doesn't make
    sense joining them.

    If first criteria will pass, it will never go to the next one, and if the
    first criteria wont pass then the second one wont pass either because they
    have the same criteria.
    As a resault the second criteria will never happen

    I hope that make sense to you
    --
    I hope that helped
    Good Luck


    "trainbif" wrote:

    > I am trying to use Access connected to a database. I have created a form
    > thru Access and on it I have the normal fields i.e. last name, first name,
    > account #, start date, termination date, etc.... and I also created 3 new
    > fields that I am trying to use for billing purposes. one field is Total
    > billing days, which is using the date dif and is working correctly. The
    > other two fields is "Billing_start" and "billing_end" which I am trying use
    > just for current month. I have entered the formula
    > 1. =IIf ([dt_started]<â€9/30/2005â€, [dt_started],â€10/01/2005â€) which looks
    > for dates less that "9/30/2005" and makes it another date of "10/01/2005" for
    > current month billing purposes. It works fine.
    > 2. =IIf ([dt_started]<â€9/30/2005â€, â€10/01/2005â€, [dt_started]) is the second
    > formula I am using to look for dates within the current month of billing and
    > make them the actual start dates (i.e. 12 Oct 2005).... It works fine.
    >
    > I have tried to combine the two formulas using "And" "Or" statement and not
    > working together???? Any Ideas????
    > Thanks
    >
    >
    >
    >
     
  4. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Seems like you want the following --
    =IIf ([dt_started]<â€9/30/2005â€, [dt_started], IIf ([dt_started]>â€9/30/2005â€,
    â€10/01/2005â€, [dt_started]) )

    But this will do the same --
    =IIf ([dt_started]<â€9/30/2005â€, [dt_started], â€10/1//2005†)


    "trainbif" wrote:

    > I am trying to use Access connected to a database. I have created a form
    > thru Access and on it I have the normal fields i.e. last name, first name,
    > account #, start date, termination date, etc.... and I also created 3 new
    > fields that I am trying to use for billing purposes. one field is Total
    > billing days, which is using the date dif and is working correctly. The
    > other two fields is "Billing_start" and "billing_end" which I am trying use
    > just for current month. I have entered the formula
    > 1. =IIf ([dt_started]<â€9/30/2005â€, [dt_started],â€10/01/2005â€) which looks
    > for dates less that "9/30/2005" and makes it another date of "10/01/2005" for
    > current month billing purposes. It works fine.
    > 2. =IIf ([dt_started]<â€9/30/2005â€, â€10/01/2005â€, [dt_started]) is the second
    > formula I am using to look for dates within the current month of billing and
    > make them the actual start dates (i.e. 12 Oct 2005).... It works fine.
    >
    > I have tried to combine the two formulas using "And" "Or" statement and not
    > working together???? Any Ideas????
    > Thanks
    >
    >
    >
    >
     
  5. trainbif

    trainbif
    Expand Collapse
    Guest

    Hi Karl,
    Your second formula gives me the correct days if the account was opened
    prior to the billing month, however for any clients that the account was
    opened within the month (i.e. 12 October) it still gives me a total of 31
    days for the month????
    Bob

    "KARL DEWEY" wrote:

    > Seems like you want the following --
    > =IIf ([dt_started]<â€9/30/2005â€, [dt_started], IIf ([dt_started]>â€9/30/2005â€,
    > â€10/01/2005â€, [dt_started]) )
    >
    > But this will do the same --
    > =IIf ([dt_started]<â€9/30/2005â€, [dt_started], â€10/1//2005†)
    >
    >
    > "trainbif" wrote:
    >
    > > I am trying to use Access connected to a database. I have created a form
    > > thru Access and on it I have the normal fields i.e. last name, first name,
    > > account #, start date, termination date, etc.... and I also created 3 new
    > > fields that I am trying to use for billing purposes. one field is Total
    > > billing days, which is using the date dif and is working correctly. The
    > > other two fields is "Billing_start" and "billing_end" which I am trying use
    > > just for current month. I have entered the formula
    > > 1. =IIf ([dt_started]<â€9/30/2005â€, [dt_started],â€10/01/2005â€) which looks
    > > for dates less that "9/30/2005" and makes it another date of "10/01/2005" for
    > > current month billing purposes. It works fine.
    > > 2. =IIf ([dt_started]<â€9/30/2005â€, â€10/01/2005â€, [dt_started]) is the second
    > > formula I am using to look for dates within the current month of billing and
    > > make them the actual start dates (i.e. 12 Oct 2005).... It works fine.
    > >
    > > I have tried to combine the two formulas using "And" "Or" statement and not
    > > working together???? Any Ideas????
    > > Thanks
    > >
    > >
    > >
    > >
     
  6. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    I do not follow. Are you using this to determine billing days?
    Post your SQL statement.

    "trainbif" wrote:

    > Hi Karl,
    > Your second formula gives me the correct days if the account was opened
    > prior to the billing month, however for any clients that the account was
    > opened within the month (i.e. 12 October) it still gives me a total of 31
    > days for the month????
    > Bob
    >
    > "KARL DEWEY" wrote:
    >
    > > Seems like you want the following --
    > > =IIf ([dt_started]<â€9/30/2005â€, [dt_started], IIf ([dt_started]>â€9/30/2005â€,
    > > â€10/01/2005â€, [dt_started]) )
    > >
    > > But this will do the same --
    > > =IIf ([dt_started]<â€9/30/2005â€, [dt_started], â€10/1//2005†)
    > >
    > >
    > > "trainbif" wrote:
    > >
    > > > I am trying to use Access connected to a database. I have created a form
    > > > thru Access and on it I have the normal fields i.e. last name, first name,
    > > > account #, start date, termination date, etc.... and I also created 3 new
    > > > fields that I am trying to use for billing purposes. one field is Total
    > > > billing days, which is using the date dif and is working correctly. The
    > > > other two fields is "Billing_start" and "billing_end" which I am trying use
    > > > just for current month. I have entered the formula
    > > > 1. =IIf ([dt_started]<â€9/30/2005â€, [dt_started],â€10/01/2005â€) which looks
    > > > for dates less that "9/30/2005" and makes it another date of "10/01/2005" for
    > > > current month billing purposes. It works fine.
    > > > 2. =IIf ([dt_started]<â€9/30/2005â€, â€10/01/2005â€, [dt_started]) is the second
    > > > formula I am using to look for dates within the current month of billing and
    > > > make them the actual start dates (i.e. 12 Oct 2005).... It works fine.
    > > >
    > > > I have tried to combine the two formulas using "And" "Or" statement and not
    > > > working together???? Any Ideas????
    > > > Thanks
    > > >
    > > >
    > > >
    > > >
     
  7. trainbif

    trainbif
    Expand Collapse
    Guest

    Hi Karl,
    I have an ODBC connection for Access to my database on the server. I have
    made a query containing all the personal stuff like name, address, equipment
    type, start date and termination dates. I then made a form in Access using a
    query that I had made, and then added 3 more fields to the form:
    1. "Total_Blling Days" and the syntax is =Date
    Diff("d",[Bill_Start],[Bill_End]

    2. "Bill_Start" and the syntax is
    First Formula =IIf([dt_started]<"10/31/2005",[dt_started],"11/01/2005")
    (this will make any account opened in previous month a new start day of
    11/01/2005 for billing purposes.
    Second Formula is =IIf([dt_started]<"10/31/2005","11/01/2005",[dt_started])
    (this will make any account opened in current month their actual start date
    for billing purposes for billing purposes.

    3. My third field I created is :Bill_End" and the syntax is =IIf
    ([dt_started] is Null,"11/30/2005",[dt_closed])

    Everything works good with the exception of the [dt_started] field. Each
    oone of the formulas work good on their own, but when I use an "And" it cause
    the software to puke out and using an "Or" statement does nothing????? If
    you have any other suggestions on how to do this I would really appreciate
    it!!!!!!
    Bob



    "KARL DEWEY" wrote:

    > I do not follow. Are you using this to determine billing days?
    > Post your SQL statement.
    >
    > "trainbif" wrote:
    >
    > > Hi Karl,
    > > Your second formula gives me the correct days if the account was opened
    > > prior to the billing month, however for any clients that the account was
    > > opened within the month (i.e. 12 October) it still gives me a total of 31
    > > days for the month????
    > > Bob
    > >
    > > "KARL DEWEY" wrote:
    > >
    > > > Seems like you want the following --
    > > > =IIf ([dt_started]<â€9/30/2005â€, [dt_started], IIf ([dt_started]>â€9/30/2005â€,
    > > > â€10/01/2005â€, [dt_started]) )
    > > >
    > > > But this will do the same --
    > > > =IIf ([dt_started]<â€9/30/2005â€, [dt_started], â€10/1//2005†)
    > > >
    > > >
    > > > "trainbif" wrote:
    > > >
    > > > > I am trying to use Access connected to a database. I have created a form
    > > > > thru Access and on it I have the normal fields i.e. last name, first name,
    > > > > account #, start date, termination date, etc.... and I also created 3 new
    > > > > fields that I am trying to use for billing purposes. one field is Total
    > > > > billing days, which is using the date dif and is working correctly. The
    > > > > other two fields is "Billing_start" and "billing_end" which I am trying use
    > > > > just for current month. I have entered the formula
    > > > > 1. =IIf ([dt_started]<â€9/30/2005â€, [dt_started],â€10/01/2005â€) which looks
    > > > > for dates less that "9/30/2005" and makes it another date of "10/01/2005" for
    > > > > current month billing purposes. It works fine.
    > > > > 2. =IIf ([dt_started]<â€9/30/2005â€, â€10/01/2005â€, [dt_started]) is the second
    > > > > formula I am using to look for dates within the current month of billing and
    > > > > make them the actual start dates (i.e. 12 Oct 2005).... It works fine.
    > > > >
    > > > > I have tried to combine the two formulas using "And" "Or" statement and not
    > > > > working together???? Any Ideas????
    > > > > Thanks
    > > > >
    > > > >
    > > > >
    > > > >
     

Share This Page