Create a Query that Prompts for Date and Time

  • Thread starter Thread starter SSi308
  • Start date Start date
S

SSi308

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".
 
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".
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top