Create a status designation from 5 date fields using "Not IsNull"

[Fairytale]

I am still learning, lots that I don't know, but I am trying to create a
status designation from 5 date fields using "Not IsNull" and having
difficulty. I am not sure if this will fuction best in query, forms... can I
do this on the table???

After some research, this is what I have in a query for now and it seems to
work but I don't want a number:

Status: IIf(Not IsNull([cogradulate]),5,IIf(Not
IsNull([verifydate]),4,IIf(Not IsNull([permCAdate]),3,IIf(Not
IsNull([shorttermCAdate]),2,IIf(Not IsNull([containmentdate]),1,0)))))

When I change it to a word in place of the numeral, like below, it asks me
for parameters. I do not understand "Parameters" or what I am to do. I did
try setting parameters but it is obvious I don't know what I'm doing because
now I get an additional prompt for parameter beyond just the original 5.

Status: =IIf(Not IsNull([cogradulate]),â€Closedâ€,IIf(Not
IsNull([verifydate]),â€Verifiedâ€,IIf(Not
IsNull([permCAdate]),â€PermanentFixâ€,IIf(Not
IsNull([shorttermCAdate]),â€ShortTermFixâ€,IIf(Not
IsNull([containmentdate]),â€Containedâ€,â€Newâ€)))))

I would appreciate any advise you can give!

 

[John Spencer]

The only thing I see that could cause a problem is the quote marks. They
should be " and not â€.

If that is only caused by the newsgroup posting then I am stumped.

Status: =IIf(Not IsNull([cogradulate]),"Closed"
,IIf(Not IsNull([verifydate]),"Verified"
,IIf(Not IsNull([permCAdate]),"PermanentFix"
,IIf(Not IsNull([shorttermCAdate]),"ShortTermFix"
,IIf(Not IsNull([containmentdate]),"Contained","New")))))


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Fairytale]

I changed those to " instead of the word quotation marks but that did not
help. Its still asking me for parameters. That seems to be the key. Is
there some setting with the parameters that I need to adjust? Or am I better
off creating a table for the status using the number and the text and doing a
DLookup (might not have the right formula name there) that returns the text
string I want?
--
Fairytale

The only thing I see that could cause a problem is the quote marks. They
should be " and not â€.

If that is only caused by the newsgroup posting then I am stumped.

Status: =IIf(Not IsNull([cogradulate]),"Closed"
,IIf(Not IsNull([verifydate]),"Verified"
,IIf(Not IsNull([permCAdate]),"PermanentFix"
,IIf(Not IsNull([shorttermCAdate]),"ShortTermFix"
,IIf(Not IsNull([containmentdate]),"Contained","New")))))


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I am still learning, lots that I don't know, but I am trying to create a
status designation from 5 date fields using "Not IsNull" and having
difficulty. I am not sure if this will fuction best in query, forms... can I
do this on the table???

After some research, this is what I have in a query for now and it seems to
work but I don't want a number:

Status: IIf(Not IsNull([cogradulate]),5,IIf(Not
IsNull([verifydate]),4,IIf(Not IsNull([permCAdate]),3,IIf(Not
IsNull([shorttermCAdate]),2,IIf(Not IsNull([containmentdate]),1,0)))))

When I change it to a word in place of the numeral, like below, it asks me
for parameters. I do not understand "Parameters" or what I am to do. I did
try setting parameters but it is obvious I don't know what I'm doing because
now I get an additional prompt for parameter beyond just the original 5.

Status: =IIf(Not IsNull([cogradulate]),â€Closedâ€,IIf(Not
IsNull([verifydate]),â€Verifiedâ€,IIf(Not
IsNull([permCAdate]),â€PermanentFixâ€,IIf(Not
IsNull([shorttermCAdate]),â€ShortTermFixâ€,IIf(Not
IsNull([containmentdate]),â€Containedâ€,â€Newâ€)))))

I would appreciate any advise you can give!

.

 

[John Spencer]

Sorry if this is a QUERY then you need to remove the equal sign at the beginning.

Status: IIf(Not IsNull([cogradulate]),"Closed"
,IIf(Not IsNull([verifydate]),"Verified"
,IIf(Not IsNull([permCAdate]),"PermanentFix"
,IIf(Not IsNull([shorttermCAdate]),"ShortTermFix"
,IIf(Not IsNull([containmentdate]),"Contained","New")))))

I don't quite know how I missed that.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Al Campagna]

Fairytale,
I'm stumped too. I'm beginning to think that those parameter
requests are coming from somewhere else.
This is a query I take it, and you're using the query design grid?
Have you tried removing this calculation from the query, and if so...
do you still get the parameter requests?
Are all 5 of these fields in the table behind the query?
Just for now, make sure all 5 fields are dragged from the table
to the query grid
Do you have anything entered in the Parameters dialog box?

