Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2012
    Posts
    3

    Unanswered: creating an end date from effective date

    Hi! I am trying to figure out why my sequel statement isn't working. I'm very much a novice when it comes to more detailed access programming.
    I have a table for tax rates (tblTaxRate) that has the following fields:
    TaxID
    EffectiveDate
    TotalTax

    I'm trying to get a query to create a begin date and end date field for each entry based on the effective date of the tax rate. I've tried to cobble something together from various sources, but all I get is an error message for the EndDate field. Here is the sequel statement:


    SELECT tblTaxRate.TaxID, tblTaxRate.EffectiveDate AS BeginDate, Nz(DateAdd("d",-1,DLookUp("EffectiveDate","tblTaxRate","TaxID"=" & Nz(DMin("TaxID"," tblTaxRate "," TaxID > " & [TaxID]),0))),#12/31/9999#) AS EndDate, tblTaxRate.TotalTaxRate
    FROM tblTaxRate
    ORDER BY tblTaxRate.TaxID;

    I just don't understand the whole &'s and when to use quotes and all that good stuff. Any help would be greatly appreciated. Thanks in advance - lbb

  2. #2
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    When you say you are trying to create a begin date and end date field for each entry based on the effective date - what do you actually mean i.e. what results are you hoping to end up with?
    Regards
    JD

    Software-Matters

  3. #3
    Join Date
    Feb 2012
    Posts
    3

    clarification

    Sorry for not being clear. The effective date of a tax rate becomes the start date since it's the date when that tax rate first gets used. The end date for the previous tax rate then becomes the effective date for the new tax rate minus one. I'd like for the end date for the current tax rate to be 12/31/9999. Does that make sense now? - lbb

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    N0
    use an effective start date. you dont'need a start AND an end date. if you stroee the startdate then by definition the statrdate of the next tax rate is more than the end data of the last taxrate. on epiece of information serves both purposes. you can always retrieve the relevent rate by requesting the tax date in force at the time of the transaction (eg SELECT TaxRate from TaxTables where StartDate<=transactionDate ORDER by StartDate DESC)

    putting in an artificial end date coudl cuase problems if its not maintained properly. oits also spurious data, the relevent bit is trying to find the current tax date, which should be the the one with the latest start date
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2012
    Posts
    3

    Unhappy not working

    Howdy! I understand what you are saying, but the problem is that all of the effective dates are less than the order date. How do I get it to choose the most recent one? I've tried using Max(EffectiveDate) but that doesn't seem to do the job. Thanks for your help. - lbb

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well to make a reasonable fist of it we would need to see your table design
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    I've tried using Max(EffectiveDate) but that doesn't seem to do the job.
    What actually happens when you try this? What is the SQL of the query concerned?
    I agree with healdem - the EffectiveDate field should be sufficient. Use the table for historical purposes, and use a query to extract the relevant code each time:
    Code:
    SELECT TaxID
    , Max(EffectiveDate) AS StartDate
    , TotalTax
    FROM
    tblTaxRate
    GROUP BY
    TaxID
    , TotalTax
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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