Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DLookup function in a query (2000 all updates)

    Below is the SQL behind a query...
    <code>SELECT tblProjects.QuoteDate, tblProjects.ProjectID, IIf(IsNull([Company]),[NameLast] & ", " & [NameFirst],[Company]) AS FullName, Sum([Quantity]*Round([ItemCost]*1.2,2)) AS TotalMaterials, Nz([TotalMaterials],0)*[CurPSTRate] AS PSTCost, Nz([TotalMaterials],0)*[CurGSTRate] AS GSTCost, [TotalMaterials]+[PSTCost]+[GSTCost] AS GrandTotal, DLookUp("PSTRate","tblPST") AS CurPSTRate, DLookUp("GSTRate","tblGST") AS CurGSTRate, Year([QuoteDate]) & "-" & [tblCustomers].[CustomerID] & "-" & [tblProjects].[ProjectID] AS ProjectNbr
    FROM (tblCustomers RIGHT JOIN tblProjects ON tblCustomers.CustomerID = tblProjects.CustomerID) INNER JOIN tblMaterials ON tblProjects.ProjectID = tblMaterials.ProjectID
    GROUP BY tblProjects.QuoteDate, tblProjects.ProjectID, IIf(IsNull([Company]),[NameLast] & ", " & [NameFirst],[Company]), tblMaterials.Invoice, tblProjects.PSTExempt, tblProjects.GSTExempt, tblCustomers.CustomerID
    HAVING (((tblMaterials.Invoice)=No))
    ORDER BY tblProjects.QuoteDate, tblProjects.ProjectID;</code>

    The part I am concerned about are the two columns DLookUp("PSTRate","tblPST") AS CurPSTRate and DLookUp("GSTRate","tblGST") AS CurGSTRate

    tblPST has fields PSTRate, StartDate, EndDate, to accommodate governmental changes to the tax rate. Same with tblGST. A specific tax rate will, therefore have StartDate and an EndDate.

    So I want to add criteria to those two expressions, so that the query selects the tax rate that is in effect on QuoteDate. I have tried all the different formulations to get the criteria expression correct but can't come up with one that Access accepts.

    Any suggestions of a method would be appreciated.

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookup function in a query (2000 all updates)

    Try

    DLookUp("PSTRate","tblPST","StartDate <= #" & [QuoteDate] & "# AND EndDate >= #" & [QuoteDate] & "#")

    If there is a chance that your database will be used by someone using another date format than US date format, replace both instances of [QuoteDate] with Format([QuoteDate], "mm/dd/yyyy")

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup function in a query (2000 all updates)

    That works. Thanks.

    I was trying to go at it using "Between" (e.g. tblProjects.QuoteDate Between tblPST.StartDate And tblPST.EndDate) but probably wasn't getting the # signs properly placed.

    After seeing your formulation, it appears that DLookUp("PSTRate","tblPST","#" & [QuoteDate] & "# Between [StartDate] And [EndDate]") also work.

    Is there a reason to use one over the other?

    Tom

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookup function in a query (2000 all updates)

    I wasn't sure the condition with Between would work in this situation, but if it does, it is easier and shorter than the expression with two separate conditions.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup function in a query (2000 all updates)

    Thanks.

    Thanks also for the tip regarding the possibility of someone else using different system date formats.

    Personally, even though Canadian, I much prefer mm/dd/yyyy (probably because I am 70 years old and that's the way I have always done it!). But lots of folks here use dd/mm/yyyy and a growing number of folks use yyyy/mm/dd. My bank insists on this latter format for writing cheques.

    Tom

  6. #6
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup function in a query (2000 all updates)

    Regarding the DLookup criteria, DLookUp("PSTRate","tblPST","#" & [QuoteDate] & "# Between [StartDate] And [EndDate]", I want also to use that in the preparation and printing of an Invoice.

    It would seem that all I need to do is change the expression to DLookUp("PSTRate","tblPST","#" & Date() & "# Between [StartDate] And [EndDate]" - changing QuoteDate to Date(). So the tax rates on the date of printing the Invoice would apply...which would, I assume, be the normal situation.

    Perhaps I am worrying needlessly. But say that an Invoice is printed on May 27. On June 1, the tax rate changes. In the meantime, the customer lost the Invoice or it has been lost in the mail so the customer never received it. But, if you print a new Invoice on June 1, that would call for the new tax rates, and that could mess up end-of-the-month accounting (Invoice has been posted with taxes at one rate, but the Customer will be paying by the new Invoice which carries the new rates).

    What's the best way to deal with this possibility, remote as it may seem?

    Tom

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookup function in a query (2000 all updates)

    I'd create an InvoiceDate field in the relevant table, with Date() as Default Value.
    Then use "#" & [InvoiceDate] & "# Between [StartDate] And [EndDate]" as WhereCondition argument in the DLookup expression.
    So if an invoice is created on May 27, the InvoiceDate will be May 27 by default. If you print the invoice again on June 1, it'll still use May 27 to look up the tax rate.

  8. #8
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup function in a query (2000 all updates)

    Sounds perfect.

    In the Projects table, I have a [CompletionDateActual] field that gets filled in when the Invoice Details are prepared via a command button. That should only happen when he is ready to print the Invoice. So it would seem that I can use that field...it should do the same job as inserting an additional Invoice Date field.

    Tom

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookup function in a query (2000 all updates)

    OK, that sounds reasonable.

Posting Permissions

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