If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Create a Query that Prompts for Date and Time
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 |
#2
|
|||
|
|||
Create a Query that Prompts for Date and Time
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 |
#3
|
|||
|
|||
Create a Query that Prompts for Date and Time
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 |
#4
|
|||
|
|||
Create a Query that Prompts for Date and Time
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 |
#5
|
|||
|
|||
Create a Query that Prompts for Date and Time
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 |
Thread Tools | |
Display Modes | |
|
|