SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition
FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
GROUP BY Query1.Disposition
HAVING (((Query1.Disposition)="Remain In Room"));

This query works well and gives me the total count of the patients who
remained in their room until I add the date criteria below then I get nothing
for a result. Can anyone tell me what I am doing wrong. I'm a beginner.


SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition, Query1.CallDate
FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
GROUP BY Query1.Disposition, Query1.CallDate
HAVING (((Query1.Disposition)="Remain In Room") AND
((Query1.CallDate)=[start date] And (Query1.CallDate)=[end date]));

Your criteria will return records only when the CallDate matches both the
start date and the end date.

Assuming that you want a date within that range:

SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition, Query1.CallDate
FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
WHERE Query1.Disposition="Remain In Room" AND
Query1.CallDate Between [start date] And [end date]
GROUP BY Query1.Disposition, Query1.CallDate;

Note that I also changed your HAVING clause to a WHERE clause. WHERE will
cause filtering of records before the grouping, whereas HAVING will filter
the records after grouping. WHERE will be much more efficient and faster.

--

Ken Snell
<MS ACCESS MVP>


"ferde" <(E-Mail Removed)> wrote in message
news:8753D58E-7E4A-4849-89A4-(E-Mail Removed)...
> SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition
> FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
> GROUP BY Query1.Disposition
> HAVING (((Query1.Disposition)="Remain In Room"));
>
> This query works well and gives me the total count of the patients who
> remained in their room until I add the date criteria below then I get
> nothing
> for a result. Can anyone tell me what I am doing wrong. I'm a beginner.
>
>
> SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition,
> Query1.CallDate
> FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
> GROUP BY Query1.Disposition, Query1.CallDate
> HAVING (((Query1.Disposition)="Remain In Room") AND
> ((Query1.CallDate)=[start date] And (Query1.CallDate)=[end date]));
>

Thank you for your reply. I understand better now about using Where but the
SQL you posted did not givie me any records when I cut and pasted it in.
Still not sure what I'm doing wrong here. Would appreciate any beginner steps
you might be able to provide.

thank you

"Ken Snell (MVP)" wrote:

> Your criteria will return records only when the CallDate matches both the
> start date and the end date.
>
> Assuming that you want a date within that range:
>
> SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition, Query1.CallDate
> FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
> WHERE Query1.Disposition="Remain In Room" AND
> Query1.CallDate Between [start date] And [end date]
> GROUP BY Query1.Disposition, Query1.CallDate;
>
> Note that I also changed your HAVING clause to a WHERE clause. WHERE will
> cause filtering of records before the grouping, whereas HAVING will filter
> the records after grouping. WHERE will be much more efficient and faster.
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
> "ferde" <(E-Mail Removed)> wrote in message
> news:8753D58E-7E4A-4849-89A4-(E-Mail Removed)...
> > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition
> > FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
> > GROUP BY Query1.Disposition
> > HAVING (((Query1.Disposition)="Remain In Room"));
> >
> > This query works well and gives me the total count of the patients who
> > remained in their room until I add the date criteria below then I get
> > nothing
> > for a result. Can anyone tell me what I am doing wrong. I'm a beginner.
> >
> >
> > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition,
> > Query1.CallDate
> > FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
> > GROUP BY Query1.Disposition, Query1.CallDate
> > HAVING (((Query1.Disposition)="Remain In Room") AND
> > ((Query1.CallDate)=[start date] And (Query1.CallDate)=[end date]));
> >
>
>
>

I think my problem is associated with the date parameter because I need to
enter dates thru the end of september just to get the records for August .
The CallDate field in Query1 has the dates clearly identified all using the
same input mask format . In Query1 there are over 50 records dated from
08/07/2006 thru 9/30/2006. I hope I am explaining this clearly.


SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition, Query1.CallDate
FROM Query1
WHERE (((Query1.Disposition)="Remain In Room") AND ((Query1.CallDate)
Between [start date] And [end date]))
GROUP BY Query1.Disposition, Query1.CallDate;


