Enter date based on date range

Posted on 2012-09-21
Last Modified: 2012-09-21
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?
Question by:correlate
    LVL 11

    Accepted Solution


    add an extra column with ValidFrom and ValidTo.

    You can use BETWEEN operator in your sql query

    Author Comment

    Hi rajvja

    Thanks for this - re the ValidFrom and ValidTo does it go in the "Nominal Table" or the "VAT Return Date" Table?

    Author Closing Comment

    brilliant - got it with a dlookup using between

    =DLookUp("[Return Date]","[VAT Return Date]"," [Purchase Invoice Date] Between [Date From] And [Return Date] ")

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now