Please respond to each of these questions with as much detail as
possible.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

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

I changed those to " instead of the word quotation marks but that did not
help. Its still asking me for parameters. That seems to be the key. Is
there some setting with the parameters that I need to adjust? Or am I
better
off creating a table for the status using the number and the text and
doing a
DLookup (might not have the right formula name there) that returns the
text
string I want?
--
Fairytale

The only thing I see that could cause a problem is the quote marks. They
should be " and not ".

If that is only caused by the newsgroup posting then I am stumped.

Status: =IIf(Not IsNull([cogradulate]),"Closed"
,IIf(Not IsNull([verifydate]),"Verified"
,IIf(Not IsNull([permCAdate]),"PermanentFix"
,IIf(Not IsNull([shorttermCAdate]),"ShortTermFix"
,IIf(Not IsNull([containmentdate]),"Contained","New")))))


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I am still learning, lots that I don't know, but I am trying to create
a
status designation from 5 date fields using "Not IsNull" and having
difficulty. I am not sure if this will fuction best in query, forms...
can I
do this on the table???

After some research, this is what I have in a query for now and it
seems to
work but I don't want a number:

Status: IIf(Not IsNull([cogradulate]),5,IIf(Not
IsNull([verifydate]),4,IIf(Not IsNull([permCAdate]),3,IIf(Not
IsNull([shorttermCAdate]),2,IIf(Not IsNull([containmentdate]),1,0)))))

When I change it to a word in place of the numeral, like below, it asks
me
for parameters. I do not understand "Parameters" or what I am to do.
I did
try setting parameters but it is obvious I don't know what I'm doing
because
now I get an additional prompt for parameter beyond just the original
5.

Status: =IIf(Not IsNull([cogradulate]),"Closed",IIf(Not
IsNull([verifydate]),"Verified",IIf(Not
IsNull([permCAdate]),"PermanentFix",IIf(Not
IsNull([shorttermCAdate]),"ShortTermFix",IIf(Not
IsNull([containmentdate]),"Contained","New")))))

I would appreciate any advise you can give!

.

 

[Fairytale]

I've tried it in query and in forms, with and without the equal sign, it has
no bearing on the end result. The only way it works is with the number
value. In the form with the text values I do not get parameter questions but
it returns this: #Name? Is there something else that I am missing?

--
Fairytale

Sorry if this is a QUERY then you need to remove the equal sign at the beginning.

Status: IIf(Not IsNull([cogradulate]),"Closed"
,IIf(Not IsNull([verifydate]),"Verified"
,IIf(Not IsNull([permCAdate]),"PermanentFix"
,IIf(Not IsNull([shorttermCAdate]),"ShortTermFix"
,IIf(Not IsNull([containmentdate]),"Contained","New")))))

I don't quite know how I missed that.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I changed those to " instead of the word quotation marks but that did not
help. Its still asking me for parameters. That seems to be the key. Is
there some setting with the parameters that I need to adjust? Or am I better
off creating a table for the status using the number and the text and doing a
DLookup (might not have the right formula name there) that returns the text
string I want?

.

 

[Fairytale]

I tried adding those fields to the query (this is a fresh query, no other
data) along with my key. If I add this expression:

Status: IIf(Not IsNull([cogradulate]),["Closed"],IIf(Not
IsNull([verifydate]),["Verified"],IIf(Not
IsNull([permCAdate]),["PermanentFix"],IIf(Not
IsNull([shorttermCAdate]),["ShortTermFix"],IIf(Not
IsNull([containmentdate]),["Contained"],["New"])))))

It prompts parameters: "Closed" "Verified" "PermanentFix" "ShortermFix"
"Contained" & "New". I do not have any parameters set in the query. I leave
the prompt blank and answer OK and the field is then blank on my query. If
for each prompt I add the word I want returned "Closed" "Verified".... then
it will populate them with that data.

But if I use this expression, it does what I expect and provides the correct
number based upon the expression without any prompts:

Status: IIf(Not IsNull([cogradulate]),5,IIf(Not
IsNull([verifydate]),4,IIf(Not IsNull([permCAdate]),3,IIf(Not
IsNull([shorttermCAdate]),2,IIf(Not IsNull([containmentdate]),1,0)))))

I don't understand why it will not work when I exchange the numbers for
text. I've tried this in forms and in query, it responds exactly the same
way. There must be some way that I can have it recognize text without
promts, right?
--
Fairytale