"ferde" wrote:

> Thank you for your reply. I understand better now about using Where but the
> SQL you posted did not givie me any records when I cut and pasted it in.
> Still not sure what I'm doing wrong here. Would appreciate any beginner steps
> you might be able to provide.
>
> thank you
>
> "Ken Snell (MVP)" wrote:
>
> > Your criteria will return records only when the CallDate matches both the
> > start date and the end date.
> >
> > Assuming that you want a date within that range:
> >
> > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition, Query1.CallDate
> > FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
> > WHERE Query1.Disposition="Remain In Room" AND
> > Query1.CallDate Between [start date] And [end date]
> > GROUP BY Query1.Disposition, Query1.CallDate;
> >
> > Note that I also changed your HAVING clause to a WHERE clause. WHERE will
> > cause filtering of records before the grouping, whereas HAVING will filter
> > the records after grouping. WHERE will be much more efficient and faster.
> >
> > --
> >
> > Ken Snell
> > <MS ACCESS MVP>
> >
> >
> > "ferde" <(E-Mail Removed)> wrote in message
> > news:8753D58E-7E4A-4849-89A4-(E-Mail Removed)...
> > > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition
> > > FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
> > > GROUP BY Query1.Disposition
> > > HAVING (((Query1.Disposition)="Remain In Room"));
> > >
> > > This query works well and gives me the total count of the patients who
> > > remained in their room until I add the date criteria below then I get
> > > nothing
> > > for a result. Can anyone tell me what I am doing wrong. I'm a beginner.
> > >
> > >
> > > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition,
> > > Query1.CallDate
> > > FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
> > > GROUP BY Query1.Disposition, Query1.CallDate
> > > HAVING (((Query1.Disposition)="Remain In Room") AND
> > > ((Query1.CallDate)=[start date] And (Query1.CallDate)=[end date]));
> > >
> >
> >
> >

As a further example I can type in the specific dates just for september and
still get august records.

SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition, Query1.CallDate
FROM Query1
WHERE (((Query1.Disposition)="Remain In Room") AND ((Query1.CallDate)
Between "08/31/2006" And "10/01/2006"))
GROUP BY Query1.Disposition, Query1.CallDate;





"ferde" wrote:

> I think my problem is associated with the date parameter because I need to
> enter dates thru the end of september just to get the records for August .
> The CallDate field in Query1 has the dates clearly identified all using the
> same input mask format . In Query1 there are over 50 records dated from
> 08/07/2006 thru 9/30/2006. I hope I am explaining this clearly.
>
>
> SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition, Query1.CallDate
> FROM Query1
> WHERE (((Query1.Disposition)="Remain In Room") AND ((Query1.CallDate)
> Between [start date] And [end date]))
> GROUP BY Query1.Disposition, Query1.CallDate;
>
>
> "ferde" wrote:
>
> > Thank you for your reply. I understand better now about using Where but the
> > SQL you posted did not givie me any records when I cut and pasted it in.
> > Still not sure what I'm doing wrong here. Would appreciate any beginner steps
> > you might be able to provide.
> >
> > thank you
> >
> > "Ken Snell (MVP)" wrote:
> >
> > > Your criteria will return records only when the CallDate matches both the
> > > start date and the end date.
> > >
> > > Assuming that you want a date within that range:
> > >
> > > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition, Query1.CallDate
> > > FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
> > > WHERE Query1.Disposition="Remain In Room" AND
> > > Query1.CallDate Between [start date] And [end date]
> > > GROUP BY Query1.Disposition, Query1.CallDate;
> > >
> > > Note that I also changed your HAVING clause to a WHERE clause. WHERE will
> > > cause filtering of records before the grouping, whereas HAVING will filter
> > > the records after grouping. WHERE will be much more efficient and faster.
> > >
> > > --
> > >
> > > Ken Snell
> > > <MS ACCESS MVP>
> > >
> > >
> > > "ferde" <(E-Mail Removed)> wrote in message
> > > news:8753D58E-7E4A-4849-89A4-(E-Mail Removed)...
> > > > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition
> > > > FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
> > > > GROUP BY Query1.Disposition
> > > > HAVING (((Query1.Disposition)="Remain In Room"));
> > > >
> > > > This query works well and gives me the total count of the patients who
> > > > remained in their room until I add the date criteria below then I get
> > > > nothing
> > > > for a result. Can anyone tell me what I am doing wrong. I'm a beginner.
> > > >
> > > >
> > > > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition,
> > > > Query1.CallDate
> > > > FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
> > > > GROUP BY Query1.Disposition, Query1.CallDate
> > > > HAVING (((Query1.Disposition)="Remain In Room") AND
> > > > ((Query1.CallDate)=[start date] And (Query1.CallDate)=[end date]));
> > > >
> > >
> > >
> > >

