Query Between Dates

[kabbit]

Hi

I have a database which logs calls. The fields include
the date the call was logged and the team that the call
was assigned to.

I have a query which returns records logged between
certain dates for a certain team.

I have the criteria for the logged date as follows:
"Between [Enter Start Date ##/##/##] And [Enter End Date
##/##/##]".

The criteria for the Team is either "1", "2" or "3".

Until recently this appeared to work fine. No changes
have been made but it does not work correctly any longer.
It is picking up the correct team and the start date is
being picked up okay but it is now not picking up the
records for the end date i.e if the End Date is 09/01/04
it will show records upto and including 08/01/04 - but
not for 09/01/04.

I have searched the previous postings and tried the
following suggestions -

(>= [Enter beginning date]) AND (< [Enter ending date] +
1)

The same as above but without the <

WHERE (((WhatEverTable.field_to_check) Between
[StartDate] And [EndDate]));

None of these work - i have checked very carefully that i
am typing them in correctly but i keep getting error
message that the calculation is too complicated.

Any help would be greatly appreciated.

Thanks

 

[Michel Walsh]

Hi,


You probably include the time in the day, recently, rather that just the
day, so today, 10:00:00 is not (BETWEEN first January 2004 and today), it
is 10 hours past the upper limit, since, including the time, today, alone,
is at 00:00:00. Indeed , having add 1 (day) to the upper limit would solve
that problem, but you probably got a syntax problem doing it. Take a look at
the SQL view (and be sure the field data type are date_and_time, not
strings):

.... WHERE ( somefieldNameHere BETWEEN [Enter Start Date] AND (1+[Enter
End Date] ) )



Hoping it may help,
Vanderghast, Access MVP

 

[kabbit]

Hi

Your are right the time is included in the date. The SQL
view is as follow:

SELECT Calls.CallReference, Calls.DateLogged,
Calls.LoggedBy, Calls.EmployeeName, Calls.EmployeeRef,
Calls.[Client Name], Calls.ContactName,
Calls.PhoneNumber, Calls.TypeOfQuery, Calls.[Query
description], Calls.AssignedToTeam, Calls.CallAssignedTo,
Calls.Severity, Calls.[Over/UnderPayment], Calls.Notes,
Calls.Action, Calls.CallClosed, Calls.DateClosed,
Calls.PayFrequency, TeamLeaders.TeamID
FROM (Operators INNER JOIN Calls ON Operators.[Operator
Name] = Calls.LoggedBy) INNER JOIN TeamLeaders ON
Operators.[Team ID] = TeamLeaders.TeamID
GROUP BY Calls.CallReference, Calls.DateLogged,
Calls.LoggedBy, Calls.EmployeeName, Calls.EmployeeRef,
Calls.[Client Name], Calls.ContactName,
Calls.PhoneNumber, Calls.TypeOfQuery, Calls.[Query
description], Calls.AssignedToTeam, Calls.CallAssignedTo,
Calls.Severity, Calls.[Over/UnderPayment], Calls.Notes,
Calls.Action, Calls.CallClosed, Calls.DateClosed,
Calls.PayFrequency, TeamLeaders.TeamID
HAVING (((Calls.DateLogged) Between [Enter Start Date
##/##/##] And [Enter End Date ##/##/##]) AND
((TeamLeaders.TeamID)=1));


If I amend the last line as you suggest I still get the
error
"This expression is typed incorrectly, or it is too
complex to be evaluated...."

Have had very little experience with either Access or SQL
so would really appreciate any further help you could
give.

 

[Michel Walsh]

Hi,


If you are using the query inside Access, try:

WHERE (Calls.DateLogged Between [Enter Start Date
##/##/##] And DateAdd('d', 1, CDate([Enter End Date ##/##/##])))
AND (TeamLeaders.TeamID=1)


instead of

HAVING (((Calls.DateLogged) Between [Enter Start Date
##/##/##] And [Enter End Date ##/##/##]) AND
((TeamLeaders.TeamID)=1))



In any case, change the HAVING to a WHERE, since you probably want to filter
BEFORE making any statistic, isn't it?



Hoping it may help,
Vanderghast, Access MVP

 

[kabbit]

Hi

Tried changing in SQL View like you said but came up with
syntax error.

Went back to the grid view and added "DateAdd('d', 1,
CDate([Enter End Date ##/##/##]))" to the criteria and it
worked.

the resulting SQL was:

....
HAVING (((Calls.DateLogged) Between [Enter Start Date
##/##/##] And DateAdd('d',1,CDate([Enter End Date
##/##/##]))) AND ((TeamLeaders.TeamID)=1));


doesnt seem to like changing HAVING TO WHERE.

Anyway the query now works perfectly - thank you very
much for you help!!