Dcount Function in a Report

Topic

New Reply

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?

Reply | Quote

Replies

try
dcount(“*”,”tblValuations”, “SalesID=” & me.salesid & ” and tblValuations!CreateValuation=” & date()-1)

Reply | Quote

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!

Reply | Quote