# Calculating Source Information

• ### Question

• I have a table (Customers) with a field that holds 'Source' information.  I am trying to count the number of occurances of this field within a user defined time frame.  I have put together the following:

This gives us the whole table grouped by Source; Counting the occurances; and summing the contract price....
SELECT Customers.Source, Count(Customers.CustDate) AS CountCustDate, Sum(Customers.ContractPrice) AS SumContractPrice
FROM Customers
GROUP BY Customers.Source
ORDER BY Customers.Source;

I have put this together to allow for grabbing the information within a user defined date range:
SELECT DCount("Customers.Source" = "Call/Walk In","Customers","Source Between #" & [Enter Start Date] & "# And #" & [Enter End Date] & "#") AS CallWalkIn;

...however, this returns a "0", when it should be "5".

...this will also be used to count for several other values.

As you can see, I will also be wanting to sum the 'contractprice' values.

I tried this too, but it did not work....

SELECT Customers.Source, Count(Customers.CustDate) AS CountOfCustDate, Sum(Customers.ContractPrice) AS SumOfContractPrice
FROM Customers
WHERE "Customers.CustDate Between #" & [StartDate] &  "# And #" & [EndDate] & "#"
GROUP BY Customers.Source
ORDER BY Customers.Source;

I know I am close, but the equation to equal the captured field values is keeping me at bay....thoughts?

Thank you, Mayhem65

Wednesday, March 7, 2018 8:54 PM

### All replies

• Hi,

If you're planning to display this on a form, I would probably suggest to avoid using a parameter query.

So, if your first query returns all the records from the table, then your DCount() or DSum() expression can calculated the totals within a given date range without any problems.

For example, to count the Source from your original query, you could try something like:

=DCount("*", "QueryName", "Source='Call/Walk In' AND CustDate Between #" & [StartDate] & "# And #" & [EndDate] & "#")

Hope it helps...

Wednesday, March 7, 2018 9:01 PM
• Try this:

PARAMETERS [StartDate] DATETIME, [EndDate]  DATETIME;
SELECT Source,
COUNT(Customers.CustDate) AS CountOfCustDate,
SUM(Customers.ContractPrice) AS SumOfContractPrice
FROM Customers
WHERE CustDate BETWEEN [StartDate]  AND [EndDate]
GROUP BY Source
ORDER BYSource;

Not that when you specify a column as the operand of the COUNT operator, this will return the number of rows with a value at the column position.  If the column position cannot be NULL, or you want to include NULLs in the count use COUNT(*) more efficiently.

Another thing to note when restricting a query by a date range with a BETWEEN....AND operation is that any rows with a non-zero time element in a date/time value on the final day of the range will be ignored.  This is because there is no such thing as a 'date value' per se in Access's implementation of the date/time data type.  When you enter a date in a column, the value really represents the point of time at midnight at the start of the day.  Unless you have specifically disallowed date/time values with a non-zero time element in the table design (by a validation rule), you cannot completely ignore the possibility of such date/time values being present.  The safe way of specifying the range therefore, is:

WHERE CustDate >= [StartDate]  AND CustDate < [EndDate]+1

Ken Sheridan, Stafford, England

• Edited by Friday, March 9, 2018 1:33 PM Typo correcteed.
Friday, March 9, 2018 1:32 PM
• Hello Mayhem65,