If Statements in Access

[bsc]

Hello
I am currently using the following IF statement in Excel
however i will need to convert it so that it will work for me in
Access

=IF(Expiry Date<>"",IF(Expiry Date <Todays Date,"Cease Working
",IF(todays date> Expiry Date -60,"Submit New Application"," ")),"")

Can someone please help
Thanks
Longbeach

 

[Al Campagna]

LongBeach,
I take it you're updating a text control with text according to the
value of [Expiry Date] vs. the current system Date...

And, you don't need the If [Expiry Date] <> "". If it's not null... it
will evaluate... if it is nullnot... the result is null.
And, you don't need [Todays Date], as a field value... just use the
Date() function.
You don't indicate the result you want if [Expiry Date] = Date().

IIF([Expiry Date] < Date(), "Cease Working", "Submit New Application")
Your way...
or try this instead...
IIF([Expiry Date] <= Date(), "Cease Working", "Submit New Application")
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Longbeach]

Sorry about the previous Email i am so eager to get response that i did not
look any futher then the first time i saw Longbeach.
I am so new at this it is scary
To answer you question result i need is this
if the expiry date is not entered then the employee must cease working
if the expiry date is 2months prior to todays date then they must re-apply
for their blue card.
i will try what you have posted and let you know how i got
Thanks again
Longbeach

--
Help Needed by Newbie

LongBeach,
I take it you're updating a text control with text according to the
value of [Expiry Date] vs. the current system Date...

And, you don't need the If [Expiry Date] <> "". If it's not null... it
will evaluate... if it is nullnot... the result is null.
And, you don't need [Todays Date], as a field value... just use the
Date() function.
You don't indicate the result you want if [Expiry Date] = Date().

IIF([Expiry Date] < Date(), "Cease Working", "Submit New Application")
Your way...
or try this instead...
IIF([Expiry Date] <= Date(), "Cease Working", "Submit New Application")
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Hello
I am currently using the following IF statement in Excel
however i will need to convert it so that it will work for me in
Access

=IF(Expiry Date<>"",IF(Expiry Date <Todays Date,"Cease Working
",IF(todays date> Expiry Date -60,"Submit New Application"," ")),"")

Can someone please help
Thanks
Longbeach

 

[Al Campagna]

LongBeach,
When you create as IIF statement, you need to define your logic so that
any possible [Expiry Date] value will evaluate.
In other words, you haven't defined what happens for ANY possible [Expiry
Date]

if the expiry date is not entered then the employee must cease working
if the expiry date is 2months prior to todays date then they must re-apply
for their blue card.

That's OK so far, but...
1. What do you want to see if Expiry Date is just 2 weeks less than
Date()?
2. What do you want to see if Expiry Date is greater than Date()?
3. Would you call that "Active", or something like that?

I'll assume #3 for now...
= IIF(IsNull([Expiry Date]) or [Expiry Date] = "", "Cease Working",
IIF([Expiry Date] < DateAdd("m", -2, Date()), "Reapply", "Active"))

Didn't have time to test, but using the logic above... that should do it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Sorry about the previous Email i am so eager to get response that i did
not
look any futher then the first time i saw Longbeach.
I am so new at this it is scary
To answer you question result i need is this
if the expiry date is not entered then the employee must cease working
if the expiry date is 2months prior to todays date then they must re-apply
for their blue card.
i will try what you have posted and let you know how i got
Thanks again
Longbeach

--
Help Needed by Newbie

LongBeach,
I take it you're updating a text control with text according to the
value of [Expiry Date] vs. the current system Date...

And, you don't need the If [Expiry Date] <> "". If it's not null...
it
will evaluate... if it is nullnot... the result is null.
And, you don't need [Todays Date], as a field value... just use the
Date() function.
You don't indicate the result you want if [Expiry Date] = Date().

IIF([Expiry Date] < Date(), "Cease Working", "Submit New Application")
Your way...
or try this instead...
IIF([Expiry Date] <= Date(), "Cease Working", "Submit New
Application")
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Hello
I am currently using the following IF statement in Excel
however i will need to convert it so that it will work for me in
Access

=IF(Expiry Date<>"",IF(Expiry Date <Todays Date,"Cease Working
",IF(todays date> Expiry Date -60,"Submit New Application"," ")),"")

Can someone please help
Thanks
Longbeach

 

[Longbeach]

Hi Al
the iif statement below worked to some extent that all dates entered came up
Reapply regardless.
what i am looking for is an either or statement
it is either "Cease Working" or "Reapply"
Are you able to assist with this if you need more information please email
me at
(e-mail address removed)

thanks
Lina

--
Help Needed by Newbie

LongBeach,
When you create as IIF statement, you need to define your logic so that
any possible [Expiry Date] value will evaluate.
In other words, you haven't defined what happens for ANY possible [Expiry
Date]

if the expiry date is not entered then the employee must cease working
if the expiry date is 2months prior to todays date then they must re-apply
for their blue card.

That's OK so far, but...
1. What do you want to see if Expiry Date is just 2 weeks less than
Date()?
2. What do you want to see if Expiry Date is greater than Date()?
3. Would you call that "Active", or something like that?

I'll assume #3 for now...
= IIF(IsNull([Expiry Date]) or [Expiry Date] = "", "Cease Working",
IIF([Expiry Date] < DateAdd("m", -2, Date()), "Reapply", "Active"))

Didn't have time to test, but using the logic above... that should do it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Sorry about the previous Email i am so eager to get response that i did
not
look any futher then the first time i saw Longbeach.
I am so new at this it is scary
To answer you question result i need is this
if the expiry date is not entered then the employee must cease working
if the expiry date is 2months prior to todays date then they must re-apply
for their blue card.
i will try what you have posted and let you know how i got
Thanks again
Longbeach

--
Help Needed by Newbie

LongBeach,
I take it you're updating a text control with text according to the
value of [Expiry Date] vs. the current system Date...

And, you don't need the If [Expiry Date] <> "". If it's not null...
it
will evaluate... if it is nullnot... the result is null.
And, you don't need [Todays Date], as a field value... just use the
Date() function.
You don't indicate the result you want if [Expiry Date] = Date().

IIF([Expiry Date] < Date(), "Cease Working", "Submit New Application")
Your way...
or try this instead...
IIF([Expiry Date] <= Date(), "Cease Working", "Submit New
Application")
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Hello
I am currently using the following IF statement in Excel
however i will need to convert it so that it will work for me in
Access

=IF(Expiry Date<>"",IF(Expiry Date <Todays Date,"Cease Working
",IF(todays date> Expiry Date -60,"Submit New Application"," ")),"")

Can someone please help
Thanks
Longbeach

 

[John W. Vinson]

Hi Al
the iif statement below worked to some extent that all dates entered came up
Reapply regardless.
what i am looking for is an either or statement
it is either "Cease Working" or "Reapply"
Are you able to assist with this if you need more information please email
me at
(e-mail address removed)

thanks
Lina

Reread Al's question.

There are dates which - by your criteria - neither "reapply" nor "Cease
working" is a correct answer.

Could you give an example of some of the dates in your table, and the actual
SQL string of the query that you are using?

John W. Vinson [MVP]

 

[Longbeach]

Sample of the dates that are on the Excel Spreadsheet that i am trying to
convert to
access

319912/2 24-Jul-09
82426/3 12-Jul-09
118680/2 22-Dec-07 Submit New Application
114934/2 01-Feb-08 Submit New Application
521691/1 21-Jun-28
339757/2 29-Jun-09
339757/2 29-Jun-09
339757/2 29-Jun-09
405180/1 10-Nov-07 Cease Working
175021/2 29-Nov-07 Cease Working
187895/2 13-Feb-08
Thanks again for all your help
Longbeach

 

[John W. Vinson]

Sample of the dates that are on the Excel Spreadsheet that i am trying to
convert to
access

319912/2 24-Jul-09
82426/3 12-Jul-09
118680/2 22-Dec-07 Submit New Application
114934/2 01-Feb-08 Submit New Application
521691/1 21-Jun-28
339757/2 29-Jun-09
339757/2 29-Jun-09
339757/2 29-Jun-09
405180/1 10-Nov-07 Cease Working
175021/2 29-Nov-07 Cease Working
187895/2 13-Feb-08
Thanks again for all your help
Longbeach

Please explain the logic again. I was going back through the various replies
and discussions in the thread and I'm now thoroughly confused. 01-Feb-08 is
more than two months in the future - but so is 29-Jun-09. I presume that
21-Jun-28 is a typo; or is it 2028, or 1928?

If the date is between 60 days ago and today, what do you want to see?

If the date is older than 60 days ago, what do you want to see?

If the date is in the future, what do you want to see?

John W. Vinson [MVP]

 

[Longbeach]

Hi John
I understand why it is confusing i will try to explain
If the date is in the past i need to see "Cease Working" (Because without a
blue card you should not be working)
if the date is 60 days ago and today i need to see "Submit New Application"
(Because it will take some time for your new card to arrive)
If the date is in the future i will need to see "Blank Space" (Because they
are free to work until their blue card is nearing Expiration)
hope this will be able to make it a little clearer for you
dont hesitate to contact me directly at (e-mail address removed)

Thanks
Lina

 

[John W. Vinson]

Hi John
I understand why it is confusing i will try to explain
If the date is in the past i need to see "Cease Working" (Because without a
blue card you should not be working)
if the date is 60 days ago and today i need to see "Submit New Application"
(Because it will take some time for your new card to arrive)

But if it is between 60 days ago and today *THEN IT IS IN THE PAST*.

These two requirements *are contradictory*. November 30 is in the past; it is
also between 60 days ago and today.

If the date is in the future i will need to see "Blank Space" (Because they
are free to work until their blue card is nearing Expiration)

If you mean between today and 60 days *in the future* rather than 60 days ago,
try

ActionNeeded: Switch([Expiry Date] < Date(), "Cease Working", [Expiry Date] <
DateAdd("d", 60, Date()), "Submit New Application", True, "")

as a calculated field in the query.

hope this will be able to make it a little clearer for you
dont hesitate to contact me directly at (e-mail address removed)

Private email support is available to paying customers. For free support, ask
here, come here for the answer.

John W. Vinson [MVP]