overwrite a calculated field

[VbaHell]

Hello all

I am new to Microsoft Access and would like to ask a question

I have a calculated field which looks at a field called "Total Cost" and divides this by 6 to get the correct VAT value

I sometimes want to edit this field if the product has a reduced or no VAT charge

Is there a way to have a calculate field that you can edit please

 

[alansidman]

Although Access allows for calculated fields, in the mind of good Access developers, this is a no-no. Calculations should only be done in Queries which allow for the functionality and flexibility you seek. Having calculated fields create issues similar to what you are experiencing.

 

[stumac]

Is this a calculated field on a table? As per the above comment this is generally a bad idea - and bad design.

e.g. Vat is currently 20%, what if it changes to be 21%... If you update the formula then it will update for previous orders causing all historic data to be incorrect.

regardless of where it is being calculated (table, form, query) it can be done. I would add a field to the table (say VAToverride) and get the calculation to check that is 0 before calculating the VAT.

The expression would be something like: IIf([VAToverride]=0,[Total Cost]/6,[VATOverride])

I would recommend you save the vat rate in the order and use that for the calculation - that way if you look at the record in the future you should easily be able to work out the correct vat amount.

 

[VbaHell]

What you are both saying makes clear sense, it's a bad idea, I am very new to Access so starting to learn these pitfalls

Thanks