Calculating how many enqiries there've been between two dates

S

Stephen Glynn

This sounds as if it should be simple, but it's thrown me.

How do I write a query that just gives me the total number of enquiries
I've received between two dates?

I'm using Count() on EnquiriesID, but as soon as I put in any parameters
for EnquiryDate I get a total for the number of enquiries received each
day during the period rather than a single figure for the whole month.

Steve
 
E

Ed Robichaud

In your [EnquiryDate] column, change "Group By" to "Where" (assuming this
is where you've set the date range criteria). If you set this up as a
parameter query, you can reuse it for any date range. Better yet, create a
form (with calendars) to capture your desired date range, then use those
unbound form controls as your query parameters.
-Ed
 
J

Joan Wild

It sounds as though you've got Group By for the EnquiryDate - change it to
Where instead and put the criteria in the criteria row.
 
S

Stephen Glynn

Thanks. That's where I'd gone wrong.

I'm trying to do as you suggest on a form with text boxes rather than
calendars. The criteria I've set are:

Between [Forms]![MyForm]![StartDate] And [Forms]![MyForm]![EndDate]

How do I get the query to refresh after I've opened the form and entered
the dates?

Steve
In your [EnquiryDate] column, change "Group By" to "Where" (assuming this
is where you've set the date range criteria). If you set this up as a
parameter query, you can reuse it for any date range. Better yet, create a
form (with calendars) to capture your desired date range, then use those
unbound form controls as your query parameters.
-Ed

This sounds as if it should be simple, but it's thrown me.

How do I write a query that just gives me the total number of enquiries
I've received between two dates?

I'm using Count() on EnquiriesID, but as soon as I put in any parameters
for EnquiryDate I get a total for the number of enquiries received each
day during the period rather than a single figure for the whole month.

Steve
Click to expand...
Click to expand...
 
J

Joan Wild

Usually such a form is unbound. You can add a button to this form that
opens the query, or better yet another form showing the information. In
your case a msgbox may be sufficient to just display the count.

--
Joan Wild
Microsoft Access MVP

Stephen said:
Thanks. That's where I'd gone wrong.

I'm trying to do as you suggest on a form with text boxes rather than
calendars. The criteria I've set are:

Between [Forms]![MyForm]![StartDate] And [Forms]![MyForm]![EndDate]

How do I get the query to refresh after I've opened the form and
entered the dates?

Steve
In your [EnquiryDate] column, change "Group By" to "Where"
(assuming this is where you've set the date range criteria). If you
set this up as a parameter query, you can reuse it for any date
range. Better yet, create a form (with calendars) to capture your
desired date range, then use those unbound form controls as your
query parameters. -Ed

This sounds as if it should be simple, but it's thrown me.

How do I write a query that just gives me the total number of
enquiries I've received between two dates?

I'm using Count() on EnquiriesID, but as soon as I put in any
parameters for EnquiryDate I get a total for the number of
enquiries received each day during the period rather than a single
figure for the whole month. Steve
Click to expand...
Click to expand...
Click to expand...
 
S

Stephen Glynn

I wanted to display the totals on the form; it's partly because I want
to be able to print the query totals and for this sort of thing I find
forms a lot easier to use than reports.

What I ended up doing was binding the textboxes to the appropriate
fields in the query and using Me.Refresh as the After Update event for
the LastDate text box. Seems to work.

Steve
 
Top