• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

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?
  • 2
1 Solution

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] ")

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now