"IF" Statement Help

[Rich Hartnett]

I need to write an IF statement that says:

If Transaction Code = "A," then Date Flagged = Date(), or
if Transaction Code = "D", then Date UnFlagged = Date()

Any help would be appreciated.

[Tim Kiebert]

If Transaction_Code = "A" then 
    Date_Flagged = Date()
Else If Transaction_Code = "D" then 
    Date_UnFlagged = Date()
End If

or


select
    case Transaction_Code = "A"
        Date_Flagged = Date()
    case Transaction_Code = "D"
        Date_UnFlagged = Date()
end select

Edit: Wasn't sure if they were real field names but I changed the spaces to underscores.

[MikeC]

Or is this more of what you had in mind?

if(Transaction_Code = "A" , Date_Flagged = Date(), if(Transaction_Code = "D", Date_UnFlagged = Date()))

Mike

edit---am unsure of one thing though: do you have to designate a false arguement--such as after the last date() in the function above? If so I guess it could be a ui_msg_box, or even just an "end" statement?

[Rich Hartnett]

Yes thanks Mike. The second way you have it, I guess that's an expression format, is what I was looking for.

And yes, those are the real field names. I should have put the underscores on them when I posted the question.

Thanks a bunch.

[Rich Hartnett]

Whoops, Sorry, didn't notice that two different people had posted responses.

Mike after looking at the expression, I'm wondering ......

Do I put the code in the field rule of the Date_Flagged field, or the Date_Unflagged field? I tried it in both, but the expression builder didn't seem to like the expression.

Also, I don't thing I need a "false" argument. I need the Date_Flagged field to show the current date when "A" (for add) is placed in the Transaction field, and that date can stay there, and at a later date, when the Transaction Code is changed to "D" (for delete), the new date needs to be placed in the Date_Unflagged field.

[MikeC]

Rich,

Why not just place one of the expressions Tim first gave in the OnChange event of the transaction field?

Mike

[Mike Wilson]

Rich,
I have to admit that the very details of this thread I haven't captured, BUT you wrote this expression is placed in a field rule code. Read about IIF() because I believe for a single line Filter Expression of a field rule you will need to use IIF(). I'm pretty sure...

[MikeC]

Rich--and another part that may be missing is what I referred to in the prior post as the "second arguement"...not asking if YOU needed it but if the IF() needed it! I am not sure about my syntax and it may be necessary to add to my expression where i've highlighted in red along with what MikeW mentioned about using the IIF:

   if(Transaction_Code = "A" , Date_Flagged = Date(), if(Transaction_Code = "D", Date_UnFlagged = Date(),may need a false statement here))

Hopefully Tim or MikeW can tell us the correct syntax involved.


Mike

[Tim Kiebert]

Or is this more of what you had in mind?

if(Transaction_Code = "A" , Date_Flagged = Date(), if(Transaction_Code = "D", Date_UnFlagged = Date()))

Mike

edit---am unsure of one thing though: do you have to designate a false arguement--such as after the last date() in the function above? If so I guess it could be a ui_msg_box, or even just an "end" statement?

Yes Mike you would need to add a false argument in order to complete the function. However I don't think your code will work. The If() function is designed to return one of two results based on a test which returns true or false.
On the other hand, Richard wants to do one of two different actions based on a test. For this you should you the If then else end if statements.

The if() function chooses between two somethings and
the If statement chooses whether or not to do an action.


Mike and Richard, I was trying to write this post as the two of you were posting so had to keep editing my post.:)


Richard, MikeC is right about puting some code (such as I provided above) in the OnChangeRecord record event or the onwrote field event.

[Rich Hartnett]

Mike C do you mean "OnChangeRecord?"

If so, I tried that, only whenever I try to change the data in the Transaction field, I get an error message.

[MikeC]

Tim,

Just going by the help file I was simply using nested if() functions---noting that I was missing the false arguement of the second if() function. If something could be placed as the false result (could "" be used??) then it would or should work.


Nested if Statements

You can nest IF() statements to create more complex tests. For example, assume that you use codes to save data entry time when entering college students into a table.

The field CLASS contains 1 for "Freshman", 2 for "Sophomore", 3 for "Junior", and 4 for "Senior".

To print a report that contains the year of school a student is in, rather than the code, define and place a calculated field called GRADE, which uses the following expression:

if(class=4, "Senior", if(class=3, "Junior", if(class=2, "Sophomore", "Freshman")))

MIke

[MikeC]

Rich,

Should work---apparently you are intent on using field rules! :-)


Previously I had mentioned the transaction field's OnChange event--from whatever form you are using to change the code to "A" or to "D".

It looks like you are using a variable now---thought the transaction code was just a field! would have to maybe change the syntax a bit to accommodate a variable. Also, have you declared your variable as global? And are sure the variable has been set to the value you want??


Mike

[Tim Kiebert]

Mike C do you mean "OnChangeRecord?"

If so, I tried that, only whenever I try to change the data in the Transaction field, I get an error message.

Richard,

Try fully qualifyng the field name as in the following assuming your table is called MasterList

