Results 1 to 5 of 5

Thread: DLookup

  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I currently have tblPropertyName that has among other fields 4 tax rate fields. There are multiple properties that are identified by PropertyIDs. Currently there is only one set of the 4 sales tax rates for each property. Since tax rates change from time to time, I want to create a new tax rate table to store the effective dates of the tax rates and select the appropriate tax rate based on the RentBeginDate in tblRental

    My thinking was to have the following fields in the new tblTaxRates:
    PropID
    EffectiveFrom
    EffectiveTo
    StateSalesTaxRate
    LocalSalesTaxRate
    AccomodationTaxRate
    HospitalityTaxRate.

    For example say tblTaxRate contained the following information

    Prop Eff EffTo State Local Hosp Accom
    ID From

    1 01/01/09 06/30/09 0.05 0.01 0.01 0.03
    1 07/01/09 01/01/30 0.06 0.01 0.02 0.03
    2 01/01/09 01/01/30 0.04 0.02 0.03 0.07

    There would be a [Enter Property ID] in qryRentCalc0 so only records with the ProperyID entered would be selected.

    In a qryRentCalc0, tblRental and tblTaxRates would be joined by the field PropertyID. To calculate the amount of state sales tax [TotalRoom] * [StateSalesTaxRate]= State Sales Tax Amt. If the RentBeginDate in tblRental were 4/1/09, it should use the 5% state rate. If the RentBeginDate were 7/10/09, it should use the 6% tax rate.

    Is using a DLookup the best way to select the appropriate tax rate? I was trying to use

    StateTaxRatelookup(“[StateSalesTaxRate]”, “tblTaxRate”,”[RentBegDate] between [EffectiveFrom] and [EffectiveTo]”)

    I get an error message that it can’t find [RentBegDate]. [RentBegDate] is in tblRental

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you use DLookup, you don't have to add tblTaxRate to the query.

    The expression could look like this:

    StateTaxRate: DLookup("StateSalesTaxRate", "tblTaxRate", "PropertyID = " & [PropertyID] &" AND RentBegDate Between #" & [EffectiveFrom] & "# And #" & [EffectiveTo] & "#")

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If I don't add tblTaxRate to query, the queryasks for EffectiveFrom and EffectiveTo. The query returns records but the State Tax Rate field is blank. I am trying to figure out the quotation marks. Why doesn't RentBegDate need to be in quotes or brackets?

    Tom



    [quote name='HansV' post='790191' date='21-Aug-2009 16:45']If you use DLookup, you don't have to add tblTaxRate to the query.

    The expression could look like this:

    StateTaxRate: DLookup("StateSalesTaxRate", "tblTaxRate", "PropertyID = " & [PropertyID] &" AND RentBegDate Between #" & [EffectiveFrom] & "# And #" & [EffectiveTo] & "#")[/quote]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Sorry, I had the condition wrong, try this instead:

    StateTaxRate: DLookup("StateSalesTaxRate", "tblTaxRate", "PropertyID = " & [PropertyID] &" AND EffectiveFrom <= #" & [RentBegDate] & "# AND EffectiveTo >= #" & [RentBegDate] & "#")

    Field names within the wherecondition string only need square brackets if they contain spaces or punctuation.

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many thanks the new code works fine.

    Tom




    [quote name='HansV' post='790268' date='22-Aug-2009 12:07']Sorry, I had the condition wrong, try this instead:

    StateTaxRate: DLookup("StateSalesTaxRate", "tblTaxRate", "PropertyID = " & [PropertyID] &" AND EffectiveFrom <= #" & [RentBegDate] & "# AND EffectiveTo >= #" & [RentBegDate] & "#")

    Field names within the wherecondition string only need square brackets if they contain spaces or punctuation.[/quote]

Posting Permissions

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