Re: Date range question

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Gerald Stanley (gcstanley_at_dsl.pipex.com)
Date: 03/29/04 

Date: Mon, 29 Mar 2004 04:02:18 -0800

If you have multiple seasons on one row of your price
table, I would recommend that you normalise the table to
have one season per row. If you do, then the DLookUp
statement is along the following lines

=DLookUp("seasonPrice","Season","seasonStartDate <= #" &
[saleDate] & "# And seasonEndDate>= #" & [saleDate] & "#")

where
Season is the name of the table holding the prices
seasonPrice, seasonStartDate and seasonEndDate are the
names of the columns on the Season table
[saleDate] is the name of the control on the form

Hope This Helps
Gerald Stanley MCSD
>-----Original Message-----
>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
>
>
>.
>

Relevant Pages

  • Re: Date range question
    ... The Application will be used to create Cost estimates for travel ... There are a number of different price permutations for each ... season, each saved as a field in each season (eg Bed and breakfast, Dinner ... > seasonPrice, ...
    (microsoft.public.access.forms)
  • Re: Dlast vs Dlookup and Dmax
    ... > DLookup() returns the first match - not what you want. ... >>I want to find the last record and price for a stock in another table. ... >> this time I have found that Dlast is the function I want. ...
    (microsoft.public.access.forms)
  • Re: Trying to Return One value based on other Criteria
    ... Your DLookup() has incorrect syntax. ... The one for the current Customer# shown in the form? ... > Price) as three different fields. ...
    (microsoft.public.access.queries)
  • Re: date comparison
    ... If you need the current price for every row of a report, use a subquery by ... DLookup() is a little dicey, as you do not get to specify the order of ... > I need to figure out how much to charge for the equipment. ...
    (microsoft.public.access.queries)
  • Re: Just Take It ??
    ... The error message indicates that the arguments for DLookup() are malformed. ... >> The sample uses DLookupto get the current price for the selected ... >>> when i choose a product and measurement in the invoice form, ...
    (microsoft.public.access.modulesdaovba)