Choosing one of two fields and putting new amount into 3rd field!!

[BCre8iv]

I am new at the d/b stuff so bear with me. I want to be able to convert money from Cdn to US and vice versa. The user will input the conversion rate in a table. Now what has to happen, if possible is the following. My user purchases product from Cdn and US suppliers. The supplier table is where they indicate weather or not the supplier is US or Cdn with an option group. From there the user will input the product into a products table. The tables are related with the SupplierID. In the products table I have the following: "MSRP(USD)" and "MSRP(CDD)". The user will input the amount in the appropriate field. From there I want the amount to go to a field, "MSRP", but I want the amount to be Cdn always. The "MSRP" field is what is used for all other calculations. So what is needed to happen is if the user puts the amount in the "MSPR(USD)" field, and indicated that the supplier of that product is US, I need it to be converted to Cdn and put into the "MSRP" field. And conversly if they put the amount into the "MSRP(CDD)" I just need it to be drawn into the "MSRP" field as is.
Any help in this what give great clarification. Thx
Tina

 

[THoey]

I think you will need to use the IFF statement. Set the MSRP field equal to:

= IFF([MSRP(USD]) IS NULL, [MSRP(CDO)], ([MSRP(USD)] * ConversionRate))

That should give you an idea of how to do it.

BTW, I am going to suggest that you don't use special characters like the &quot" in yuor field names. Somewhere along the line, you are either going to confuse MS Access or yourself... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[BCre8iv]

Thx for the tips
Your suggestion works to a degree
If I use:
=IIf(IsNull([MSRP(USD)]),[MSRP(CDD)],([MSRP(USD)]*[ConvRate]))
I Get an error #Name?
When i just use:
=IIf(IsNull([MSRP(USD)]),[MSRP(CDD)],([MSRP(USD)])
i have no problems except that my US amount isn't converted into Cdn which is what is wanted. Any Ideas?
Tina