I need to create a piece of code which enters a corresponding date from a look up in another table.
The purpose of which is to determine which VAT accounting period an invoice falls within.
The fields in question are as follows:
[Purchase Invoice Date] (Date/Time formatted) - this is the date which is to be cross referenced to the VAT Return Date
[Submitted in VAT Return] (Date/Time formatted) - This is the field for the end result (Date/Time formatted)
Table: "VAT Return Date" which contains 2 fields:
[Return Date] (Date/Time formatted)
So the logic would be to lookup the last date that [Nominal].[Purchase Invoice Date] is earlier than or equal to in [VAT Return Date].[Return Date] and place the result into [Nominal].[Submitted in VAT Return].
If it helps I can certainly add in an extra field to the table "VAT Return Date" so there are 2 date fields … [Return Date From] & [Return Date to]
Can anybody help?