|
| 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
January 21st, 2010, 10:14 PM
posted to microsoft.public.access.queries
|
|||
|
|||
"IIf" formula, how to return a value of zero
Can anyone help me with this? I need to know where to put the coding to tell
the formula that if there is no value in any of the txt's, to return a value of zero. =IIf([costcode]="013210" And [costtype]="05320", [txtEstimatedCost]/[txtCraftLabEst], IIf([costcode]="020110" and [costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst], IIf([costcode]="064201" and [costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst],Null))) Thanks so much...Laurie 
|
|
#2
January 21st, 2010, 11:37 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
"IIf" formula, how to return a value of zero
Hi Laurie,
You could add another IIf() function: IIf(IsNull([costcode] Or IsNull(costtype), 0, [your existing IIf() functions here] ) Just a note that you can use the Switch() function to eliminate the need to have all those nested IIf() functions. =Switch(IsNull([costcode] Or IsNull(costtype), 0, [costcode]="013210" And [costtype]="05320", [txtEstimatedCost]/[txtCraftLabEst], [costcode]="020110" and [costtype] ="05320", [txtEstimatedCost]/[txtFirewatchLabEst], [costcode]="064201" and [costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst], True, Null) Look in your online help for details about the Switch() function. Clifford Bass Alberta Rose wrote: Can anyone help me with this? I need to know where to put the coding to tell the formula that if there is no value in any of the txt's, to return a value of zero. =IIf([costcode]="013210" And [costtype]="05320", [txtEstimatedCost]/[txtCraftLabEst], IIf([costcode]="020110" and [costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst], IIf([costcode]="064201" and [costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst],Null))) Thanks so much...Laurie -- Message posted via http://www.accessmonster.com
|
|
#3
January 22nd, 2010, 03:47 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
"IIf" formula, how to return a value of zero
Good day. I get a syntax error message when i try to use the =Switch code.
I put a [] around the first costtype word instead of (), but doesn't seem to be happy yet. Laurie "Clifford Bass via AccessMonster.com" wrote: Hi Laurie, You could add another IIf() function: IIf(IsNull([costcode] Or IsNull(costtype), 0, [your existing IIf() functions here] ) Just a note that you can use the Switch() function to eliminate the need to have all those nested IIf() functions. =Switch(IsNull([costcode] Or IsNull(costtype), 0, [costcode]="013210" And [costtype]="05320", [txtEstimatedCost]/[txtCraftLabEst], [costcode]="020110" and [costtype] ="05320", [txtEstimatedCost]/[txtFirewatchLabEst], [costcode]="064201" and [costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst], True, Null) Look in your online help for details about the Switch() function. Clifford Bass Alberta Rose wrote: Can anyone help me with this? I need to know where to put the coding to tell the formula that if there is no value in any of the txt's, to return a value of zero. =IIf([costcode]="013210" And [costtype]="05320", [txtEstimatedCost]/[txtCraftLabEst], IIf([costcode]="020110" and [costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst], IIf([costcode]="064201" and [costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst],Null))) Thanks so much...Laurie -- Message posted via http://www.accessmonster.com .
|
|
#4
January 22nd, 2010, 04:06 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
"IIf" formula, how to return a value of zero
Switch(IsNull([costcode]) Or IsNull(costtype), 0
, [costcode]="013210" And [costtype]="05320", [txtEstimatedCost]/[txtCraftLabEst] , [costcode]="020110" and [costtype] ="05320", [txtEstimatedCost]/[txtFirewatchLabEst] , [costcode]="064201" and [costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst] , True, Null) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Alberta Rose wrote: Good day. I get a syntax error message when i try to use the =Switch code. I put a [] around the first costtype word instead of (), but doesn't seem to be happy yet. Laurie "Clifford Bass via AccessMonster.com" wrote: Hi Laurie, You could add another IIf() function: IIf(IsNull([costcode] Or IsNull(costtype), 0, [your existing IIf() functions here] ) Just a note that you can use the Switch() function to eliminate the need to have all those nested IIf() functions. =Switch(IsNull([costcode] Or IsNull(costtype), 0, [costcode]="013210" And [costtype]="05320", [txtEstimatedCost]/[txtCraftLabEst], [costcode]="020110" and [costtype] ="05320", [txtEstimatedCost]/[txtFirewatchLabEst], [costcode]="064201" and [costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst], True, Null) Look in your online help for details about the Switch() function. Clifford Bass Alberta Rose wrote: Can anyone help me with this? I need to know where to put the coding to tell the formula that if there is no value in any of the txt's, to return a value of zero. =IIf([costcode]="013210" And [costtype]="05320", [txtEstimatedCost]/[txtCraftLabEst], IIf([costcode]="020110" and [costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst], IIf([costcode]="064201" and [costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst],Null))) Thanks so much...Laurie -- Message posted via http://www.accessmonster.com .
|
|
#5
January 22nd, 2010, 04:13 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
"IIf" formula, how to return a value of zero
OK I got this to work by putting () around [costcode] as well as [costtype].
Could I use the IsNull if the txtEstimatedCost is zero as well? Right now if the txtEstimatedCost is zero, I get an error message #Num!, same if the txtActualCost is zero. "Clifford Bass via AccessMonster.com" wrote: Hi Laurie, You could add another IIf() function: IIf(IsNull([costcode] Or IsNull(costtype), 0, [your existing IIf() functions here] ) Just a note that you can use the Switch() function to eliminate the need to have all those nested IIf() functions. =Switch(IsNull([costcode] Or IsNull(costtype), 0, [costcode]="013210" And [costtype]="05320", [txtEstimatedCost]/[txtCraftLabEst], [costcode]="020110" and [costtype] ="05320", [txtEstimatedCost]/[txtFirewatchLabEst], [costcode]="064201" and [costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst], True, Null) Look in your online help for details about the Switch() function. Clifford Bass Alberta Rose wrote: Can anyone help me with this? I need to know where to put the coding to tell the formula that if there is no value in any of the txt's, to return a value of zero. =IIf([costcode]="013210" And [costtype]="05320", [txtEstimatedCost]/[txtCraftLabEst], IIf([costcode]="020110" and [costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst], IIf([costcode]="064201" and [costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst],Null))) Thanks so much...Laurie -- Message posted via http://www.accessmonster.com .
|
|
#6
January 22nd, 2010, 04:56 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
"IIf" formula, how to return a value of zero
Zero is a value you will get errors if txtCraftLabEst or txtFirewatchLabEst or
txtCraftSuprLabEst is equal to zero and you try to calculate a value based on those fields when the value of the field is zero. If txtEstimatedCost is equal to zero you will automatically get zero returned as the result - unless the above is true for the denominator (=0) is involved. You could use Switch(IsNull([costcode]) Or IsNull(costtype), 0 , [costcode]="013210" And [costtype]="05320" AND txtCraftLabEst0, [txtEstimatedCost]/[txtCraftLabEst] , [costcode]="020110" and [costtype] ="05320" AND txtFirewatchLabEst0, [txtEstimatedCost]/[txtFirewatchLabEst] , [costcode]="064201" and [costtype]="05320" AND txtCraftSuprLabEst0, [txtEstimatedCost]/[txtCraftSuprLabEst] , True, Null) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Alberta Rose wrote: OK I got this to work by putting () around [costcode] as well as [costtype]. Could I use the IsNull if the txtEstimatedCost is zero as well? Right now if the txtEstimatedCost is zero, I get an error message #Num!, same if the txtActualCost is zero. "Clifford Bass via AccessMonster.com" wrote: Hi Laurie, You could add another IIf() function: IIf(IsNull([costcode] Or IsNull(costtype), 0, [your existing IIf() functions here] ) Just a note that you can use the Switch() function to eliminate the need to have all those nested IIf() functions. =Switch(IsNull([costcode] Or IsNull(costtype), 0, [costcode]="013210" And [costtype]="05320", [txtEstimatedCost]/[txtCraftLabEst], [costcode]="020110" and [costtype] ="05320", [txtEstimatedCost]/[txtFirewatchLabEst], [costcode]="064201" and [costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst], True, Null) Look in your online help for details about the Switch() function. Clifford Bass Alberta Rose wrote: Can anyone help me with this? I need to know where to put the coding to tell the formula that if there is no value in any of the txt's, to return a value of zero. =IIf([costcode]="013210" And [costtype]="05320", [txtEstimatedCost]/[txtCraftLabEst], IIf([costcode]="020110" and [costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst], IIf([costcode]="064201" and [costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst],Null))) Thanks so much...Laurie -- Message posted via http://www.accessmonster.com .
|
|
#7
January 22nd, 2010, 05:29 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
"IIf" formula, how to return a value of zero
Hi Laurie,
That was due to a missing closing paranthesis, as you have discovered: =Switch(IsNull([costcode] Or... should have been =Switch(IsNull([costcode]) Or... I see John has given you the way to deal with zero values in the denominators. Good. Clifford Bass Alberta Rose wrote: Good day. I get a syntax error message when i try to use the =Switch code. I put a [] around the first costtype word instead of (), but doesn't seem to be happy yet. Laurie -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201001/1
|
|
#8
January 25th, 2010, 02:47 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
"IIf" formula, how to return a value of zero
Good morning. I get an error message telling me that my expression is too
complex. Any other suggestions? "John Spencer" wrote: Zero is a value you will get errors if txtCraftLabEst or txtFirewatchLabEst or txtCraftSuprLabEst is equal to zero and you try to calculate a value based on those fields when the value of the field is zero. If txtEstimatedCost is equal to zero you will automatically get zero returned as the result - unless the above is true for the denominator (=0) is involved. You could use Switch(IsNull([costcode]) Or IsNull(costtype), 0 , [costcode]="013210" And [costtype]="05320" AND txtCraftLabEst0, [txtEstimatedCost]/[txtCraftLabEst] , [costcode]="020110" and [costtype] ="05320" AND txtFirewatchLabEst0, [txtEstimatedCost]/[txtFirewatchLabEst] , [costcode]="064201" and [costtype]="05320" AND txtCraftSuprLabEst0, [txtEstimatedCost]/[txtCraftSuprLabEst] , True, Null) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Alberta Rose wrote: OK I got this to work by putting () around [costcode] as well as [costtype]. Could I use the IsNull if the txtEstimatedCost is zero as well? Right now if the txtEstimatedCost is zero, I get an error message #Num!, same if the txtActualCost is zero. "Clifford Bass via AccessMonster.com" wrote: Hi Laurie, You could add another IIf() function: IIf(IsNull([costcode] Or IsNull(costtype), 0, [your existing IIf() functions here] ) Just a note that you can use the Switch() function to eliminate the need to have all those nested IIf() functions. =Switch(IsNull([costcode] Or IsNull(costtype), 0, [costcode]="013210" And [costtype]="05320", [txtEstimatedCost]/[txtCraftLabEst], [costcode]="020110" and [costtype] ="05320", [txtEstimatedCost]/[txtFirewatchLabEst], [costcode]="064201" and [costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst], True, Null) Look in your online help for details about the Switch() function. Clifford Bass Alberta Rose wrote: Can anyone help me with this? I need to know where to put the coding to tell the formula that if there is no value in any of the txt's, to return a value of zero. =IIf([costcode]="013210" And [costtype]="05320", [txtEstimatedCost]/[txtCraftLabEst], IIf([costcode]="020110" and [costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst], IIf([costcode]="064201" and [costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst],Null))) Thanks so much...Laurie -- Message posted via http://www.accessmonster.com . .
|
|
#9
January 25th, 2010, 06:41 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
"IIf" formula, how to return a value of zero
No, I don't see any syntax error in that expression. If you remove the
expression completely do you still get the query is too complex? If so, then the expression is not the problem. Switch(IsNull([costcode]) Or IsNull(costtype), 0 , [costcode]="013210" And [costtype]="05320" AND txtCraftLabEst0 , [txtEstimatedCost]/[txtCraftLabEst] , [costcode]="020110" and [costtype] ="05320" AND txtFirewatchLabEst0 , [txtEstimatedCost]/[txtFirewatchLabEst] , [costcode]="064201" and [costtype]="05320" AND txtCraftSuprLabEst0 , [txtEstimatedCost]/[txtCraftSuprLabEst] , True, Null) Perhaps you can post the SQL text of the query that you are trying to run. (Open query in SQL view and copy and paste the text) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Alberta Rose wrote: Good morning. I get an error message telling me that my expression is too complex. Any other suggestions? "John Spencer" wrote: Zero is a value you will get errors if txtCraftLabEst or txtFirewatchLabEst or txtCraftSuprLabEst is equal to zero and you try to calculate a value based on those fields when the value of the field is zero. If txtEstimatedCost is equal to zero you will automatically get zero returned as the result - unless the above is true for the denominator (=0) is involved. You could use Switch(IsNull([costcode]) Or IsNull(costtype), 0 , [costcode]="013210" And [costtype]="05320" AND txtCraftLabEst0, [txtEstimatedCost]/[txtCraftLabEst] , [costcode]="020110" and [costtype] ="05320" AND txtFirewatchLabEst0, [txtEstimatedCost]/[txtFirewatchLabEst] , [costcode]="064201" and [costtype]="05320" AND txtCraftSuprLabEst0, [txtEstimatedCost]/[txtCraftSuprLabEst] , True, Null) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Alberta Rose wrote: OK I got this to work by putting () around [costcode] as well as [costtype]. Could I use the IsNull if the txtEstimatedCost is zero as well? Right now if the txtEstimatedCost is zero, I get an error message #Num!, same if the txtActualCost is zero. "Clifford Bass via AccessMonster.com" wrote: Hi Laurie, You could add another IIf() function: IIf(IsNull([costcode] Or IsNull(costtype), 0, [your existing IIf() functions here] ) Just a note that you can use the Switch() function to eliminate the need to have all those nested IIf() functions. =Switch(IsNull([costcode] Or IsNull(costtype), 0, [costcode]="013210" And [costtype]="05320", [txtEstimatedCost]/[txtCraftLabEst], [costcode]="020110" and [costtype] ="05320", [txtEstimatedCost]/[txtFirewatchLabEst], [costcode]="064201" and [costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst], True, Null) Look in your online help for details about the Switch() function. Clifford Bass Alberta Rose wrote: Can anyone help me with this? I need to know where to put the coding to tell the formula that if there is no value in any of the txt's, to return a value of zero. =IIf([costcode]="013210" And [costtype]="05320", [txtEstimatedCost]/[txtCraftLabEst], IIf([costcode]="020110" and [costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst], IIf([costcode]="064201" and [costtype]="05320", [txtEstimatedCost]/[txtCraftSuprLabEst],Null))) Thanks so much...Laurie -- Message posted via http://www.accessmonster.com . .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
