C. the vat rate should be stored along with the other pertinent details of the record (transaction?). Having to find it via a complicated expression is not only not necessary, it smacks of less than optimal design. The vat rate you save with each record should probably come from an unbound combo on a form that allows users to look up and apply the rate.
Generally speaking
- any amounts that are calculated now, or again in the future when we're talking about historical records, should be calculated in a query or form.
- values such as discounts, tax rates, that are part of the calculation, should be saved as part of the record
- values such as prices, which are subject to change and would cause historical records to be incorrect (e.g. an old invoice) need special handling.
OK, I am not an expert on financials and most of my db experience has been in maintenance, reliability and quality/environmental systems, so talk is cheap - I'm short on such experience.
I think way back someone had a suggestion as to how your tables should look. I know it would be a pain after all you've just gone through, but if you don't rebuild your db and create the necessary tables, you will be back here with more issues. One of them I believe you're overlooking (I commented on this) is the use of mv (multi value) fields. Unless you're dealing with Sharepoint, it is best to avoid them. If you want to pull a value from one of these fields, it requires special handling. Think of it this way: Access creates and stores a hidden table and the value you see in one of these fields is not really there. So if you want to query it, you can't in the normal fashion because it doesn't really exist where you're seeing it.
D. I know. However, it is not part of the domain upon which you are doing the DLookup, thus it will never work IMHO.
G. How the form looks is irrelevant. What's behind the form that governs the way it works is just as important. You don't seem to have that right.
H. Your cat i doomed if you don't understand how a function such as DLookup works. Did anyone refer you to here?
Note the part that says
Criteria
Optional. It is the WHERE clause to apply to the TableName.
You can't include other tables when trying to build the criteria part of the expression.