Dlookup changing vat

[eyalco]

I have an invoice form (access 2003) where vat is a part of payment. The problem is : when VAT changes, I want the new VAT to be placed in my form.
I have a VAT table where I have [from date] - [to date] and the relevant VAT.
I'm trying to use :

DlookUp("[MyField]","MyTable","#07/02/2009# Between [from_date] And [to_date]")

But VAT taken is the old VAT (the change was on july 1st).

What can I do please?
Thanks.

[MikeTheBike]

@eyalco
Hi

Have you tried

DlookUp("[MyField]","MyTable","[from_date] <= #07/02/2009# And [to_date] >=#07/02/2009#")

??

MTB

[mshmyob]

To make life simpler you could make a slight redesign.

You keep your VAT table but only need one record with just the VAT rate in it without the dates. When VAT changes just overwrite the existing VAT rate.

Then in your invoice table (not the InvoiceDetail table) you save the VAT rate that was used for that invoice.

No need to be creating new records with date ranges for VAT rates and doing complicated lookups. Each invoice will save the VAT rate used for that particular invoice.

cheers,