adspmo
asked on
Formula does not seem to work(A new one)
Hi Guys
I would like to accomplish the following:
WhenNew :=@If(CountryAH ="";0 ;CountryAH = "Canada":"United States";5;PersonalDaysAH);
Overide := PersonalDaysAH_3;
New := @If(Overide =0 ;WhenNew;Overide);
@If(PersonalDaysAH = "";0;New)
The above does not seem to work
Im importing data from Access into this as well as using it to add new employees, hence the lookup
There are several fields that use the same logic
Any suggestions welcome
James
I would like to accomplish the following:
WhenNew :=@If(CountryAH ="";0 ;CountryAH = "Canada":"United States";5;PersonalDaysAH);
Overide := PersonalDaysAH_3;
New := @If(Overide =0 ;WhenNew;Overide);
@If(PersonalDaysAH = "";0;New)
The above does not seem to work
Im importing data from Access into this as well as using it to add new employees, hence the lookup
There are several fields that use the same logic
Any suggestions welcome
James
My guess would be a type mismatch somewhere:
WhenNew :=@If(CountryAH ="";0 ;CountryAH = "Canada":"United States";5;PersonalDaysAH);
Overide := PersonalDaysAH_3;
New := @If(Overide =0 ;WhenNew;Overide);
@If(PersonalDaysAH = "";0;New)
WhenNew receives either (numeric) 0, 5 or the value of PersonalDaysAH. Not sure if the last one is text ("") or numeric, but I assume numeric.
The 4th line evaluates PersonalDaysAH again against "" (=text).
If PersonalDaysAH was text, then WhenNew may become text. So may New and the final @If, which returns either this text or a numeric 0 to...(a field?)....
WhenNew :=@If(CountryAH ="";0 ;CountryAH = "Canada":"United States";5;PersonalDaysAH);
Overide := PersonalDaysAH_3;
New := @If(Overide =0 ;WhenNew;Overide);
@If(PersonalDaysAH = "";0;New)
WhenNew receives either (numeric) 0, 5 or the value of PersonalDaysAH. Not sure if the last one is text ("") or numeric, but I assume numeric.
The 4th line evaluates PersonalDaysAH again against "" (=text).
If PersonalDaysAH was text, then WhenNew may become text. So may New and the final @If, which returns either this text or a numeric 0 to...(a field?)....
Well Crak.. I think you should be named Hunch(back) of Netherlands (or is it Denmark)...
James hasn't clearly stated what the problem was ???
James hasn't clearly stated what the problem was ???
ASKER
Crak has nailed it
What is going on is if the field has no value(from import) I want it to return a 0 else I want it to run New
When I refresh the document or import the data the field PersonalDaysAH remains blank
All the fields are numeric
How elese would you state if the field is blank other than "="""??
James
What is going on is if the field has no value(from import) I want it to return a 0 else I want it to run New
When I refresh the document or import the data the field PersonalDaysAH remains blank
All the fields are numeric
How elese would you state if the field is blank other than "="""??
James
WhenNew :=@If(CountryAH ="";0 ;CountryAH = "Canada":"United States";5;PersonalDaysAH);
Overide := PersonalDaysAH_3;
New := @If(Overide = "" ;WhenNew;Overide); -- THis has to check if the override is blank
@If(PersonalDaysAH = "";0;New)
Overide := PersonalDaysAH_3;
New := @If(Overide = "" ;WhenNew;Overide); -- THis has to check if the override is blank
@If(PersonalDaysAH = "";0;New)
ASKER
I do not see the difference as far as Overide goes
It has a default value of 0 Why would giving it a default value of "" make the formula run
Or why would it not run if Overide has a default value of 0
James
It has a default value of 0 Why would giving it a default value of "" make the formula run
Or why would it not run if Overide has a default value of 0
James
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is the field that formula runs in
PersonalDaysAH it is anumber field
It cannot have a value of nothing
I do not get x:=@TextToNumber("0" + @Text(PersonalDaysAH)); Why the @TExt it is a number field
PersonalDaysAH it is anumber field
It cannot have a value of nothing
I do not get x:=@TextToNumber("0" + @Text(PersonalDaysAH)); Why the @TExt it is a number field
When you concatenate text values you have to convert it to text and then concatenate.
Well Crak.. I was just pulling your leg :-)
Well Crak.. I was just pulling your leg :-)
ASKER
There is no concatenation within the field
It is either a new document created by amanager:hence the lookups
WhenNew :=@If(CountryAH ="";0 ;CountryAH = "Canada":"United States";5;PersonalDaysAH);
Or amanager may change the vale if it is not a new doc:
Overide := PersonalDaysAH_3;
New := @If(Overide =0 ;WhenNew;Overide);
Or here is the problem the data may be pulled in from an access database, if the value is empty I want a 0 instead of a blank
James
It is either a new document created by amanager:hence the lookups
WhenNew :=@If(CountryAH ="";0 ;CountryAH = "Canada":"United States";5;PersonalDaysAH);
Or amanager may change the vale if it is not a new doc:
Overide := PersonalDaysAH_3;
New := @If(Overide =0 ;WhenNew;Overide);
Or here is the problem the data may be pulled in from an access database, if the value is empty I want a 0 instead of a blank
James
The concatenation is where I prefix the "0".
I try to force a "" field to numeric by prefixing (concatenating) a "0" to it.
Since 0 + "" cause an error and @TextToNumber("") causes an error, a force it to text first.
@Text("") remains ""
@Text(0) returns "0"
Then I prefix the "0" (returning "0" resp. "00") and convert the result back to number (0 either way).
Works great untill you introduce negative numbers!
I try to force a "" field to numeric by prefixing (concatenating) a "0" to it.
Since 0 + "" cause an error and @TextToNumber("") causes an error, a force it to text first.
@Text("") remains ""
@Text(0) returns "0"
Then I prefix the "0" (returning "0" resp. "00") and convert the result back to number (0 either way).
Works great untill you introduce negative numbers!
It woudl help if you explained what each field is used for, and gave the business logic instead of teh formula that you created that you THOUGHT would represent that busines logic... but apparently does not.
PersonalDaysAH_3 is a field that when BLANK should be used in place of 0/5/PersonalDaysAH (depending on CountryAH value). Is this correct?
You are using ZERO instead of BLANK, which means that there is no way to put in an override value of zero instead of, say, teh default of 5.
One of your problems is that when PersonalDaysAH is blank, you always use zero as the result, and ignore the override. Note the number fields CAN have a blank value in NOtes. In Access, they can have a NULL value or be numeric.
I would THINK the following is what you are really trying to do, thought without a business logic explanation, I'm whistling in the dark...
@If(
@IsNumber(Overide);
Overide;
CountryAH = "";
0;
CountryAH = "Canada":"United States";
5;
PersonalDaysAH="";
0;
PersonalDaysAH
)
If you really want to use 0 in overide to indicate "no override," and you are CERTAIN it will always contain a number, then use:
@If(
Overide<>0;
Overide;
CountryAH = "";
0;
CountryAH = "Canada":"United States";
5;
PersonalDaysAH="";
0;
PersonalDaysAH
)
PersonalDaysAH_3 is a field that when BLANK should be used in place of 0/5/PersonalDaysAH (depending on CountryAH value). Is this correct?
You are using ZERO instead of BLANK, which means that there is no way to put in an override value of zero instead of, say, teh default of 5.
One of your problems is that when PersonalDaysAH is blank, you always use zero as the result, and ignore the override. Note the number fields CAN have a blank value in NOtes. In Access, they can have a NULL value or be numeric.
I would THINK the following is what you are really trying to do, thought without a business logic explanation, I'm whistling in the dark...
@If(
@IsNumber(Overide);
Overide;
CountryAH = "";
0;
CountryAH = "Canada":"United States";
5;
PersonalDaysAH="";
0;
PersonalDaysAH
)
If you really want to use 0 in overide to indicate "no override," and you are CERTAIN it will always contain a number, then use:
@If(
Overide<>0;
Overide;
CountryAH = "";
0;
CountryAH = "Canada":"United States";
5;
PersonalDaysAH="";
0;
PersonalDaysAH
)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
~Hemanth