calculated field

[melda11]

I have this query which selects two fields and calculates it in another field.
But the calculated field has more than 1 expression.Eg
If field A < Field B,Field B + Field C AS X,If field A < Field C,Field A + Field C AS Y.The problem is that when I show this on the Form I want only 1 of the fields to be shown ie either X or Y .if any of the expressions is satisfied then only that field should be shown as a textbox control on the form and not both.How do I do this?? Its really complicated for me..Pls help..

 

[melda11]

I should have mentioned that because the value of my calculated field changes as it satisfies aan expression I create a new field.But in my form I want to show only one control for all these field that are affected.If the expression satifies field X then Field X should be shown in this partcular control in the form and if the expression satisfies field Y then field Y should be shown in this same control in the form.One control should show either of the caculated fields when it satisfies the expression..I hope u guys understand what Im tryin to say here..Thx.

 

[pbaldy]

=IIf(Your condition here, X, Y)

 

[melda11]

u mean put both conditions in 1 bracket??
Like
IIf(A < Field B,field A < Field C,Field B + Field C ,Field A + Field C) AS Z

Is this what u mean?
Thanks..

 

[melda11]

can someone pls help me with this..thx..

 

[pbaldy]

You've done all that in the query, and you already have 2 fields, X & Y, correct? You want 1 control to display one or the other, correct? Then what I posted would work as the control source of a textbox, but I don't know under what conditions you want to display X vs Y. Whatever that is would go in the test portion of the function.

 

[melda11]

Gee thx I get it now..

 

[melda11]

1 more question.But will this work if there are 3 or 4 calculated fields???

 

[pbaldy]

It could, though it would get messy. Why don't you tell us what you're really trying to accomplish, so we can come up with the correct solution?

 

[melda11]

Ok I have these fields in my tableEmployeeLeave .
FROM - starting date
TO - ending date
To Be Taken- DateDiff("d",FROM,TO).(This is a calculated field.)
Forward -no.of days left from previous year.(Not a calculated field.)
No.of Days Due- no.of days left for current year.(Not a calculated field.)
Balance -Total no. of days left.(Not a calculated field.)

The scenario is such that when an employee wants to take his leave then the table should check if he has some left from the previous year first(Forward) before he can look at the current year(No.of Days Due) and the update the balance.

If Forward > To be Taken Then
Forward = Forward - To be Taken
Balance = No of Days Due + Forward..


But if Forward = 0 Then
No.of Days Due = No.of Days Due - To Be Taken
Balance = No.of Days Due - To Be Taken

But If Forward < ToBe Taken AND > 0 Then
variable a = To Be Taken - Forward
Forward = 0
No of Days Due = No.of Days Due - a
Balance = No.of Days Due - a

I found that I can only do this in a query.But Ina query I will be creating new fields everyitime one of the controls needs to change its value and I need to show only One field/control in the form.When I fill in the leave form and I fill the FROM and To Controls.The rest of the controls should be updated automatically.Is this possible??Thanks...

 

[melda11]

can anyone answer my question please.Thx.

 

[boblarson]

Expecting an answer on an ALL VOLUNTEER forum after waiting only 29 minutes? Patience is a virtue which is good to cultivate. There may not be anyone available at this time who either knows the answer or wants to try to answer. But, if you are patient, the liklihood is that you will get an answer.

But, posting as you did which sounds like you are impatient doesn't make many around here happy to go help.

Got it? I hope so.

 

[melda11]

oops sorry!! my bad... I just got in the feel of trying to solve this problem..I got excited and got carried away..sorry again..

 

[The_Doc_Man]

Several possibilities come to mind.

First, you can nest IIF( condition, true-case, false-case ) as...


IIF( condition, true-case, IIF( another-condition, true-case, IIF( ..... ) ) )

Second, this might not be right for you, but look up the SWITCH operator.

Both IIF and SWITCH are adequately described in Access Help.

 

[melda11]

Thanks Il try that out...

 

[melda11]

Is this Iff staement in the query or control source???

 

[ajetrumpet]

Melda, if you haven't already gone crazy with this, here is a Visual Basic translation of the data you gave earlier (this will work fine for you if you have controls on your form that represent the data in the underlying source table)....

From this...

If Forward > To be Taken Then
Forward = Forward - To be Taken
Balance = No of Days Due + Forward..


But if Forward = 0 Then
No.of Days Due = No.of Days Due - To Be Taken
Balance = No.of Days Due - To Be Taken

But If Forward < ToBe Taken AND > 0 Then
variable a = To Be Taken - Forward
Forward = 0
No of Days Due = No.of Days Due - a
Balance = No.of Days Due - a

you should be able to set the values of the different controls on the form like this (maybe an "OnCurrent" Event or something)...

With Me

IF .Forward > .ToBeTaken THEN
  .Forward = .Forward - .ToBeTaken

Else:
  IF .Forward = 0 THEN
    .NoOfDaysDue = .NoOfDaysDue - .ToBeTaken
    .Balance = .NoOfDaysDue - .ToBeTaken

  Else:
    IF .Forward < .ToBeTaken AND .Forward > 0 THEN
      .Forward = 0
      .NoOfDaysDue = .NoOfDaysDue - .ToBeTaken - .Forward
      .Balance = .NoOfDaysDue - .ToBeTaken - .Forward

    End If

  End If

End If

End With

I found that I can only do this in a query.

This is not true, and as a matter of fact, in this case it shouldn't be, because that's too much work. Let the program do it.

Other ways to do something like this (although not as efficient) would include using IIF's, Switch's (like DOC said) and "SELECT Case" statements.

 

[melda11]

That was the first thing I did before I used queries.But when I did that in VBA code there was an error..something like you "cannot assign this to the object"..Is this because we cant update a control because there is already a value assigned to it??This is what I thought was the problem.

 

[melda11]

I tried it but the controls are blank nothing is being displayed.I put the statement in the OnCurrent event like you said.

 

[ajetrumpet]

I read through the entire thread here Melda, and as of now, I cannot say for sure what is going on unless I see the database, or at least the portion that is in question.

That was the first thing I did before I used queries.But when I did that in VBA code there was an error..something like you "cannot assign this to the object"..Is this because we cant update a control because there is already a value assigned to it??

No, that is not the problem....a control's value can be changed at any time...unless of course you have bound it to a function or something...