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

    Dcount Function in a Report

    Hi,

    Having a complete memory block.

    I have a report where I have based it upon the salesman we have.

    Each salesman has his unique id of 'salesid' in the table and other tables I want to query.

    In the report, I would like to use a dcount function to count how many appoinments each salesman has booked.

    I have a blank field in the report where I would like to display the number of appointments that salesman has booked. The table I need to query is called 'tblValuations', where I need to query where the salesid equals the line on the report and also the date each record was created (createvaluation) is today.

    I have tried to use dcount("*","tblValuations",SalesID= & me.salesid & " and tblValuations!CreateValuation=" & date()-1)

    However this does not work.

    Any ideas?
    Best Regards,

    Luke

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    try
    dcount("*","tblValuations", "SalesID=" & me.salesid & " and tblValuations!CreateValuation=" & date()-1)

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

    Two more variations

    Quote Originally Posted by Lstclair55 View Post
    Hi,

    Having a complete memory block.

    I have a report where I have based it upon the salesman we have.

    Each salesman has his unique id of 'salesid' in the table and other tables I want to query.

    In the report, I would like to use a dcount function to count how many appoinments each salesman has booked.

    I have a blank field in the report where I would like to display the number of appointments that salesman has booked. The table I need to query is called 'tblValuations', where I need to query where the salesid equals the line on the report and also the date each record was created (createvaluation) is today.

    I have tried to use dcount("*","tblValuations",SalesID= & me.salesid & " and tblValuations!CreateValuation=" & date()-1)

    However this does not work.

    Any ideas?
    Or how about this: DCount("*","tblValuations","[SalesID]=" & Me.SalesID.Value & " AND DateValue([CreateValuation])=Date()")

    I'm assuming SalesID is a numeric value. I use DateValue() to strip away any time component so your are assured of a match with the Date() function. Note that I put the Date() function inside the criteria rather than appending its results to it. If you want to append the results in a fashion similar to your example, use:

    DCount("*","tblValuations","[SalesID]=" & Me.SalesID.Value & " AND DateValue([CreateValuation])=" & Format$(Date(),"\#mm/dd/yyyy\#))

    Of course, there are probably 247 other ways to accomplish your goal. Enjoy!

Posting Permissions

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