IIf problem - Kautzen

[Kautzen]

I have a query that gets the dates of expirations for trainings that I want
to qualify the answer. Since the information is for three trainings, I get
all or none, so I want to have the word "OK" replace the date it the training
expiration date is greater than the date of the request. CPR is one type of
training, date is the date the request for expirations go through. Can
anyone help me understand why I can not get this IIf statement to work?
IIf([CPR]>[date],"OK",[CPR])

 

[Douglas J. Steele]

It looks as though date is the name of a field in one of your tables. Date
is a reserved word, and should never be used for your own purposes. For a
comprehensive list of names to avoid in Access (as well as a link to a free
utility that will check your application for compliance), see what Allen
Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

Another potential problem is that your IIf statement doesn't handle the case
where either CPR or date is Null.

 

[Kautzen]

So, I will change the name and see how this works. Also, if the date is
null, I want it to print null, so I shall put an or statement once I get the
IIf to work. Thanks for your help. I have been looking through many of the
questions and see when you answer it usually is the best advice. I wll also
keep te badword address for further help.

--
Kautzen

It looks as though date is the name of a field in one of your tables. Date
is a reserved word, and should never be used for your own purposes. For a
comprehensive list of names to avoid in Access (as well as a link to a free
utility that will check your application for compliance), see what Allen
Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

Another potential problem is that your IIf statement doesn't handle the case
where either CPR or date is Null.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a query that gets the dates of expirations for trainings that I want
to qualify the answer. Since the information is for three trainings, I
get
all or none, so I want to have the word "OK" replace the date it the
training
expiration date is greater than the date of the request. CPR is one type
of
training, date is the date the request for expirations go through. Can
anyone help me understand why I can not get this IIf statement to work?
IIf([CPR]>[date],"OK",[CPR])

 

[Douglas J. Steele]

Try something like:

IIf(IsNull([MyDateField]) OR IsNull([CPR]), "Null",
IIf([CPR]>[MyDateField],"OK",[CPR]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So, I will change the name and see how this works. Also, if the date is
null, I want it to print null, so I shall put an or statement once I get
the
IIf to work. Thanks for your help. I have been looking through many of
the
questions and see when you answer it usually is the best advice. I wll
also
keep te badword address for further help.

--
Kautzen

It looks as though date is the name of a field in one of your tables.
Date
is a reserved word, and should never be used for your own purposes. For a
comprehensive list of names to avoid in Access (as well as a link to a
free
utility that will check your application for compliance), see what Allen
Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

Another potential problem is that your IIf statement doesn't handle the
case
where either CPR or date is Null.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a query that gets the dates of expirations for trainings that I
want
to qualify the answer. Since the information is for three trainings, I
get
all or none, so I want to have the word "OK" replace the date it the
training
expiration date is greater than the date of the request. CPR is one
type
of
training, date is the date the request for expirations go through. Can
anyone help me understand why I can not get this IIf statement to work?
IIf([CPR]>[date],"OK",[CPR])

 

[Douglas J. Steele]

Because it isn't obvious, that statement would be all on a single line.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try something like:

IIf(IsNull([MyDateField]) OR IsNull([CPR]), "Null",
IIf([CPR]>[MyDateField],"OK",[CPR]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So, I will change the name and see how this works. Also, if the date is
null, I want it to print null, so I shall put an or statement once I get
the
IIf to work. Thanks for your help. I have been looking through many of
the
questions and see when you answer it usually is the best advice. I wll
also
keep te badword address for further help.

--
Kautzen

It looks as though date is the name of a field in one of your tables.
Date
is a reserved word, and should never be used for your own purposes. For
a
comprehensive list of names to avoid in Access (as well as a link to a
free
utility that will check your application for compliance), see what Allen
Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

Another potential problem is that your IIf statement doesn't handle the
case
where either CPR or date is Null.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query that gets the dates of expirations for trainings that I
want
to qualify the answer. Since the information is for three trainings,
I
get
all or none, so I want to have the word "OK" replace the date it the
training
expiration date is greater than the date of the request. CPR is one
type
of
training, date is the date the request for expirations go through.
Can
anyone help me understand why I can not get this IIf statement to
work?
IIf([CPR]>[date],"OK",[CPR])

 

[Kautzen]

Gives me a data type mismatch in criteria expression. I did change the date
to JFdate and inserted it in the expression below.
IIf(IsNull([JFdate]) OR IsNull([CPR]), "Null",
IIf([CPR]>[JFdate],"OK",[CPR]))
--
Kautzen

Try something like:

IIf(IsNull([MyDateField]) OR IsNull([CPR]), "Null",
IIf([CPR]>[MyDateField],"OK",[CPR]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So, I will change the name and see how this works. Also, if the date is
null, I want it to print null, so I shall put an or statement once I get
the
IIf to work. Thanks for your help. I have been looking through many of
the
questions and see when you answer it usually is the best advice. I wll
also
keep te badword address for further help.

--
Kautzen

It looks as though date is the name of a field in one of your tables.
Date
is a reserved word, and should never be used for your own purposes. For a
comprehensive list of names to avoid in Access (as well as a link to a
free
utility that will check your application for compliance), see what Allen
Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

Another potential problem is that your IIf statement doesn't handle the
case
where either CPR or date is Null.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query that gets the dates of expirations for trainings that I
want
to qualify the answer. Since the information is for three trainings, I
get
all or none, so I want to have the word "OK" replace the date it the
training
expiration date is greater than the date of the request. CPR is one
type
of
training, date is the date the request for expirations go through. Can
anyone help me understand why I can not get this IIf statement to work?
IIf([CPR]>[date],"OK",[CPR])

 

[Kautzen]

Yes, I did it on one line and to make sure I had all of the syntax correct I
copied it and did a paste with changes. I put it in, then had to close to
take a look and make sure that everyplace the JFdate was used it was in date
format, yes. Then I re-opened the query and this is what it is now.
IIf(([Exp Cruda].[CPR])=IsNull([JFdate]) Or ([Exp
Cruda].[CPR])=IsNull([CPR]),"Null",IIf([CPR]>[JFdate],"OK",[CPR])), but still
the same error.

--
Kautzen

Because it isn't obvious, that statement would be all on a single line.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try something like:

IIf(IsNull([MyDateField]) OR IsNull([CPR]), "Null",
IIf([CPR]>[MyDateField],"OK",[CPR]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So, I will change the name and see how this works. Also, if the date is
null, I want it to print null, so I shall put an or statement once I get
the
IIf to work. Thanks for your help. I have been looking through many of
the
questions and see when you answer it usually is the best advice. I wll
also
keep te badword address for further help.

--
Kautzen


:

It looks as though date is the name of a field in one of your tables.
Date
is a reserved word, and should never be used for your own purposes. For
a
comprehensive list of names to avoid in Access (as well as a link to a
free
utility that will check your application for compliance), see what Allen
Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

Another potential problem is that your IIf statement doesn't handle the
case
where either CPR or date is Null.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query that gets the dates of expirations for trainings that I
want
to qualify the answer. Since the information is for three trainings,
I
get
all or none, so I want to have the word "OK" replace the date it the
training
expiration date is greater than the date of the request. CPR is one
type
of
training, date is the date the request for expirations go through.
Can
anyone help me understand why I can not get this IIf statement to
work?
IIf([CPR]>[date],"OK",[CPR])

 

[tuesamlarry]

Is CPR a type of training (Text) or an actual date field? If it's text, you
won't be able to ask if it's greater than a date.
Tuesamlarry

 

[Douglas J. Steele]

Where did you enter it?

What you've got below definitely is incorrect.

It should be

IIf(IsNull([JFdate]) OR IsNull([CPR]), "Null",
IIf([CPR]>[JFdate],"OK",[CPR]))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Yes, I did it on one line and to make sure I had all of the syntax correct
I
copied it and did a paste with changes. I put it in, then had to close to
take a look and make sure that everyplace the JFdate was used it was in
date
format, yes. Then I re-opened the query and this is what it is now.
IIf(([Exp Cruda].[CPR])=IsNull([JFdate]) Or ([Exp
Cruda].[CPR])=IsNull([CPR]),"Null",IIf([CPR]>[JFdate],"OK",[CPR])), but
still
the same error.

--
Kautzen

Because it isn't obvious, that statement would be all on a single line.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try something like:

IIf(IsNull([MyDateField]) OR IsNull([CPR]), "Null",
IIf([CPR]>[MyDateField],"OK",[CPR]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


So, I will change the name and see how this works. Also, if the date
is
null, I want it to print null, so I shall put an or statement once I
get
the
IIf to work. Thanks for your help. I have been looking through many
of
the
questions and see when you answer it usually is the best advice. I
wll
also
keep te badword address for further help.

--
Kautzen


:

It looks as though date is the name of a field in one of your tables.
Date
is a reserved word, and should never be used for your own purposes.
For
a
comprehensive list of names to avoid in Access (as well as a link to
a
free
utility that will check your application for compliance), see what
Allen
Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

Another potential problem is that your IIf statement doesn't handle
the
case
where either CPR or date is Null.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query that gets the dates of expirations for trainings that
I
want
to qualify the answer. Since the information is for three
trainings,
I
get
all or none, so I want to have the word "OK" replace the date it
the
training
expiration date is greater than the date of the request. CPR is
one
type
of
training, date is the date the request for expirations go through.
Can
anyone help me understand why I can not get this IIf statement to
work?
IIf([CPR]>[date],"OK",[CPR])

 

[Kautzen]

I am using it in a query. I see my error, but I think that the data in that
field is date format and I am trying to make it be alpha, that it might not
work there. I will try it in the report field and see if it will work there.
Wish I had your brain!!!! At least for access,
--
Kautzen

Where did you enter it?

What you've got below definitely is incorrect.

It should be

IIf(IsNull([JFdate]) OR IsNull([CPR]), "Null",
IIf([CPR]>[JFdate],"OK",[CPR]))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Yes, I did it on one line and to make sure I had all of the syntax correct
I
copied it and did a paste with changes. I put it in, then had to close to
take a look and make sure that everyplace the JFdate was used it was in
date
format, yes. Then I re-opened the query and this is what it is now.
IIf(([Exp Cruda].[CPR])=IsNull([JFdate]) Or ([Exp
Cruda].[CPR])=IsNull([CPR]),"Null",IIf([CPR]>[JFdate],"OK",[CPR])), but
still
the same error.

--
Kautzen

Because it isn't obvious, that statement would be all on a single line.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Try something like:

IIf(IsNull([MyDateField]) OR IsNull([CPR]), "Null",
IIf([CPR]>[MyDateField],"OK",[CPR]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


So, I will change the name and see how this works. Also, if the date
is
null, I want it to print null, so I shall put an or statement once I
get
the
IIf to work. Thanks for your help. I have been looking through many
of
the
questions and see when you answer it usually is the best advice. I
wll
also
keep te badword address for further help.

--
Kautzen


:

It looks as though date is the name of a field in one of your tables.
Date
is a reserved word, and should never be used for your own purposes.
For
a
comprehensive list of names to avoid in Access (as well as a link to
a
free
utility that will check your application for compliance), see what
Allen
Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

Another potential problem is that your IIf statement doesn't handle
the
case
where either CPR or date is Null.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query that gets the dates of expirations for trainings that
I
want
to qualify the answer. Since the information is for three
trainings,
I
get
all or none, so I want to have the word "OK" replace the date it
the
training
expiration date is greater than the date of the request. CPR is
one
type
of
training, date is the date the request for expirations go through.
Can
anyone help me understand why I can not get this IIf statement to
work?
IIf([CPR]>[date],"OK",[CPR])

 

[Kautzen]

CPR is a name of training, but the data is date. What I think may not work
is the fact it is date and I want it to say OK. Not doing too good on this,
can use help!!
--
Kautzen

Is CPR a type of training (Text) or an actual date field? If it's text, you
won't be able to ask if it's greater than a date.
Tuesamlarry

I have a query that gets the dates of expirations for trainings that I want
to qualify the answer. Since the information is for three trainings, I get
all or none, so I want to have the word "OK" replace the date it the training
expiration date is greater than the date of the request. CPR is one type of
training, date is the date the request for expirations go through. Can
anyone help me understand why I can not get this IIf statement to work?
IIf([CPR]>[date],"OK",[CPR])

 

[Douglas J. Steele]

You might need to wrap the Format function around the final CPR to convert
it to text:

IIf(IsNull([JFdate]) OR IsNull([CPR]), "Null",
IIf([CPR]>[JFdate],"OK",Format([CPR], "yyyy\-mm\-dd")))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am using it in a query. I see my error, but I think that the data in
that
field is date format and I am trying to make it be alpha, that it might
not
work there. I will try it in the report field and see if it will work
there.
Wish I had your brain!!!! At least for access,
--
Kautzen

Where did you enter it?

What you've got below definitely is incorrect.

It should be

IIf(IsNull([JFdate]) OR IsNull([CPR]), "Null",
IIf([CPR]>[JFdate],"OK",[CPR]))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Yes, I did it on one line and to make sure I had all of the syntax
correct
I
copied it and did a paste with changes. I put it in, then had to close
to
take a look and make sure that everyplace the JFdate was used it was in
date
format, yes. Then I re-opened the query and this is what it is now.
IIf(([Exp Cruda].[CPR])=IsNull([JFdate]) Or ([Exp
Cruda].[CPR])=IsNull([CPR]),"Null",IIf([CPR]>[JFdate],"OK",[CPR])), but
still
the same error.

--
Kautzen


:

Because it isn't obvious, that statement would be all on a single
line.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Try something like:

IIf(IsNull([MyDateField]) OR IsNull([CPR]), "Null",
IIf([CPR]>[MyDateField],"OK",[CPR]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


So, I will change the name and see how this works. Also, if the
date
is
null, I want it to print null, so I shall put an or statement once
I
get
the
IIf to work. Thanks for your help. I have been looking through
many
of
the
questions and see when you answer it usually is the best advice. I
wll
also
keep te badword address for further help.

--
Kautzen


:

It looks as though date is the name of a field in one of your
tables.
Date
is a reserved word, and should never be used for your own
purposes.
For
a
comprehensive list of names to avoid in Access (as well as a link
to
a
free
utility that will check your application for compliance), see what
Allen
Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

Another potential problem is that your IIf statement doesn't
handle
the
case
where either CPR or date is Null.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query that gets the dates of expirations for trainings
that
I
want
to qualify the answer. Since the information is for three
trainings,
I
get
all or none, so I want to have the word "OK" replace the date it
the
training
expiration date is greater than the date of the request. CPR is
one
type
of
training, date is the date the request for expirations go
through.
Can
anyone help me understand why I can not get this IIf statement
to
work?
IIf([CPR]>[date],"OK",[CPR])