If/Then Statements/Conditional Formatting 3

[sagehrer]

I am all new to this and was wondering if someone could guide me.

I have a large database and the two fields that I would like to use as an example is as follows: Date Field and Amount Field

This is what I need to do. If the date is <=12/31/00 then the amount in the amount field should be $90 and if the amount in the date field is >/=01/01/01 then the amount should be $120. What should the expression be and does it get placed in the amount or date field criteria

I tried several strategies and I am getting no where. Any knowlege that you can share would be greatly appreciated.

Thanks alot

 

[jane30]

try to use iif(expression, truestatement, falsestatement) if you have this scenario: if a is not true, then b. In your datefield, are there only two kinds of date: either >=01/01/01 or <=12/31/00? If the answer is yes, you can use the above-mentioned iif function.

 

[jflachman]

How you implement this practically will really depend a little on where you want to specify the value. If you want to calculate the data everytime you view it (not recommended for obvious reasons) then you can do this in your query and not have a permanent field for the value.

Otherwise, if you want this calculated once when the date is entered in your form, you can modify the AfterUpdate event on the date field to calculate the value for the Value field. Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[tlbroadbent]

If you want to update teh table to set the amounts based on the date, try the following. This query only updates records that don't already contain the correct value.

UPDATE MyTable
SET Amount=IIf(DateField<=#12/31/00#,90,120)
WHERE Amount<>IIf(DateField<=#12/31/00#,90,120) Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[sagehrer]

Thank you so much for your help. I appreciate it. Your knowlege sent me in the right direction.

Sheryl