Fairytale,
I'm stumped too. I'm beginning to think that those parameter
requests are coming from somewhere else.
This is a query I take it, and you're using the query design grid?
Have you tried removing this calculation from the query, and if so...
do you still get the parameter requests?
Are all 5 of these fields in the table behind the query?
Just for now, make sure all 5 fields are dragged from the table
to the query grid
Do you have anything entered in the Parameters dialog box?

Please respond to each of these questions with as much detail as
possible.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

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

I changed those to " instead of the word quotation marks but that did not
help. Its still asking me for parameters. That seems to be the key. Is
there some setting with the parameters that I need to adjust? Or am I
better
off creating a table for the status using the number and the text and
doing a
DLookup (might not have the right formula name there) that returns the
text
string I want?
--
Fairytale

The only thing I see that could cause a problem is the quote marks. They
should be " and not ".

If that is only caused by the newsgroup posting then I am stumped.

Status: =IIf(Not IsNull([cogradulate]),"Closed"
,IIf(Not IsNull([verifydate]),"Verified"
,IIf(Not IsNull([permCAdate]),"PermanentFix"
,IIf(Not IsNull([shorttermCAdate]),"ShortTermFix"
,IIf(Not IsNull([containmentdate]),"Contained","New")))))


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Fairytale wrote:
I am still learning, lots that I don't know, but I am trying to create
a
status designation from 5 date fields using "Not IsNull" and having
difficulty. I am not sure if this will fuction best in query, forms...
can I
do this on the table???

After some research, this is what I have in a query for now and it
seems to
work but I don't want a number:

Status: IIf(Not IsNull([cogradulate]),5,IIf(Not
IsNull([verifydate]),4,IIf(Not IsNull([permCAdate]),3,IIf(Not
IsNull([shorttermCAdate]),2,IIf(Not IsNull([containmentdate]),1,0)))))

When I change it to a word in place of the numeral, like below, it asks
me
for parameters. I do not understand "Parameters" or what I am to do.
I did
try setting parameters but it is obvious I don't know what I'm doing
because
now I get an additional prompt for parameter beyond just the original
5.

Status: =IIf(Not IsNull([cogradulate]),"Closed",IIf(Not
IsNull([verifydate]),"Verified",IIf(Not
IsNull([permCAdate]),"PermanentFix",IIf(Not
IsNull([shorttermCAdate]),"ShortTermFix",IIf(Not
IsNull([containmentdate]),"Contained","New")))))

I would appreciate any advise you can give!



.

.

 

[Fairytale]

Sorry, I did not answer the first two questions you had:

Have you tried removing this calculation from the query, and if so...
do you still get the parameter requests? Yes, I removed it and no I do not
get the parameter requests then so it's definately tied to the expression or
calculation as you call it.

Are all 5 of these fields in the table behind the query? Yes, they all exist
in the table behind the query and form.

--
Fairytale

I tried adding those fields to the query (this is a fresh query, no other
data) along with my key. If I add this expression:

Status: IIf(Not IsNull([cogradulate]),["Closed"],IIf(Not
IsNull([verifydate]),["Verified"],IIf(Not
IsNull([permCAdate]),["PermanentFix"],IIf(Not
IsNull([shorttermCAdate]),["ShortTermFix"],IIf(Not
IsNull([containmentdate]),["Contained"],["New"])))))

It prompts parameters: "Closed" "Verified" "PermanentFix" "ShortermFix"
"Contained" & "New". I do not have any parameters set in the query. I leave
the prompt blank and answer OK and the field is then blank on my query. If
for each prompt I add the word I want returned "Closed" "Verified".... then
it will populate them with that data.

But if I use this expression, it does what I expect and provides the correct
number based upon the expression without any prompts:

Status: IIf(Not IsNull([cogradulate]),5,IIf(Not
IsNull([verifydate]),4,IIf(Not IsNull([permCAdate]),3,IIf(Not
IsNull([shorttermCAdate]),2,IIf(Not IsNull([containmentdate]),1,0)))))

I don't understand why it will not work when I exchange the numbers for
text. I've tried this in forms and in query, it responds exactly the same
way. There must be some way that I can have it recognize text without
promts, right?
--
Fairytale

Fairytale,
I'm stumped too. I'm beginning to think that those parameter
requests are coming from somewhere else.
This is a query I take it, and you're using the query design grid?
Have you tried removing this calculation from the query, and if so...
do you still get the parameter requests?
Are all 5 of these fields in the table behind the query?
Just for now, make sure all 5 fields are dragged from the table
to the query grid
Do you have anything entered in the Parameters dialog box?

Please respond to each of these questions with as much detail as
possible.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

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

