I am in the beginning stages of setting up a call database that tracks calls
made by sales people. The table includes separate fields for date and time.

A query is needed that will prompt for a date range and time range.
For example a user may want a report that shows all calls for April 1
through April 7 between 11:00 AM and 12:00 PM.

I created the query for a time range, which worked. I then added the
expression, for date range: >=[Start Date] And <=[End Date] I have also
tried: BETWEEN [Start Date] AND [End Date]

Both expressions get the same error when trying to run the query:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

For your reference the time expression is: Between [Start Time] And [End Time]

How do I set up the query to allow choosing both date range and time range?

There are other criteria that will also need to be added, but thought I
should get this working first. Thanks for any help you can give.

Lori

You can do it in a query but it's much better to create a form for the user
to enter the dates and times and then to run the query from a command button.
The query will refer to the form controls to get the dates and times.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"SSi308" wrote:

> I am in the beginning stages of setting up a call database that tracks calls
> made by sales people. The table includes separate fields for date and time.
>
> A query is needed that will prompt for a date range and time range.
> For example a user may want a report that shows all calls for April 1
> through April 7 between 11:00 AM and 12:00 PM.
>
> I created the query for a time range, which worked. I then added the
> expression, for date range: >=[Start Date] And <=[End Date] I have also
> tried: BETWEEN [Start Date] AND [End Date]
>
> Both expressions get the same error when trying to run the query:
> "This expression is typed incorrectly, or it is too complex to be evaluated.
> For example, a numeric expression may contain too many complicated elements.
> Try simplifying the expression by assigning parts of the expression to
> variables."
>
> For your reference the time expression is: Between [Start Time] And [End Time]
>
> How do I set up the query to allow choosing both date range and time range?
>
> There are other criteria that will also need to be added, but thought I
> should get this working first. Thanks for any help you can give.
>
> Lori

Post the full SQL text of your query.
Use BETWEEN not >= and <=

Here is sample:
SELECT CaseNumber, Unit, CMPFiledDate, CMPRcvdDate, CMPPlus30, CMPPlus60
FROM [MyTable]
WHERE CMPFiled = True AND CMPAcpt = False AND CMPRcvdDate IS NOT NULL AND
CMPRcvdDate BETWEEN NZ([CMP Rcvd From],#1/1/1900#) AND NZ([CMP Rcvd
To],#12/31/2999#)
ORDER BY CMPRcvdDate DESC

This also shows how to set up default if user enters nothing.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"SSi308" wrote:

> I am in the beginning stages of setting up a call database that tracks calls
> made by sales people. The table includes separate fields for date and time.
>
> A query is needed that will prompt for a date range and time range.
> For example a user may want a report that shows all calls for April 1
> through April 7 between 11:00 AM and 12:00 PM.
>
> I created the query for a time range, which worked. I then added the
> expression, for date range: >=[Start Date] And <=[End Date] I have also
> tried: BETWEEN [Start Date] AND [End Date]
>
> Both expressions get the same error when trying to run the query:
> "This expression is typed incorrectly, or it is too complex to be evaluated.
> For example, a numeric expression may contain too many complicated elements.
> Try simplifying the expression by assigning parts of the expression to
> variables."
>
> For your reference the time expression is: Between [Start Time] And [End Time]
>
> How do I set up the query to allow choosing both date range and time range?
>
> There are other criteria that will also need to be added, but thought I
> should get this working first. Thanks for any help you can give.
>
> Lori

Dorian,

Thanks for the reply, I finally did get this to work. My error was that I
had added a line in the query for totals and was trying to total the date
field. After changing back to group the query ran fine.

I am interested in how to do this with a form versus a query though. I
thought the query needed to be set up first then a form could be created from
that.

Lori

"Dorian" wrote:

> You can do it in a query but it's much better to create a form for the user
> to enter the dates and times and then to run the query from a command button.
> The query will refer to the form controls to get the dates and times.
> -- Dorian
> "Give someone a fish and they eat for a day; teach someone to fish and they
> eat for a lifetime".
>
>
> "SSi308" wrote:
>
> > I am in the beginning stages of setting up a call database that tracks calls
> > made by sales people. The table includes separate fields for date and time.
> >
> > A query is needed that will prompt for a date range and time range.
> > For example a user may want a report that shows all calls for April 1
> > through April 7 between 11:00 AM and 12:00 PM.
> >
> > I created the query for a time range, which worked. I then added the
> > expression, for date range: >=[Start Date] And <=[End Date] I have also
> > tried: BETWEEN [Start Date] AND [End Date]
> >
> > Both expressions get the same error when trying to run the query:
> > "This expression is typed incorrectly, or it is too complex to be evaluated.
> > For example, a numeric expression may contain too many complicated elements.
> > Try simplifying the expression by assigning parts of the expression to
> > variables."
> >
> > For your reference the time expression is: Between [Start Time] And [End Time]
> >
> > How do I set up the query to allow choosing both date range and time range?
> >
> > There are other criteria that will also need to be added, but thought I
> > should get this working first. Thanks for any help you can give.
> >
> > Lori

