Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    505
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with formula

    Hi,

    I have the following formula on a report:

    =DCount("*","tblValuations","[tblValuations]![CreateValuation]>=[reportdate] and <=[reportdate2]" & " and [tblValuations]![salesID]=" & [Reports]![tblSalesman2]![salesid])

    However it just reports an error I suspect it is to do with the "[tblValuations]![CreateValuation]>=[reportdate] and <=[reportdate2]" section?
    Best Regards,

    Luke

  2. #2
    Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    91
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Try [tblValuations]![CreateValuation]>=[reportdate] and [tblValuations]![CreateValuation]<=[reportdate2]

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,329
    Thanks
    1
    Thanked 13 Times in 13 Posts
    And just to save yourself some time in the future, since your DLookup is against a single table, you don't need to include the tablename with the field names in the WHERE clause.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    New Lounger
    Join Date
    Mar 2013
    Posts
    16
    Thanks
    0
    Thanked 1 Time in 1 Post

    Lightbulb A clean solution

    Quote Originally Posted by Lstclair55 View Post
    Hi,

    I have the following formula on a report:

    =DCount("*","tblValuations","[tblValuations]![CreateValuation]>=[reportdate] and <=[reportdate2]" & " and [tblValuations]![salesID]=" & [Reports]![tblSalesman2]![salesid])

    However it just reports an error I suspect it is to do with the "[tblValuations]![CreateValuation]>=[reportdate] and <=[reportdate2]" section?
    Here is a clean solution for your DCount:

    =DCount("*","tblValuations","([CreateValuation] Between [reportdate] and [reportdate2]) AND [SalesID] = " & [Reports]![tblSalesman2]![SalesID].Value)

    I enclose the date part of the criteria within parenthesis more for clarity than anything else; it's a habit. Be sure that your [CreateValuation] and both reportdates are all just date values with no time portion attached, or you might not get exactly the count you expect. You can assure that with the DateValue() function:


    =DCount("*","tblValuations","(DateValue([CreateValuation]) Between DateValue([reportdate]) and DateValue([reportdate2])) AND [SalesID] = " & [Reports]![tblSalesman2]![SalesID].Value)

    Of course, this level of precision is not necessary if you know for certain that you never have time-of-day in your dates.

    Cheers!

  5. #5
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    505
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks.

    I have a similar problem now with the following:

    =DCount("[IncomingCalls]","tblCallHistory","([tblCallHistory]![CallDate] Between [dates1] and [dates2]) AND [SalesID] = " & [Reports]![rptWeeklyActivityReport]![SalesID].[Value])

    I basically want it to add the value of all reords in the field IncomingCalls between the two dates specified and for that particular person based upon their salesid. However it is not giving me the information back I expect, it is only counting the number of records.
    Best Regards,

    Luke

  6. #6
    New Lounger
    Join Date
    Mar 2013
    Posts
    16
    Thanks
    0
    Thanked 1 Time in 1 Post

    It is doing what you ask

    Quote Originally Posted by Lstclair55 View Post
    Thanks. I have a similar problem now with the following:

    =DCount("[IncomingCalls]","tblCallHistory","([tblCallHistory]![CallDate] Between [dates1] and [dates2]) AND [SalesID] = " & [Reports]![rptWeeklyActivityReport]![SalesID].[Value])

    I basically want it to add the value of all reords in the field IncomingCalls between the two dates specified and for that particular person based upon their salesid. However it is not giving me the information back I expect, it is only counting the number of records.
    Forgive me for pointing it out, but in your example you have asked it to Count, not Add. Use DSum(). I hope this solves your problem.

Posting Permissions

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