I changed those to " instead of the word quotation marks but that did not
help. Its still asking me for parameters. That seems to be the key. Is
there some setting with the parameters that I need to adjust? Or am I
better
off creating a table for the status using the number and the text and
doing a
DLookup (might not have the right formula name there) that returns the
text
string I want?
--
Fairytale


:

The only thing I see that could cause a problem is the quote marks. They
should be " and not ".

If that is only caused by the newsgroup posting then I am stumped.

Status: =IIf(Not IsNull([cogradulate]),"Closed"
,IIf(Not IsNull([verifydate]),"Verified"
,IIf(Not IsNull([permCAdate]),"PermanentFix"
,IIf(Not IsNull([shorttermCAdate]),"ShortTermFix"
,IIf(Not IsNull([containmentdate]),"Contained","New")))))


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Fairytale wrote:
I am still learning, lots that I don't know, but I am trying to create
a
status designation from 5 date fields using "Not IsNull" and having
difficulty. I am not sure if this will fuction best in query, forms...
can I
do this on the table???

After some research, this is what I have in a query for now and it
seems to
work but I don't want a number:

Status: IIf(Not IsNull([cogradulate]),5,IIf(Not
IsNull([verifydate]),4,IIf(Not IsNull([permCAdate]),3,IIf(Not
IsNull([shorttermCAdate]),2,IIf(Not IsNull([containmentdate]),1,0)))))

When I change it to a word in place of the numeral, like below, it asks
me
for parameters. I do not understand "Parameters" or what I am to do.
I did
try setting parameters but it is obvious I don't know what I'm doing
because
now I get an additional prompt for parameter beyond just the original
5.

Status: =IIf(Not IsNull([cogradulate]),"Closed",IIf(Not
IsNull([verifydate]),"Verified",IIf(Not
IsNull([permCAdate]),"PermanentFix",IIf(Not
IsNull([shorttermCAdate]),"ShortTermFix",IIf(Not
IsNull([containmentdate]),"Contained","New")))))

I would appreciate any advise you can give!



.

.

 

[John Spencer]

WHERE or WHERE did those brackets come from? When you put in the brackets
then you are telling the query that these are object names (probably fields).
Since it can't find a field named "Closed" (including the quote marks) then
it thinks this is a parameter. Hence you get a parameter prompt.

NO SQUARE BRACKETS, just quote marks.

Status: IIf(Not IsNull([cogradulate]),"Closed"
,IIf(Not IsNull([verifydate]),"Verified"
,IIf(Not IsNull([permCAdate]),"PermanentFix"
,IIf(Not IsNull([shorttermCAdate]),"ShortTermFix"
,IIf(Not IsNull([containmentdate]),"Contained","New")))))

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Fairytale]

Thank you! That did it. I appreciate your patience with this newbe!!

--
Fairytale

WHERE or WHERE did those brackets come from? When you put in the brackets
then you are telling the query that these are object names (probably fields).
Since it can't find a field named "Closed" (including the quote marks) then
it thinks this is a parameter. Hence you get a parameter prompt.

NO SQUARE BRACKETS, just quote marks.

Status: IIf(Not IsNull([cogradulate]),"Closed"
,IIf(Not IsNull([verifydate]),"Verified"
,IIf(Not IsNull([permCAdate]),"PermanentFix"
,IIf(Not IsNull([shorttermCAdate]),"ShortTermFix"
,IIf(Not IsNull([containmentdate]),"Contained","New")))))

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I tried adding those fields to the query (this is a fresh query, no other
data) along with my key. If I add this expression:

Status: IIf(Not IsNull([cogradulate]),["Closed"],IIf(Not
IsNull([verifydate]),["Verified"],IIf(Not
IsNull([permCAdate]),["PermanentFix"],IIf(Not
IsNull([shorttermCAdate]),["ShortTermFix"],IIf(Not
IsNull([containmentdate]),["Contained"],["New"])))))

It prompts parameters: "Closed" "Verified" "PermanentFix" "ShortermFix"
"Contained" & "New". I do not have any parameters set in the query. I leave
the prompt blank and answer OK and the field is then blank on my query. If
for each prompt I add the word I want returned "Closed" "Verified".... then
it will populate them with that data.

But if I use this expression, it does what I expect and provides the correct
number based upon the expression without any prompts:

Status: IIf(Not IsNull([cogradulate]),5,IIf(Not
IsNull([verifydate]),4,IIf(Not IsNull([permCAdate]),3,IIf(Not
IsNull([shorttermCAdate]),2,IIf(Not IsNull([containmentdate]),1,0)))))

