Combining two IIf statements

[trainbif]

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

 

[Ofer]

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

 

[KARL DEWEY]

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]

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

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

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

 

[KARL DEWEY]

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

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

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

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

 

[trainbif]

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

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

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

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


:

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