determine which time interval the date falls into

  • Thread starter Thread starter Aivars
  • Start date Start date
A

Aivars

Hello,

I have a simple table Rates, which shows what freight rates were
applicable in different time intervals/periods:

StartDate EndDate FreightRate
12.10.2005 12.10.2006 20500
12.10.2006 12.05.2007 21500
12.05.2007 12.08.2007 22000
12.08.2007 12.01.2008 23000

It is probably very simple but how can I select/determine what
FreightRate was applicable on a given specific date, lets say 25.03.2005
(should be 21500) or on 13.07.2007 (22000)?
I want to query this table and pass a specific date as a parameter to
this query to get back this freightrate number.
I could not find anything on a Google so maybe it is really simple.

Thanks a lot


Aivars
 
One small problem with your table is that you have overlapping dates. For
instance if the date were 12.10.2006 which rate would you want from these
two records?
12.10.2005 12.10.2006 20500
12.10.2006 12.05.2007 21500

You can edit the table to remove the overlap between begin and end dates OR
you can change the expression to something like:
(using Allen Browne's example)
DLookup("FreightRate","Table1",
Format([OrderDate], "\#mm\/dd\/yyyy\#") & " >[StartDate] And " &
Format([OrderDate], "\#mm\/dd\/yyyy\#") & "<=EndDate]")

Or alternatively

DLookup("FreightRate","Table1",
Format([OrderDate], "\#mm\/dd\/yyyy\#") & " >=[StartDate] And " &
Format([OrderDate], "\#mm\/dd\/yyyy\#") & "<EndDate]")
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Allen Browne said:
Use DLookup().

The expression will be something like this:
=DLookup("FreightRate", "Table1",
Format([OrderDate], "\#mm\/dd\/yyyy\#") &
" Between [StartDate] And [EndDate]")

More info about DLookup():
http://allenbrowne.com/casu-07.html

Do not change the Format() to suit your local dates. More about that:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Aivars said:
Sorry,
I made a mistake. e.g., frieght rate on 23.12.2006 should be 21500

Aivars
Click to expand...
Click to expand...
 
That's a good point, John

We should probably have pointed out that the best way to build this table
would be with a StartDate only. That avoids the issues of overlapping dates
and missed dates.

It takes a bit more effort to create a query that gives you the end date so
you can query it in the same way, but it's worth the effort. Tom Ellison
explains how in this article:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html
Aivars, it's up to you if you want to do that direction, but at least you
are aware of it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

John Spencer said:
One small problem with your table is that you have overlapping dates. For
instance if the date were 12.10.2006 which rate would you want from these
two records?
12.10.2005 12.10.2006 20500
12.10.2006 12.05.2007 21500

You can edit the table to remove the overlap between begin and end dates
OR you can change the expression to something like:
(using Allen Browne's example)
DLookup("FreightRate","Table1",
Format([OrderDate], "\#mm\/dd\/yyyy\#") & " >[StartDate] And " &
Format([OrderDate], "\#mm\/dd\/yyyy\#") & "<=EndDate]")

Or alternatively

DLookup("FreightRate","Table1",
Format([OrderDate], "\#mm\/dd\/yyyy\#") & " >=[StartDate] And " &
Format([OrderDate], "\#mm\/dd\/yyyy\#") & "<EndDate]")
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Allen Browne said:
Use DLookup().

The expression will be something like this:
=DLookup("FreightRate", "Table1",
Format([OrderDate], "\#mm\/dd\/yyyy\#") &
" Between [StartDate] And [EndDate]")

More info about DLookup():
http://allenbrowne.com/casu-07.html

Do not change the Format() to suit your local dates. More about that:
http://allenbrowne.com/ser-36.html

Aivars said:
Sorry,
I made a mistake. e.g., frieght rate on 23.12.2006 should be 21500

Aivars


Aivars wrote:
Hello,

I have a simple table Rates, which shows what freight rates were
applicable in different time intervals/periods:

StartDate EndDate FreightRate
12.10.2005 12.10.2006 20500
12.10.2006 12.05.2007 21500
12.05.2007 12.08.2007 22000
12.08.2007 12.01.2008 23000

It is probably very simple but how can I select/determine what
FreightRate was applicable on a given specific date, lets say
25.03.2005 (should be 21500) or on 13.07.2007 (22000)?
I want to query this table and pass a specific date as a parameter to
this query to get back this freightrate number.
I could not find anything on a Google so maybe it is really simple.

Thanks a lot


Aivars
Click to expand...
Click to expand...
Click to expand...
 
Allen and John,
Thank you very much for advices. I liked and managed to adopt the
solution by Tom Ellison, with a subquery and storing only startdate of
the new freightrate period. I will need to redesign a bit my tables now.
Actually this helps me a lot because by this you helped me to solve also
the problem with employee salary changes and similar problems.
I am a newbee in Access (coming slowly from Excel which i like very
much) and still learning sql. I think thats why I like query solutions
more that DLookup. Dlookups reminds me of Excel Dbase functions which to
me are useless if compared with Excels SUMPRODUCT function.
Also reading the newsgroup posts I get the feeling that using queries
are preferable to DLokups.


Thanks again,
Aivars
 
Also reading the newsgroup posts I get the feeling that using queries
are preferable to DLokups.
Click to expand...

lovely typo - especially since DLookUps can cause apparent DLockUps!

John W. Vinson [MVP]
 
Back
Top