I don't understand why it will not work when I exchange the numbers for
text. I've tried this in forms and in query, it responds exactly the same
way. There must be some way that I can have it recognize text without
promts, right?

.

 

[Al Campagna]

As John asked... where did those brackets come from??

Were they there in the IIF statement all along?

Whenever you have trouble with code... or a calculation... or any
expression... always Cut & Paste "exactly" what you have
into your post.

OK... glad you're all set.
--
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

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

Sorry, I did not answer the first two questions you had:

Have you tried removing this calculation from the query, and if so...
do you still get the parameter requests? Yes, I removed it and no I do not
get the parameter requests then so it's definately tied to the expression
or
calculation as you call it.

Are all 5 of these fields in the table behind the query? Yes, they all
exist
in the table behind the query and form.

--
Fairytale

I tried adding those fields to the query (this is a fresh query, no other
data) along with my key. If I add this expression:

Status: IIf(Not IsNull([cogradulate]),["Closed"],IIf(Not
IsNull([verifydate]),["Verified"],IIf(Not
IsNull([permCAdate]),["PermanentFix"],IIf(Not
IsNull([shorttermCAdate]),["ShortTermFix"],IIf(Not
IsNull([containmentdate]),["Contained"],["New"])))))

It prompts parameters: "Closed" "Verified" "PermanentFix" "ShortermFix"
"Contained" & "New". I do not have any parameters set in the query. I
leave
the prompt blank and answer OK and the field is then blank on my query.
If
for each prompt I add the word I want returned "Closed" "Verified"....
then
it will populate them with that data.

But if I use this expression, it does what I expect and provides the
correct
number based upon the expression without any prompts:

Status: IIf(Not IsNull([cogradulate]),5,IIf(Not
IsNull([verifydate]),4,IIf(Not IsNull([permCAdate]),3,IIf(Not
IsNull([shorttermCAdate]),2,IIf(Not IsNull([containmentdate]),1,0)))))

I don't understand why it will not work when I exchange the numbers for
text. I've tried this in forms and in query, it responds exactly the
same
way. There must be some way that I can have it recognize text without
promts, right?
--
Fairytale

Fairytale,
I'm stumped too. I'm beginning to think that those parameter
requests are coming from somewhere else.
This is a query I take it, and you're using the query design grid?
Have you tried removing this calculation from the query, and if
so...
do you still get the parameter requests?
Are all 5 of these fields in the table behind the query?
Just for now, make sure all 5 fields are dragged from the table
to the query grid
Do you have anything entered in the Parameters dialog box?

Please respond to each of these questions with as much detail as
possible.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

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

I changed those to " instead of the word quotation marks but that did
not
help. Its still asking me for parameters. That seems to be the key.
Is
there some setting with the parameters that I need to adjust? Or am
I
better
off creating a table for the status using the number and the text and
doing a
DLookup (might not have the right formula name there) that returns
the
text
string I want?
--
Fairytale


:

The only thing I see that could cause a problem is the quote marks.
They
should be " and not ".

If that is only caused by the newsgroup posting then I am stumped.

Status: =IIf(Not IsNull([cogradulate]),"Closed"
,IIf(Not IsNull([verifydate]),"Verified"
,IIf(Not IsNull([permCAdate]),"PermanentFix"
,IIf(Not IsNull([shorttermCAdate]),"ShortTermFix"
,IIf(Not IsNull([containmentdate]),"Contained","New")))))


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Fairytale wrote:
I am still learning, lots that I don't know, but I am trying to
create
a
status designation from 5 date fields using "Not IsNull" and
having
difficulty. I am not sure if this will fuction best in query,
forms...
can I
do this on the table???

After some research, this is what I have in a query for now and it
seems to
work but I don't want a number:

Status: IIf(Not IsNull([cogradulate]),5,IIf(Not
IsNull([verifydate]),4,IIf(Not IsNull([permCAdate]),3,IIf(Not
IsNull([shorttermCAdate]),2,IIf(Not
IsNull([containmentdate]),1,0)))))

When I change it to a word in place of the numeral, like below, it
asks
me
for parameters. I do not understand "Parameters" or what I am to
do.
I did
try setting parameters but it is obvious I don't know what I'm
doing
because
now I get an additional prompt for parameter beyond just the
original
5.

Status: =IIf(Not IsNull([cogradulate]),"Closed",IIf(Not
IsNull([verifydate]),"Verified",IIf(Not
IsNull([permCAdate]),"PermanentFix",IIf(Not
IsNull([shorttermCAdate]),"ShortTermFix",IIf(Not
IsNull([containmentdate]),"Contained","New")))))

I would appreciate any advise you can give!



.



.