Case MasterList->Transaction_Code = "A"

[Tim Kiebert]

Tim,

Just going by the help file I was simply using nested if() functions---noting that I was missing the false arguement of the second if() function. If something could be placed as the false result (could "" be used??) then it would or should work.


Nested if Statements
MIke

I have no problem with your use of nested IFs. Which when you boil them down are always returning one of two choices. One of the choices just happens to be the result of another IF. What I reckon is wrong with your code is the value you are trying to return. Namely, an instruction to do an action.

You are trying to return one of two instructions that will assign a date to a field. But the If Function's purpose is not to branch between instructions but to return some type of physical data like a date or a name or amount or as in the above example a Grade.

If you want to return an instruction you could do some thing like this. Run this in the Interactive Window changing the value for 'Transaction_Code'. Take note though that the IF function is still only returning some text to be evaluated by the evaluate_template()function.

dim Date_Flagged as D

dim Date_UnFlagged as D

dim transaction_Code as c = "s"




?if(Transaction_Code = "A" , "Date_Flagged = Date()", if(Transaction_Code = "D", "Date_UnFlagged = Date()","ui_msg_box(transaction_Code)"))


evaluate_template( if(Transaction_Code = "A" , "Date_Flagged = Date()", if(Transaction_Code = "D", "Date_UnFlagged = Date()","ui_msg_box(\"\",transaction_Code)")))


?Date_Flagged


?Date_UnFlagged

[Rich Hartnett]

Rich,

Should work---apparently you are intent on using field rules! :-)


Previously I had mentioned the transaction field's OnChange event--from whatever form you are using to change the code to "A" or to "D".

It looks like you are using a variable now---thought the transaction code was just a field! would have to maybe change the syntax a bit to accommodate a variable. Also, have you declared your variable as global? And are sure the variable has been set to the value you want??


Mike

Sorry Mike, I guess I'm putting this code in the wrong place, because I can't seem to make it work. I thought the code goes in the Events section of the field itself, under Field Rules. If not, you'll have to enlighten me on where to put it. As for the Transaction_Code field itself, it is just a regular field with a drop-down list giving the user three choices: "A," "D" or blank. The screen shot is where I'm and inserting the code.

[G Gabriel]

Rich:
Just read the first post and either something is missing or you are using if() when you shouldn't:

If Transaction Code = "A," then Date Flagged = Date(), or
if Transaction Code = "D", then Date UnFlagged = Date()

Both conditions produce the same result: date() !!

On reading further on what you wish to accomplish:

I need the Date_Flagged field to show the current date when "A" (for add) is placed in the Transaction field, and that date can stay there, and at a later date, when the Transaction Code is changed to "D" (for delete)

You could do that several ways. If you want to use the field rules:
In Data entery, use Conditional default value and in the window in Default put: date() and in the condition put: Date_Flagged="A".or.Date_Flagged="D".

[Tim Kiebert]

Richard,

Try changing your code to

Select 
    Case MasterList->Transaction_Code = "A"
       Masterlist->Entrydate = date()
    Case MasterList->Transaction_Code = "D"
        Masterlist->DateUnFlagged = date()
End Select

If it still doesn't work then try putting the code in the OnWrotefield field event.

If it still doesn't work try putting the following in the CanWriteField field event.

Select 
     Case A_FIELD_VALUE.value = "A"
        Masterlist->Entrydate = date()
     Case A_FIELD_VALUE.value = "D"
         Masterlist->DateUnFlagged = date()
 End Select

If it still doesn't work post us a sample to work with. :)

Good Luck

NOTE: While in field rules if you place code in a record event it doesn't matter which field you are on because the event applies to the whole record. However when placing code in a field event make sure you are on the field that you want to trigger the code.

[MikeC]

Tim,

Thanks---I can see your point for the most part (just have to think about it more to actually get it straight in my head now!).


Rich,
Tim is the goto guy here I think (or G, or MikeW, or...)--sometimes I stick my 2 cents in when I maybe shouldn't!! :) I was simply giving a possible alternative and doesn't look look like it actually was a viable one after all.
Please listen to them as I do when trying to learn things.




Mike

[Rich Hartnett]

Okay Tim, I followed this code:

Select
Case MasterList->Transaction_Code = "A"
Masterlist->Entrydate = date()
Case MasterList->Transaction_Code = "D"
Masterlist->DateUnFlagged = date()
End Select

... and placed it in the CanWriteField and now it works fine.

I appologize for the confusion earlier. I've only had experience with using expessions in Field Rules as basically one line code, and didn't realize there were so many other ways to get the same end results. I've never placed code in the Events tab before, so this has certainly been a learning experience for me.

Once again, thanks to all who made suggestions. This message board is a lifesaver. It allows me to learn something new every day.

[G Gabriel]

Rich:
Have you tried any of the other suggestions?
Did any of them work?

[Rich Hartnett]

No I didn't. Once I found one that worked, I stuck with that. Had to keep the project moving.

One day ........................ if I ever catch up with all my ongoing projects .......... I'll have time to just "play and experiment." LOL