Records in between 2 dates

[Bob Vance]

How can you I select records between StartDate and EndDate in my query
below

SELECT tblRemarks.HorseID, tblRemarks.Category, Max(tblRemarks.RemarkID) AS
RemarkID1, tblHorseInfo.Status, tblRemarks.dtDate,
tblHorseManagement.StartDate, tblHorseManagement.EndDate
FROM tblHorseManagement, tblRemarks INNER JOIN tblHorseInfo ON
tblRemarks.HorseID = tblHorseInfo.HorseID
GROUP BY tblRemarks.HorseID, tblRemarks.Category, tblHorseInfo.Status,
tblRemarks.dtDate, tblHorseManagement.StartDate, tblHorseManagement.EndDate
HAVING (((tblHorseInfo.Status) Like 'Active*'));

 

[Bob Vance]

Please Correct me if Im wrong........Regards Bob

SELECT tblRemarks.HorseID, tblRemarks.Category, Max(tblRemarks.RemarkID) AS
RemarkID1, tblHorseInfo.Status, tblRemarks.dtDate,
tblHorseManagement.StartDate, tblHorseManagement.EndDate
FROM tblHorseManagement, tblRemarks INNER JOIN tblHorseInfo ON
tblRemarks.HorseID = tblHorseInfo.HorseID
GROUP BY tblRemarks.HorseID, tblRemarks.Category, tblHorseInfo.Status,
tblRemarks.dtDate, tblHorseManagement.StartDate, tblHorseManagement.EndDate
HAVING (((tblHorseInfo.Status) Like 'Active*') AND ((tblRemarks.dtDate)
Between [StartDate] And [EndDate]));

 

[John W. Vinson]

Please Correct me if Im wrong........Regards Bob

Well... not really "wrong" but it can be made more efficient. In a totals
query you can apply criteria in both a WHERE clause (applied *before* all the
total operations are calculated) or in a HAVING clause (does all the totalling
first and then throws away most of your work). Try using the WHERE clause:

SELECT tblRemarks.HorseID, tblRemarks.Category, Max(tblRemarks.RemarkID) AS
RemarkID1, tblHorseInfo.Status, tblRemarks.dtDate
FROM tblHorseManagement, tblRemarks INNER JOIN tblHorseInfo ON
tblRemarks.HorseID = tblHorseInfo.HorseID
WHERE (((tblHorseInfo.Status) Like 'Active*') AND ((tblRemarks.dtDate)
Between [StartDate] And [EndDate]))
GROUP BY tblRemarks.HorseID, tblRemarks.Category, tblHorseInfo.Status,
tblRemarks.dtDate;

I'm presuming that you only want to use the startdate and enddate fields to
limit which records you see, not group by them; otherwise you'll see only the
maximum remarkID for each date.

Just from curiosity: why do you want to see the remarkID at all?

 

[Richard]

How about a calendar control to pick between two dates ? One example..
http://www.datapigtechnologies.com/flashfiles/clndrcontrol.html

Richard

Please Correct me if Im wrong........Regards Bob

Well... not really "wrong" but it can be made more efficient. In a totals
query you can apply criteria in both a WHERE clause (applied *before* all the
total operations are calculated) or in a HAVING clause (does all the totalling
first and then throws away most of your work). Try using the WHERE clause:

SELECT tblRemarks.HorseID, tblRemarks.Category, Max(tblRemarks.RemarkID) AS
RemarkID1, tblHorseInfo.Status, tblRemarks.dtDate
FROM tblHorseManagement, tblRemarks INNER JOIN tblHorseInfo ON
tblRemarks.HorseID = tblHorseInfo.HorseID
WHERE (((tblHorseInfo.Status) Like 'Active*') AND ((tblRemarks.dtDate)
Between [StartDate] And [EndDate]))
GROUP BY tblRemarks.HorseID, tblRemarks.Category, tblHorseInfo.Status,
tblRemarks.dtDate;

I'm presuming that you only want to use the startdate and enddate fields to
limit which records you see, not group by them; otherwise you'll see only the
maximum remarkID for each date.

Just from curiosity: why do you want to see the remarkID at all?

 

[Bob Vance]

Thanks John, But where can i put a requery so as when i change the dates i
dont have to close the form and re-open it to get the right
result......Thanks Bob

Please Correct me if Im wrong........Regards Bob

Well... not really "wrong" but it can be made more efficient. In a totals
query you can apply criteria in both a WHERE clause (applied *before* all
the
total operations are calculated) or in a HAVING clause (does all the
totalling
first and then throws away most of your work). Try using the WHERE clause:

SELECT tblRemarks.HorseID, tblRemarks.Category, Max(tblRemarks.RemarkID)
AS
RemarkID1, tblHorseInfo.Status, tblRemarks.dtDate
FROM tblHorseManagement, tblRemarks INNER JOIN tblHorseInfo ON
tblRemarks.HorseID = tblHorseInfo.HorseID
WHERE (((tblHorseInfo.Status) Like 'Active*') AND ((tblRemarks.dtDate)
Between [StartDate] And [EndDate]))
GROUP BY tblRemarks.HorseID, tblRemarks.Category, tblHorseInfo.Status,
tblRemarks.dtDate;

