|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
April 23rd, 2010, 01:33 PM
posted to microsoft.public.access
|
|||
|
|||
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 
|
|
#2
April 23rd, 2010, 02:32 PM
posted to microsoft.public.access
|
|||
|
|||
|
Create a status designation from 5 date fields using "Not IsNull"
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!
|
|
#3
April 23rd, 2010, 03:35 PM
posted to microsoft.public.access
|
|||
|
|||
|
Create a status designation from 5 date fields using "Not IsNu
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! .
|
|
#4
April 23rd, 2010, 05:51 PM
posted to microsoft.public.access
|
|||
|
|||
|
Create a status designation from 5 date fields using "Not IsNu
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?
|
|
#5
April 23rd, 2010, 06:00 PM
posted to microsoft.public.access
|
|||
|
|||
|
Create a status designation from 5 date fields using "Not IsNu
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! .
|
|
#6
April 23rd, 2010, 06:39 PM
posted to microsoft.public.access
|
|||
|
|||
|
Create a status designation from 5 date fields using "Not IsNu
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? .
|
|
#7
April 23rd, 2010, 07:52 PM
posted to microsoft.public.access
|
|||
|
|||
|
Create a status designation from 5 date fields using "Not IsNu
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! . .
|
|
#8
April 23rd, 2010, 07:55 PM
posted to microsoft.public.access
|
|||
|
|||
|
Create a status designation from 5 date fields using "Not IsNu
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! . .
|
|
#9
April 23rd, 2010, 08:12 PM
posted to microsoft.public.access
|
|||
|
|||
|
Create a status designation from 5 date fields using "Not IsNu
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?
|
|
#10
April 23rd, 2010, 10:32 PM
posted to microsoft.public.access
|
|||
|
|||
|
Create a status designation from 5 date fields using "Not IsNu
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? .
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
