Help with formula

Topic

New Reply

Hi,

I have the following formula on a report:

=DCount(“*”,”tblValuations”,”[tblValuations]![CreateValuation]>=[reportdate] and =[reportdate] and <=[reportdate2]"[/B] section?

Reply | Quote

Replies

Try [tblValuations]![CreateValuation]>=[reportdate] and [tblValuations]![CreateValuation]<=[reportdate2]

Reply | Quote

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.

Reply | Quote

Hi,

I have the following formula on a report:

=DCount(“*”,”tblValuations”,”[tblValuations]![CreateValuation]>=[reportdate] and =[reportdate] and <=[reportdate2]"[/B] 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!

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote