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

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
0
Utf
4/23/2010 12:33:02 PM
access 16762 articles. 3 followers. Follow

10 Replies
1029 Views

Similar Articles

[PageSpeed] 29

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!
> 
> 
>  
0
John
4/23/2010 1:32:01 PM
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!
> > 
> > 
> >  
> .
> 
0
Utf
4/23/2010 2:35:02 PM
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

Fairytale wrote:
> 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? 
0
John
4/23/2010 4:51:39 PM
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" <Fairytale@discussions.microsoft.com> wrote in message 
news:624A7366-B7C9-4F08-8854-5CC585B6A11D@microsoft.com...
>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!
>> >
>> >
>> >
>> .
>> 


0
Al
4/23/2010 5:00:36 PM
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


"John Spencer" wrote:

> 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
> 
> Fairytale wrote:
> > 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? 
> .
> 
0
Utf
4/23/2010 5:39:01 PM
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" <Fairytale@discussions.microsoft.com> wrote in message 
> news:624A7366-B7C9-4F08-8854-5CC585B6A11D@microsoft.com...
> >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!
> >> >
> >> >
> >> >
> >> .
> >> 
> 
> 
> .
> 
0
Utf
4/23/2010 6:52:01 PM
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" <Fairytale@discussions.microsoft.com> wrote in message 
> > news:624A7366-B7C9-4F08-8854-5CC585B6A11D@microsoft.com...
> > >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!
> > >> >
> > >> >
> > >> >
> > >> .
> > >> 
> > 
> > 
> > .
> > 
0
Utf
4/23/2010 6:55:01 PM
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 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?
0
John
4/23/2010 7:12:25 PM
Thank you!  That did it.  I appreciate your patience with this newbe!!

-- 
Fairytale


"John Spencer" wrote:

> 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 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?
> .
> 
0
Utf
4/23/2010 9:32:01 PM
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" <Fairytale@discussions.microsoft.com> wrote in message 
news:2B190134-9A43-4A27-BCB0-A46A4C94EAF3@microsoft.com...
> 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" <Fairytale@discussions.microsoft.com> wrote in message
>> > news:624A7366-B7C9-4F08-8854-5CC585B6A11D@microsoft.com...
>> > >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!
>> > >> >
>> > >> >
>> > >> >
>> > >> .
>> > >>
>> >
>> >
>> > .
>> > 


0
Al
4/23/2010 11:39:15 PM
Reply:

Similar Artilces:

Create a status designation from 5 date fields using "Not IsNull"
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...