query field

[bbcwjun]

I have a field in a Query that makes a calculation (ie. datediff) When I put that field in a form, i would like to be able to change the data in it that shows up but it says Field is based on an expression and cannot be edited...
Is there any way to edit this field or how to put it in a form to edit it? I tried opening the query and editing it also but says same thing??

THANKS

 

[DES]

Try putting an unbound text box on the form.

Then go back to the query and in the criteria field for the datediff field have it use the unbound text box.

Then back at the form on the text box's afterupdate event have it requery the form Me.Requery.

Should give you what you want.

 

[Pat Hartman]

I'm not sure where you're going with this. As you have the form now, the control is bound to the query but since the field is calculated by the query it is not being stored anywhere. And as you have noticed, calculated fields are not updateable. The implication is that you want to store this difference somewhere because (otherwise what is the point of overriding the calculation) but you want to be able to override the calculation manually. Is this the case?

 

[bbcwjun]

Thanks for helping Pat and all. I don't need to overide the calculation. Either store it somewhere and change it somehow after it has been entered. (ie. latefee is calculation of datediff's done in a query).

If this doesn't help, I can send you the file to take a quick peek?

THANKS!

 

[Pat Hartman]

If lateFee is calculated based on the difference between two dates, you NEVER need to store it. Just calculate it whenever you need it. If the calculated value may be overriden (ie increased or decreased by some manual calculation) then it DOES need to be stored. If you need to store it:
1. remove the current calculation from the query.
2. add a column to the table to store the lateFee and bind it to a field on the form.
3. In the Current event of the form place the calculation of the late fee:
Me.YourLateFeeBoundField = your calculation
4. In the AfterUpdate event of the date field call the Current event sub to recalculate the lateFee:
Call Form_Current