Simple expression to subtract two (currency) fields won't work?

[Sharky II]

Hi guys

I have 2 currency fields, one for cost of trip, one for amount that the customer has paid so far. I have created another unbound field on the form which i want to subtract the amount paid from the cost.

I have the following expression for default value:

=Nz([Cost])-Nz([AmountPaidToDate])

Seems straight forward enough. But always gives me £0.00. If i change everything to straight forward numbers, i still get 0. If i take off the NULL converter, i just get an empty box.

EDIT - If the first record you look at has the values in it will work, but because it's unbound, the value is kept for every record! Basically i want it to update as soon as you enter any values, and also work for new records!

Cheers

Edd

 

[KenHigg]

Place commands (me.controlname.requery), in the afterupdate events of the two controls that updates the calculated field.

??

 

[boblarson]

make sure that the text boxes that are bound DO NOT have the same name as their fields they are bound to (Microsoft default).

 

[KenHigg]

make sure that the text boxes that are bound DO NOT have the same name as their fields they are bound to (Microsoft default).

Please explain why?

 

[Sharky II]

You'd think so wouldn't ya. Tried Me.txtAmountOwed.Requery in all the logical afterupdates, and also tried it in the afterupdate for the entire form, and on the current too.

 

[Sharky II]

make sure that the text boxes that are bound DO NOT have the same name as their fields they are bound to (Microsoft default).

didn't make any difference mate

 

[KenHigg]

I know I chasing rabbits here but: Have you tried doing the math all over again in the afterupdate event?

me.controlname = Nz([Cost])-Nz([AmountPaidToDate])

 

[boblarson]

I just noticed that you said:

I have the following expression for default value:

It doesn't go in "default" it goes in ControlSource for the unbound textbox.

 

[Sharky II]

I just noticed that you said:

I have the following expression for default value:

It doesn't go in "default" it goes in ControlSource for the unbound textbox.

that's it! many thanks mate

 

[Rich]

I have the following expression for default value:

wrong place, the expression belongs in the control source of the textbox

 

[KenHigg]

I just noticed that you said:

I have the following expression for default value:

It doesn't go in "default" it goes in ControlSource for the unbound textbox.

I be darn - good eyes...

 

[Sharky II]

Thanks guys

 

[Dan_T]

hehe i was looking at that bemused why it didn't work and like all didn't spot the default thing. 10 outta 10 for proper observation. How much time gets wasted looking for errors and climbing teh wall in frustration only to find something simple like that. Best not count aye.

 

[Sharky II]

Guys

If i wanted to actually make this value a field in a table which is automatically updated whenever any of the two variables int he expression are changed, where would i put the 'formula' then?

If i did this, then the company can then search for 'all people who owe them money' as the field stores the value of [Cost of trip] - [Paid so far].

Would i first create a field in the table, then somehow set the value of this field as the result of Nz([Cost])-Nz([AmountPaidToDate]) ?

Basically it'd be nice if it did the exact same thing but actually stored the value in the table, as soon as it changed (ie it changes/recalculates if anything changes).... but would this be really slow/messy?

Thanks guys

 

[KenHigg]

You're fixin to get bashed...

 

[Sharky II]

sorry

 

[KenHigg]

Ah - Just kidding with you. It's just that there are several good reasons not to do a calculated field like the one you described and most experienced db'rs avoid them. When it's so easy to add the values in a query, etc., it's not worth risking your data integrity and additional coding requirements. I'm sure there are more experienced db'rs on this forum that can give you a more detailed explaination...

 

[Sharky II]

Oh right

I managed to get both things working (and yeah i used queries etc for the 2nd thing).

For the blinking textbox thing, i've noticed that i can no longer see the total number of records at the bottom left hand on the main form, until you jump all the way to the last one, only THEN it will say record 2000 of 2000 (of whatever)... usually now it will just say Record 1 etc (as opposed to 1 of 2000!) - anyone know why! Must be something to do with the timer, or the focus, or something!

Thank you

 

[Pat Hartman]

I don't see that any of the suggestions should have solved your problem. Put the calculation in the query. That way you won't need any code and the value will be updated as soon as you change either value.

As to storing the calculated value, it is not necessary. You can apply criteria to a calculated expression.