The SQL statement you posted won't work because it's treating dates as if
they were strings. It needs to look like this if you "hard-code" the date
values:

SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition, Query1.CallDate
FROM Query1
WHERE (((Query1.Disposition)="Remain In Room") AND ((Query1.CallDate)
Between #08/31/2006# And #10/01/2006#))
GROUP BY Query1.Disposition, Query1.CallDate;

Assuming that [start date] and [end date] are parameters that you type in
when you run the query, it's likely that Jet is not seeing the entries as
dates. So let's specifically declare the parameters to be the date type:

PARAMETERS [start date] DateTime, [end date] DateTime;
SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition, Query1.CallDate
FROM Query1
WHERE (((Query1.Disposition)="Remain In Room") AND ((Query1.CallDate)
Between [start date] And [end date]))
GROUP BY Query1.Disposition, Query1.CallDate;

--

Ken Snell
<MS ACCESS MVP>


"ferde" <(E-Mail Removed)> wrote in message
news:770610DC-E26D-4895-8D6F-(E-Mail Removed)...
>
> As a further example I can type in the specific dates just for september
> and
> still get august records.
>
> SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition,
> Query1.CallDate
> FROM Query1
> WHERE (((Query1.Disposition)="Remain In Room") AND ((Query1.CallDate)
> Between "08/31/2006" And "10/01/2006"))
> GROUP BY Query1.Disposition, Query1.CallDate;
>
>
>
>
>
> "ferde" wrote:
>
>> I think my problem is associated with the date parameter because I need
>> to
>> enter dates thru the end of september just to get the records for August
>> .
>> The CallDate field in Query1 has the dates clearly identified all using
>> the
>> same input mask format . In Query1 there are over 50 records dated from
>> 08/07/2006 thru 9/30/2006. I hope I am explaining this clearly.
>>
>>
>> SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition,
>> Query1.CallDate
>> FROM Query1
>> WHERE (((Query1.Disposition)="Remain In Room") AND ((Query1.CallDate)
>> Between [start date] And [end date]))
>> GROUP BY Query1.Disposition, Query1.CallDate;
>>
>>
>> "ferde" wrote:
>>
>> > Thank you for your reply. I understand better now about using Where
>> > but the
>> > SQL you posted did not givie me any records when I cut and pasted it
>> > in.
>> > Still not sure what I'm doing wrong here. Would appreciate any beginner
>> > steps
>> > you might be able to provide.
>> >
>> > thank you
>> >
>> > "Ken Snell (MVP)" wrote:
>> >
>> > > Your criteria will return records only when the CallDate matches both
>> > > the
>> > > start date and the end date.
>> > >
>> > > Assuming that you want a date within that range:
>> > >
>> > > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition,
>> > > Query1.CallDate
>> > > FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
>> > > WHERE Query1.Disposition="Remain In Room" AND
>> > > Query1.CallDate Between [start date] And [end date]
>> > > GROUP BY Query1.Disposition, Query1.CallDate;
>> > >
>> > > Note that I also changed your HAVING clause to a WHERE clause. WHERE
>> > > will
>> > > cause filtering of records before the grouping, whereas HAVING will
>> > > filter
>> > > the records after grouping. WHERE will be much more efficient and
>> > > faster.
>> > >
>> > > --
>> > >
>> > > Ken Snell
>> > > <MS ACCESS MVP>
>> > >
>> > >
>> > > "ferde" <(E-Mail Removed)> wrote in message
>> > > news:8753D58E-7E4A-4849-89A4-(E-Mail Removed)...
>> > > > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition
>> > > > FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
>> > > > GROUP BY Query1.Disposition
>> > > > HAVING (((Query1.Disposition)="Remain In Room"));
>> > > >
>> > > > This query works well and gives me the total count of the patients
>> > > > who
>> > > > remained in their room until I add the date criteria below then I
>> > > > get
>> > > > nothing
>> > > > for a result. Can anyone tell me what I am doing wrong. I'm a
>> > > > beginner.
>> > > >
>> > > >
>> > > > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition,
>> > > > Query1.CallDate
>> > > > FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
>> > > > GROUP BY Query1.Disposition, Query1.CallDate
>> > > > HAVING (((Query1.Disposition)="Remain In Room") AND
>> > > > ((Query1.CallDate)=[start date] And (Query1.CallDate)=[end date]));
>> > > >
>> > >
>> > >
>> > >

Thank you so much )

"Ken Snell (MVP)" wrote:

> The SQL statement you posted won't work because it's treating dates as if
> they were strings. It needs to look like this if you "hard-code" the date
> values:
>
> SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition, Query1.CallDate
> FROM Query1
> WHERE (((Query1.Disposition)="Remain In Room") AND ((Query1.CallDate)
> Between #08/31/2006# And #10/01/2006#))
> GROUP BY Query1.Disposition, Query1.CallDate;
>
> Assuming that [start date] and [end date] are parameters that you type in
> when you run the query, it's likely that Jet is not seeing the entries as
> dates. So let's specifically declare the parameters to be the date type:
>
> PARAMETERS [start date] DateTime, [end date] DateTime;
> SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition, Query1.CallDate
> FROM Query1
> WHERE (((Query1.Disposition)="Remain In Room") AND ((Query1.CallDate)
> Between [start date] And [end date]))
> GROUP BY Query1.Disposition, Query1.CallDate;
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
> "ferde" <(E-Mail Removed)> wrote in message
> news:770610DC-E26D-4895-8D6F-(E-Mail Removed)...
> >
> > As a further example I can type in the specific dates just for september
> > and
> > still get august records.
> >
> > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition,
> > Query1.CallDate
> > FROM Query1
> > WHERE (((Query1.Disposition)="Remain In Room") AND ((Query1.CallDate)
> > Between "08/31/2006" And "10/01/2006"))
> > GROUP BY Query1.Disposition, Query1.CallDate;
> >
> >
> >
> >
> >
> > "ferde" wrote:
> >
> >> I think my problem is associated with the date parameter because I need
> >> to
> >> enter dates thru the end of september just to get the records for August
> >> .
> >> The CallDate field in Query1 has the dates clearly identified all using
> >> the
> >> same input mask format . In Query1 there are over 50 records dated from
> >> 08/07/2006 thru 9/30/2006. I hope I am explaining this clearly.
> >>
> >>
> >> SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition,
> >> Query1.CallDate
> >> FROM Query1
> >> WHERE (((Query1.Disposition)="Remain In Room") AND ((Query1.CallDate)
> >> Between [start date] And [end date]))
> >> GROUP BY Query1.Disposition, Query1.CallDate;
> >>
> >>
> >> "ferde" wrote:
> >>
> >> > Thank you for your reply. I understand better now about using Where
> >> > but the
> >> > SQL you posted did not givie me any records when I cut and pasted it
> >> > in.
> >> > Still not sure what I'm doing wrong here. Would appreciate any beginner
> >> > steps
> >> > you might be able to provide.
> >> >
> >> > thank you
> >> >
> >> > "Ken Snell (MVP)" wrote:
> >> >
> >> > > Your criteria will return records only when the CallDate matches both
> >> > > the
> >> > > start date and the end date.
> >> > >
> >> > > Assuming that you want a date within that range:
> >> > >
> >> > > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition,
> >> > > Query1.CallDate
> >> > > FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
> >> > > WHERE Query1.Disposition="Remain In Room" AND
> >> > > Query1.CallDate Between [start date] And [end date]
> >> > > GROUP BY Query1.Disposition, Query1.CallDate;
> >> > >
> >> > > Note that I also changed your HAVING clause to a WHERE clause. WHERE
> >> > > will
> >> > > cause filtering of records before the grouping, whereas HAVING will
> >> > > filter
> >> > > the records after grouping. WHERE will be much more efficient and
> >> > > faster.
> >> > >
> >> > > --
> >> > >
> >> > > Ken Snell
> >> > > <MS ACCESS MVP>
> >> > >
> >> > >
> >> > > "ferde" <(E-Mail Removed)> wrote in message
> >> > > news:8753D58E-7E4A-4849-89A4-(E-Mail Removed)...
> >> > > > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition
> >> > > > FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
> >> > > > GROUP BY Query1.Disposition
> >> > > > HAVING (((Query1.Disposition)="Remain In Room"));
> >> > > >
> >> > > > This query works well and gives me the total count of the patients
> >> > > > who
> >> > > > remained in their room until I add the date criteria below then I
> >> > > > get
> >> > > > nothing
> >> > > > for a result. Can anyone tell me what I am doing wrong. I'm a
> >> > > > beginner.
> >> > > >
> >> > > >
> >> > > > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition,
> >> > > > Query1.CallDate
> >> > > > FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
> >> > > > GROUP BY Query1.Disposition, Query1.CallDate
> >> > > > HAVING (((Query1.Disposition)="Remain In Room") AND
> >> > > > ((Query1.CallDate)=[start date] And (Query1.CallDate)=[end date]));
> >> > > >
> >> > >
> >> > >
> >> > >
>
>
>

PMJI.

I notice that you have used 31 August as the start of the range and 1
October as the end of the range. If you want to return the rows for
September only this is a little risky as it will also return any rows on 31
August, and any on 1 October with a zero time of day, i.e. if the date only
has been entered into the field rather than a date and time (if the Now()
function has been used as the default value say). Either of the following
would be safer, returning only those rows dated in September regardless of
whether they have zero or non zero times of day:

WHERE CallDate >= #09/01/2006# AND CallDate < #10/01/2006#

or:

WHERE YEAR(CallDate) = 2006 AND MONTH(CallDate) = 9

Ken Sheridan
Stafford, England

"ferde" wrote:

> Thank you so much )
>
> "Ken Snell (MVP)" wrote:
>
> > The SQL statement you posted won't work because it's treating dates as if
> > they were strings. It needs to look like this if you "hard-code" the date
> > values:
> >
> > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition, Query1.CallDate
> > FROM Query1
> > WHERE (((Query1.Disposition)="Remain In Room") AND ((Query1.CallDate)
> > Between #08/31/2006# And #10/01/2006#))
> > GROUP BY Query1.Disposition, Query1.CallDate;
> >
> > Assuming that [start date] and [end date] are parameters that you type in
> > when you run the query, it's likely that Jet is not seeing the entries as
> > dates. So let's specifically declare the parameters to be the date type:
> >
> > PARAMETERS [start date] DateTime, [end date] DateTime;
> > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition, Query1.CallDate
> > FROM Query1
> > WHERE (((Query1.Disposition)="Remain In Room") AND ((Query1.CallDate)
> > Between [start date] And [end date]))
> > GROUP BY Query1.Disposition, Query1.CallDate;
> >
> > --
> >
> > Ken Snell
> > <MS ACCESS MVP>
> >
> >
> > "ferde" <(E-Mail Removed)> wrote in message
> > news:770610DC-E26D-4895-8D6F-(E-Mail Removed)...
> > >
> > > As a further example I can type in the specific dates just for september
> > > and
> > > still get august records.
> > >
> > > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition,
> > > Query1.CallDate
> > > FROM Query1
> > > WHERE (((Query1.Disposition)="Remain In Room") AND ((Query1.CallDate)
> > > Between "08/31/2006" And "10/01/2006"))
> > > GROUP BY Query1.Disposition, Query1.CallDate;
> > >
> > >
> > >
> > >
> > >
> > > "ferde" wrote:
> > >
> > >> I think my problem is associated with the date parameter because I need
> > >> to
> > >> enter dates thru the end of september just to get the records for August
> > >> .
> > >> The CallDate field in Query1 has the dates clearly identified all using
> > >> the
> > >> same input mask format . In Query1 there are over 50 records dated from
> > >> 08/07/2006 thru 9/30/2006. I hope I am explaining this clearly.
> > >>
> > >>
> > >> SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition,
> > >> Query1.CallDate
> > >> FROM Query1
> > >> WHERE (((Query1.Disposition)="Remain In Room") AND ((Query1.CallDate)
> > >> Between [start date] And [end date]))
> > >> GROUP BY Query1.Disposition, Query1.CallDate;
> > >>
> > >>
> > >> "ferde" wrote:
> > >>
> > >> > Thank you for your reply. I understand better now about using Where
> > >> > but the
> > >> > SQL you posted did not givie me any records when I cut and pasted it
> > >> > in.
> > >> > Still not sure what I'm doing wrong here. Would appreciate any beginner
> > >> > steps
> > >> > you might be able to provide.
> > >> >
> > >> > thank you
> > >> >
> > >> > "Ken Snell (MVP)" wrote:
> > >> >
> > >> > > Your criteria will return records only when the CallDate matches both
> > >> > > the
> > >> > > start date and the end date.
> > >> > >
> > >> > > Assuming that you want a date within that range:
> > >> > >
> > >> > > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition,
> > >> > > Query1.CallDate
> > >> > > FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
> > >> > > WHERE Query1.Disposition="Remain In Room" AND
> > >> > > Query1.CallDate Between [start date] And [end date]
> > >> > > GROUP BY Query1.Disposition, Query1.CallDate;
> > >> > >
> > >> > > Note that I also changed your HAVING clause to a WHERE clause. WHERE
> > >> > > will
> > >> > > cause filtering of records before the grouping, whereas HAVING will
> > >> > > filter
> > >> > > the records after grouping. WHERE will be much more efficient and
> > >> > > faster.
> > >> > >
> > >> > > --
> > >> > >
> > >> > > Ken Snell
> > >> > > <MS ACCESS MVP>
> > >> > >
> > >> > >
> > >> > > "ferde" <(E-Mail Removed)> wrote in message
> > >> > > news:8753D58E-7E4A-4849-89A4-(E-Mail Removed)...
> > >> > > > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition
> > >> > > > FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
> > >> > > > GROUP BY Query1.Disposition
> > >> > > > HAVING (((Query1.Disposition)="Remain In Room"));
> > >> > > >
> > >> > > > This query works well and gives me the total count of the patients
> > >> > > > who
> > >> > > > remained in their room until I add the date criteria below then I
> > >> > > > get
> > >> > > > nothing
> > >> > > > for a result. Can anyone tell me what I am doing wrong. I'm a
> > >> > > > beginner.
> > >> > > >
> > >> > > >
> > >> > > > SELECT Count(Query1.Log) AS CountOfLog, Query1.Disposition,
> > >> > > > Query1.CallDate
> > >> > > > FROM Query1 INNER JOIN [Log table] ON Query1.Log = [Log table].Log
> > >> > > > GROUP BY Query1.Disposition, Query1.CallDate
> > >> > > > HAVING (((Query1.Disposition)="Remain In Room") AND
> > >> > > > ((Query1.CallDate)=[start date] And (Query1.CallDate)=[end date]));
> > >> > > >
> > >> > >
> > >> > >
> > >> > >
> >
> >
> >