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

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."

"Fairytale" wrote in message
...
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


"Fairytale" wrote:

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


"Al Campagna" wrote:

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."

"Fairytale" wrote in message
...
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


"John Spencer" wrote:

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!



.



.