I'm presuming that you only want to use the startdate and enddate fields
to
limit which records you see, not group by them; otherwise you'll see only
the
maximum remarkID for each date.

Just from curiosity: why do you want to see the remarkID at all?

 

[Bob Vance]

RemarkID was in a query that was used for "Max" to get the last record, and
i just copied that query to create this between date query so has no purpose
at all, so should i delete it out of my query?..Regards Bob

Please Correct me if Im wrong........Regards Bob

Well... not really "wrong" but it can be made more efficient. In a totals
query you can apply criteria in both a WHERE clause (applied *before* all
the
total operations are calculated) or in a HAVING clause (does all the
totalling
first and then throws away most of your work). Try using the WHERE clause:

SELECT tblRemarks.HorseID, tblRemarks.Category, Max(tblRemarks.RemarkID)
AS
RemarkID1, tblHorseInfo.Status, tblRemarks.dtDate
FROM tblHorseManagement, tblRemarks INNER JOIN tblHorseInfo ON
tblRemarks.HorseID = tblHorseInfo.HorseID
WHERE (((tblHorseInfo.Status) Like 'Active*') AND ((tblRemarks.dtDate)
Between [StartDate] And [EndDate]))
GROUP BY tblRemarks.HorseID, tblRemarks.Category, tblHorseInfo.Status,
tblRemarks.dtDate;

I'm presuming that you only want to use the startdate and enddate fields
to
limit which records you see, not group by them; otherwise you'll see only
the
maximum remarkID for each date.

Just from curiosity: why do you want to see the remarkID at all?

 

[BruceM]

If you don't need the field you can get rid of it, as you surmised. The
only reason it's there is because you copied one query as the basis for
another.

RemarkID was in a query that was used for "Max" to get the last record,
and i just copied that query to create this between date query so has no
purpose at all, so should i delete it out of my query?..Regards Bob

Please Correct me if Im wrong........Regards Bob

Well... not really "wrong" but it can be made more efficient. In a totals
query you can apply criteria in both a WHERE clause (applied *before* all
the
total operations are calculated) or in a HAVING clause (does all the
totalling
first and then throws away most of your work). Try using the WHERE
clause:

SELECT tblRemarks.HorseID, tblRemarks.Category, Max(tblRemarks.RemarkID)
AS
RemarkID1, tblHorseInfo.Status, tblRemarks.dtDate
FROM tblHorseManagement, tblRemarks INNER JOIN tblHorseInfo ON
tblRemarks.HorseID = tblHorseInfo.HorseID
WHERE (((tblHorseInfo.Status) Like 'Active*') AND ((tblRemarks.dtDate)
Between [StartDate] And [EndDate]))
GROUP BY tblRemarks.HorseID, tblRemarks.Category, tblHorseInfo.Status,
tblRemarks.dtDate;

I'm presuming that you only want to use the startdate and enddate fields
to
limit which records you see, not group by them; otherwise you'll see only
the
maximum remarkID for each date.

Just from curiosity: why do you want to see the remarkID at all?

 

[John W. Vinson]

RemarkID was in a query that was used for "Max" to get the last record, and
i just copied that query to create this between date query so has no purpose
at all, so should i delete it out of my query?..Regards Bob

Well, let me know a) what you're starting with and b) what you're trying to
accomplish. I don't have any way to guess whether the query is correct if I
don't know what it's supposed to be doing!

 

[Bob Vance]

Thanks Guys, just needed a Me.Requery in the date box and it changed the
result..Thanks bob

Thanks John, But where can i put a requery so as when i change the dates i
dont have to close the form and re-open it to get the right
result......Thanks Bob

Please Correct me if Im wrong........Regards Bob

Well... not really "wrong" but it can be made more efficient. In a totals
query you can apply criteria in both a WHERE clause (applied *before* all
the
total operations are calculated) or in a HAVING clause (does all the
totalling
first and then throws away most of your work). Try using the WHERE
clause:

SELECT tblRemarks.HorseID, tblRemarks.Category, Max(tblRemarks.RemarkID)
AS
RemarkID1, tblHorseInfo.Status, tblRemarks.dtDate
FROM tblHorseManagement, tblRemarks INNER JOIN tblHorseInfo ON
tblRemarks.HorseID = tblHorseInfo.HorseID
WHERE (((tblHorseInfo.Status) Like 'Active*') AND ((tblRemarks.dtDate)
Between [StartDate] And [EndDate]))
GROUP BY tblRemarks.HorseID, tblRemarks.Category, tblHorseInfo.Status,
tblRemarks.dtDate;

I'm presuming that you only want to use the startdate and enddate fields
to
limit which records you see, not group by them; otherwise you'll see only
the
maximum remarkID for each date.

Just from curiosity: why do you want to see the remarkID at all?