Auto Fill based on wheter there is a value in 3 other fields.

[Njliven]

Here is what I am facing. Please let me know if this is possible.
Table A has 3 fields that are dollar amounts and only 1 of these 3 fields will have an entry.
Then I have Table B that has one field that need the dollar amout in it from one of the 3 fields in table A.
I have all these fields set up on a form, with a query connecting the two tables.


So is it possible to auto fill the field in table B with one of the 3 fields from table A based on whether or not there is a value in one of the other 3 fields.
I hope this makes sense. Thanks!

[June7]

It is possible.

=Nz(field1, Nz(field2, field3))

That expression will display value from one field, if any have a value.

Saving that calculated result to table will require VBA code in some event. The trick is figuring out which event, such as a button Click or form Close.

Me!field4 = Nz(field1, Nz(field2, field3))

[Njliven]

I get this message that shows up in my field instead of the amount #Type!. Not sure what this means. I made the field a currency field to match the others.

Before I got your answer I was playing around with it and I got it partly working with the actual field in the form and using if statements to fill the field on Got Focus. However you take an amout out of one of the 3 fields it does not update with a null value. I tried using another if statement for this, but have not been able to get it to work so far.

If I could get your suggestion to work then I could have it up the table with a On Lost Focus of the field being filled in?

[June7]

What do you mean by 'take an amount out of one' - just deleting value? Code should still work.

If you want to provide db for analysis, follow instructions at bottom of my post.