Dorian,

Wanted to follow up on this post. I took your suggestion and with the help
of John Spencer and Karl Dewey was able to get the parameter to work. Here is
the sql view, thanks..

PARAMETERS [Forms]!frmWeeklyReport![txtStartDate] DateTime,
[Forms]!frmWeeklyReport![txtEndDate] DateTime;

SELECT DailyCalls.EmpID, Employees.Department, Employees.Initials,
Count(DailyCalls.EmpID) AS [Total Calls]
, Abs(Sum(LengthOfCall>=#12/30/1899 0:3:0#)) AS [Calls 3+]
, Abs(Sum(CallDirection="OUT")) AS [Out Calls]
, Abs(Sum(CallDirection Like "IN*")) AS [In Calls]
, Abs(Sum(CallDirection="OUT"))/Count([DailyCalls.EmpID]) AS [Pt Calls Out]
, Abs(Sum(CallDirection Like "IN*"))/Count([DailyCalls.EmpID]) AS [Pt Calls
In]
, Abs(Sum(LengthOfCall>=#12/30/1899 0:3:0#))/Count([DailyCalls.EmpID]) AS
[Pt Calls 3+]
, Abs(Sum(CallDirection="Out" And (DailyCalls.LengthOfCall)>=#12/30/1899
0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt Calls Out 3+]
, Abs(Sum(CallDirection Like "IN*" And
(DailyCalls.LengthOfCall)>=#12/30/1899 0:3:0#))/Count([DailyCalls.EmpID]) AS
[Pt Calls In 3+]

FROM Employees INNER JOIN DailyCalls ON Employees.EmpID = DailyCalls.EmpID

WHERE (((DailyCalls.CallDate)>=[Forms]![frmWeeklyReport]![txtStartDate] And
(DailyCalls.CallDate)<DateAdd("d",1,[Forms]![frmWeeklyReport]![txtEndDate])))

GROUP BY DailyCalls.EmpID, Employees.Department, Employees.Initials;

Lori

"Dorian" wrote:

> Post the full SQL text of your query.
> Use BETWEEN not >= and <=
>
> Here is sample:
> SELECT CaseNumber, Unit, CMPFiledDate, CMPRcvdDate, CMPPlus30, CMPPlus60
> FROM [MyTable]
> WHERE CMPFiled = True AND CMPAcpt = False AND CMPRcvdDate IS NOT NULL AND
> CMPRcvdDate BETWEEN NZ([CMP Rcvd From],#1/1/1900#) AND NZ([CMP Rcvd
> To],#12/31/2999#)
> ORDER BY CMPRcvdDate DESC
>
> This also shows how to set up default if user enters nothing.
> -- Dorian
> "Give someone a fish and they eat for a day; teach someone to fish and they
> eat for a lifetime".
>
>
> "SSi308" wrote:
>
> > I am in the beginning stages of setting up a call database that tracks calls
> > made by sales people. The table includes separate fields for date and time.
> >
> > A query is needed that will prompt for a date range and time range.
> > For example a user may want a report that shows all calls for April 1
> > through April 7 between 11:00 AM and 12:00 PM.
> >
> > I created the query for a time range, which worked. I then added the
> > expression, for date range: >=[Start Date] And <=[End Date] I have also
> > tried: BETWEEN [Start Date] AND [End Date]
> >
> > Both expressions get the same error when trying to run the query:
> > "This expression is typed incorrectly, or it is too complex to be evaluated.
> > For example, a numeric expression may contain too many complicated elements.
> > Try simplifying the expression by assigning parts of the expression to
> > variables."
> >
> > For your reference the time expression is: Between [Start Time] And [End Time]
> >
> > How do I set up the query to allow choosing both date range and time range?
> >
> > There are other criteria that will also need to be added, but thought I
> > should get this working first. Thanks for any help you can give.
> >
> > Lori