Enter date based on date range

Dear Experts
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:

Table: "Nominal"
[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:
[ID] (Autonumber)
[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?
Who is Participating?

add an extra column with ValidFrom and ValidTo.

You can use BETWEEN operator in your sql query
correlateAuthor Commented:
Hi rajvja

Thanks for this - re the ValidFrom and ValidTo does it go in the "Nominal Table" or the "VAT Return Date" Table?
correlateAuthor Commented:
brilliant - got it with a dlookup using between

=DLookUp("[Return Date]","[VAT Return Date]"," [Purchase Invoice Date] Between [Date From] And [Return Date] ")
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.