Date query problem

G

Guest

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]));
 
K

Ken Snell \(MVP\)

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.
 
G

Guest

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) said:
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 said:
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]));
Click to expand...
Click to expand...
 
G

Guest

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 said:
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) said:
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 said:
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]));
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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) said:
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>


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]));
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

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 said:
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 said:
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 said:
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

:

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>


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]));
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thank you so much :))

Ken Snell (MVP) said:
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 said:
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 said:
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;


:

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

:

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>


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]));
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
Thank you so much :))

Ken Snell (MVP) said:
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 said:
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;





:

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;


:

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

:

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>


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]));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

date query problems 1
MS-Access sum problem in query 2
Variables in Query 1
Expression typed incorrect or too complicated 6
Pull latest date from Multiple entries 2
Error when Date/Time is a criteria 12
Can't get date range in a query 1
Crosstab query totals 3

Top