Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Effective Date query (A2k3 SP1)

    Hi All,
    I have a simple select query. The Main table is tblStateLocalityDetails and its dependent table is tblStateLocalityPriceTracker.
    The dependent table has a field labeled EffectiveDate, which is when certain prices are in effect. So for a certain StateLocality there can be 1 or many EffectiveDates along with the pertinent pricing which goes with it.
    I am having problems with selecting the Max EffectiveDate and *only* the Max EffectiveDate when the following sql statement is run.

    <pre>strSQLDefault = "SELECT [StateLocalityPKID], " & _
    "[StateName], " & _
    "[StateCode], " & _
    "[LocalityName], " & _
    "[LocalityCode], " & _
    "[AbsentCounted], " & _
    "[PhlebFeesInvoiced], " & _
    "[GreaterThanAmount], " & _
    "[ChargedPerPerson], " & _
    "[PerPersonPrice], " & _
    "[PerPersonPreviousDiscount], " & _
    "[IncompletePerPersonPrice], " & _
    "[ClosedPerPersonPrice], " & _
    "[ChargedPerCase], " & _
    "[PerCasePrice], " & _
    "[IncompleteCasePrice], " & _
    "[ClosedCasePrice], " & _
    "Max([EffectiveDate]) "
    strSQLDefault = strSQLDefault & "FROM qryStateLocalityDefaults "
    strSQLDefault = strSQLDefault & "WHERE [StateCode] = '" & strState & "'"
    strSQLDefault = strSQLDefault & " AND [LocalityCode] = '" & strLocal & "'"
    strSQLDefault = strSQLDefault & " AND [EffectiveDate] <= #" & rst![TestRequestDate] & "#"
    ---
    ---tried the following also----
    'strSQLDefault = strSQLDefault & " AND [EffectiveDate] <=
    (SELECT Max(effectiveDate) As EffDate FROM qryStateLocalityDefaults As Test
    ---
    WHERE [EffectiveDate] <= #" & rst![TestRequestDate] & "#)"

    GROUP BY StateLocalityPKID, StateName, StateCode, LocalityName, LocalityCode,
    AbsentCounted, PhlebFeesInvoiced, GreaterThanAmount, ChargedPerPerson,
    PerPersonPrice, PerPersonPreviousDiscount, IncompletePerPersonPrice,
    ClosedPerPersonPrice, ChargedPerCase, PerCasePrice, IncompleteCasePrice,
    ClosedCasePrice, EffectiveDate
    </pre>


    My results always return *all* of the data with an EffectiveDate <= the [TestRequestDate] not just the Max EffectiveDate.
    I've tried many variations of the above code with no success.
    Would someone please show me the error of my ways?
    Thank you.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Effective Date query (A2k3 SP1)

    What if you use :
    SELECT TOP 1 [StateLocalityPKID]
    ...
    Remove the max from the line "Max([EffectiveDate]) "
    and add
    ORDER BY EffectiveDate DESC
    Francois

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Effective Date query (A2k3 SP1)

    Hi Francois,
    Thank you. That works nicely.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •