Thread: Finding the Next Record

Hi Guys,

hope you can help me again on my scenario:

How do i get the next data for specific condition.

here's my scenario: i have two tables:

Table1: RAW Data (which consist of Invoice Date) and Country
Table2: Lookup Data ( which consist of Country, Month and days)

Example: In my Raw data table i have US (country) and my Invoice Date is 1/1/2012, then in my Lookup Data i have two rows consist of the following.

Table 2:
US - 1-7
US - 1-22
US - 1 31

if invoice date is fall on 1/1/2012 up to 1/7/2012. payment date should be 1/7/2012
if invoice date is fall on 1/8/2012 up to 1/22/2012. payment date should be 1/22/2012
if invoice date is fall on 1/23/2012 up to 1/31/2012. payment date should be 1/31/2012


how do i get this result?

Hope you can help me on this.

What do you mean by 'two rows' in table 2?

ideally you should have three fields, one for country, one for month and one for the day.

then we can build a dlookup query that will test the date range.