?
Solved

Formula does not seem to work(A new one)

Posted on 2003-12-04
14
Medium Priority
?
259 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:adspmo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9874936
"Does not seem to work".. Can you please make it little elaborate what the results that you are expecting and what the formula is getting you. And any errors. Plus all the dependent field formula values

~Hemanth
0
 
LVL 13

Expert Comment

by:CRAK
ID: 9875019
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?)....
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9875421
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 ???
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:adspmo
ID: 9875594
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
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9875737
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)
0
 

Author Comment

by:adspmo
ID: 9875770
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
0
 
LVL 13

Assisted Solution

by:CRAK
CRAK earned 500 total points
ID: 9876992
Was that some sort of a compliment Hemanth? I may never make it as Mr Universe, but a hunckback?
It's the Netherlands allright!
And I really thought of this as a challenge... You and Qualetee allways get to the questions first. For two months I haven't been able to earn my monthly 3k. Nearly there now.... so I just had to try and find a clue!

adspmo, try this:

x:=@TextToNumber("0" + @Text(PersonalDaysAH));
WhenNew :=@If(CountryAH ="";0 ;CountryAH = "Canada":"United States";5;x);
Overide := PersonalDaysAH_3;
New := @If(Overide =0 ;WhenNew;Overide);
@If(x = 0; 0; New)

Of course, you could also see if there's a way to pass the computation of x straight into PersonalDaysAH. If several fields depend on it....
Or provide it with a default value of 0 and start off with a refresh...?!
0
 

Author Comment

by:adspmo
ID: 9877051
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
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9877471
When you concatenate text values you have to convert it to text and then concatenate.

Well Crak.. I was just pulling your leg :-)

0
 

Author Comment

by:adspmo
ID: 9877753
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
0
 
LVL 13

Expert Comment

by:CRAK
ID: 9877982
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!
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9879142
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
)

0
 
LVL 24

Assisted Solution

by:HemanthaKumar
HemanthaKumar earned 500 total points
ID: 9882528
James, Might be that the values that are imported from Access are not of number type.. it could be "0" or "" So check the field data type properties of the document.. And correct the formula accordingly
0
 
LVL 31

Accepted Solution

by:
qwaletee earned 1000 total points
ID: 9882925
Good point, Hemantha.  If you are worried about them coming across as "text numbers" instead of numerics, try:

overide := @TextToNumber(@Text(overide));
personalDaysAH := @TextToNumber(@Text(personalDaysAH));

@If(
  @IsNumber(Overide);
    Overide;
  CountryAH = "";
    0;
  CountryAH = "Canada":"United States";
    5;
  PersonalDaysAH="";
    0;
 PersonalDaysAH
)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month9 days, 9 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question