Date Range Question

  • Thread starter Thread starter Istari
  • Start date Start date
I

Istari

I have a table which contains prices which are valid between a set of dates.

I have
Season1 Start (date)
Season 1 End (date)
Season 1 Price (number)

Season 2 Start (date)
Season 2 End (date)
Season 2 Price (number)

etc...

Now, I need to check whether a date value given in a form based on another
table falls within which season, and then use dlookup to insert the
corresponding price value into a field on the form.

Any help would be much appreciated.

Istari
 
I have a table which contains prices which are valid between a set of dates.

I have
Season1 Start (date)
Season 1 End (date)
Season 1 Price (number)

Season 2 Start (date)
Season 2 End (date)
Season 2 Price (number)

etc...

Now, I need to check whether a date value given in a form based on another
table falls within which season, and then use dlookup to insert the
corresponding price value into a field on the form.
Click to expand...

Do you want to insert the price into an unbound field for display, or
actually store it in the table? You can actually find this value
without doing a DLookUp at all - let's say you have the date you want
to price in a field named VisitDate; create a Query joining that table
(which I'll call Visits) to this table (which I'll call Rates). To
make it easier, first join VisitDate to Start.

Then open the SQL window and change

ON [Visits].[VisitDate] = [Rates].[Start]

to

ON [Visits].[VisitDate] >= [Rates].[Start] AND [Visits].[VisitDate] <=
[Rates].[End]

Include the Price field in your query and you won't need to store it
at all.
 
Back
Top